+ Reply to Thread
Results 1 to 2 of 2

time format problem for sports result analysis

  1. #1
    Registered User
    Join Date
    09-14-2012
    Location
    Montreal, CAN
    MS-Off Ver
    Excel 2010
    Posts
    4

    time format problem for sports result analysis

    hi all ! I just completed my registration to ask for your help. I'm sure it will be easy for you but I can't figure it out. 2012-09-08 Olympique Esprit mtl.xlsx

    if you open attached .xlsx file you will see I imported triathlon results and made 4 graphs

    1st graph shows overall time plotted vs overall position (every data point is aligned)
    2nd graph shows swim time plotted vs overall position (scattered, understandable because a faster swimmer doesn't necessarily equals great overall time)
    3rd graph shows bike time plotted vs overall position (idem)
    4th graph shows running time plotted vs overall position (idem)

    Actually the data is sorted by overall position (which is fine). But the problem I'm having is that the time format for some athletes is messed up. For example see position 5, 14 and 37 bike split times. Because the majority of triathles have a bike time of more than 1 hour. But these 3 managed to finish under 1h. When I plot the dots, Excel think it's 59h (when it's actually minutes). If you look at the swim (nage) and running (course à pied) graphs, you can see these irregularities also with times splits that are out of the ordinary.

    If you could fix that, and tell me how to manage this in my future analysis

    thx all !

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: time format problem for sports result analysis

    In a separate column convert your times to the correct format and value by using

    =IF(DAY(J4)=0,J4,TIME(0,(DAY(J4)*24)+HOUR(J4),MINUTE(J4)))

    format as h:mm:ss

    You can then copy -- paste special -- values this column back onto the orignal one
    Life's a spreadsheet, Excel!
    Say thanks, Click *

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1