Forum Discussion
Swim Times - mm:ss.00 format but numerical so you can do formulas and conditional formatting
Hi there - I have spent a lot of time trying to get the formulas to work for this. I am doing simple formulas for swim times. Cell A1 would be a swimmers actual swim time - let's say 50mFree - ...
A slight modification of Hans' formatting — to include a plus sign for non-negative numbers — makes it easy to use an additional formula to convert a formatted time difference back to a number, for calculation purposes.
And using Excel 365 or Excel for the web, the LAMBDA function is useful for that calculation. See cells E2:E6 on Sheet1 in the attached workbook.
I created the defined name TimeDiffValue (a "named formula", loosely speaking) for that LAMBDA function. It's a handy way of doing the conversion; see cells E11:E13 and G13.