Convert race results to useable format

%3CLINGO-SUB%20id%3D%22lingo-sub-3219723%22%20slang%3D%22en-US%22%3EConvert%20race%20results%20to%20useable%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3219723%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStruggling%20to%20convertan%20excel%20spreadhseet%20with%20triathlon%20race%20times.%20The%20spreadsheet%20is%20a%20conversion%20from%20a%20PDF.%3C%2FP%3E%3CP%3EThe%20file%20looks%20like%20this%3A%3C%2FP%3E%3CP%3EParticipant%26nbsp%3B%20%26nbsp%3BSwim%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BBike%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Run%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20TOTAL%3C%2FP%3E%3CP%3Ex%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B05%3A47%26nbsp%3B%20%26nbsp%3B%2024%3A52%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B18%3A14%26nbsp%3B%20%26nbsp%3B%2049%3A53%20(fake%20data)%3C%2FP%3E%3CP%3EI'm%20having%20issues%20converting%20the%20times%20above%20into%20mm%3Ass%20as%20Excel%20then%20converts%20that%20into%20what%20looks%20like%20hh%3Amm.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20file%20is%20%3CA%20href%3D%22https%3A%2F%2Ffile.io%2FXRBugnx5ONqF%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehere%3C%2FA%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20sparing%20me%20some%20headaches%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3219723%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3219824%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20race%20results%20to%20useable%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3219824%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1319420%22%20target%3D%22_blank%22%3E%40Thighbolt%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eit%20seems%20that%20the%20example%20file%20that%20you%20provided%20does%20not%20exist%20anymore.%20Could%20you%20please%20check%20and%20upload%20a%20new%20one%2C%20so%20it's%20easier%20to%20give%20advise%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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 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?

@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.