Convert race results to useable format

[Thighbolt]

Hi!

Struggling to convertan excel spreadhseet with triathlon race times. The spreadsheet is a conversion from a PDF.

The file looks like this:

Participant   Swim     Bike      Run      TOTAL

x                   05:47    24:52     18:14    49:53 (fake data)

I'm having issues converting the times above into mm:ss as Excel then converts that into what looks like hh:mm.

The file is here.

Thanks for sparing me some headaches :)

[#2]

Hi @Thighbolt 

it seems that the example file that you provided does not exist anymore. Could you please check and upload a new one, so it's easier to give advise?

[#3]

@Thighbolt 

If you enter the times manually, you append ".0" (or whatever fractional seconds are in the race results), and format as [mm]:ss .  For example, 5:47.0 instead of 5:47.

If you must copy-and-paste data in the form min:sec, which Excel interprets as h:m, you can convert the data afterwards, as follows....

Suppose the  individual times for swim, bike and run are in B2:D2.  Enter 60 into any unused cell, and copy that cell.  Then select B2:D2 and paste-special-divide.  Then format B2:D2 as [mm]:ss .

Also, format the cell with =SUM(B2:D2) as [mm]:ss .

But don't format to include your transition times.  They factor into the total.