Convert race results to useable format

Question

Hi!&nbsp;Struggling to convertan excel spreadhseet with triathlon race times. The spreadsheet is a conversion from a PDF.The file looks like this:Participant&nbsp; &nbsp;Swim&nbsp; &nbsp; &nbsp;Bike&nbsp; &nbsp; &nbsp; Run&nbsp; &nbsp; &nbsp; TOTALx&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;05:47&nbsp; &nbsp; 24:52&nbsp; &nbsp; &nbsp;18:14&nbsp; &nbsp; 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.&nbsp;The file is https://file.io/XRBugnx5ONqF.&nbsp;Thanks for sparing me some headaches 🙂

martin_weiss · Answer

Hi&nbsp;Thighbolt&nbsp;&nbsp;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?

joeuser2004 · Answer

Thighbolt&nbsp;&nbsp;If you enter the times manually, you append ".0" (or whatever fractional seconds are in the race results), and format as [mm]:ss .&nbsp; For example, 5:47.0 instead of 5:47.&nbsp;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....&nbsp;Suppose the&nbsp; individual times for swim, bike and run are in B2:D2.&nbsp; Enter 60 into any unused cell, and copy that cell.&nbsp; Then select B2:D2 and paste-special-divide.&nbsp; Then format B2:D2 as [mm]:ss .&nbsp;Also, format the cell with =SUM(B2:D2) as [mm]:ss .&nbsp;But don't format to include your transition times.&nbsp; They factor into the total.&nbsp;

Forum Discussion

Convert race results to useable format

2 Replies

Resources