Convert race results to useable format

Occasional Visitor



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 Replies

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?



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.