I was talking with a workmate of mine and he mentioned that he is a swimming coach.
He told me that it’s difficult to record swimming times in Excel.
His approach was to record Minutes, Seconds and Splits (Split Seconds) as separate columns. Now, I can only imagine what sort of frustrating (if not impressive!) formulas this led to.
I explained to him that Excel can store Split Seconds right out of the box.
Just format the cell as mm:ss.00
Great! That’ll work.
Being the thinker, he suddenly realised typing in the times would be even more difficult than before. It’s that fiddly colon key :
“Is there a way to type the times with a decimal point as a separator?” he asked.
Fair enough question. His times on paper are written as dot separated.
Fun! Let’s write a formula!
I figured out there were 3 formats of time:
23 which means 23 seconds
23.45 which means 23.45 seconds
1.23.45 which means 1 minute and 23.45 seconds
The hour portion of the time is never reached.
So for a time typed into A1, the following formula turns it into an Excel time.
=IF(LEN(A1) – LEN(SUBSTITUTE(A1, “.”, “”)) = 2, TIMEVALUE(“00:” & SUBSTITUTE(A1, “.”, “:”, 1)), TIMEVALUE(“00:00:” & A1))
Again, the cell has a custom format of mm:ss.00