Excel is an amazing tool and is used and abused to perform many tasks it was not originally intended for. One thing it should be good at is calculating times, however in swimming we tend to use times in the format minutes:seconds.hundredths and Excel does not really work well with times in this format.
If you search the internet you will find loads of posts saying you can enter a MM:SS.00 time in excel using the custom number format:
However as soon as you click on a time using this format to edit it, it looses the hundredths! This is not great when you have carefully typed in a load of times to work with and excel promptly corrupts them 🙁 . This is really frustrating.
Currently my best tip here is to enter the MM:SS.00 times as text, use an excel formula to convert the text to decimal seconds. Then to do any maths and convert back to MM:SS.00 to display.
For example 2 mins 45 seconds 30 hundredths becomes 165.30 decimal seconds, excel understands decimal numbers and can easily add, subtract, compare and manipulate them 🙂
Here is an example: In Cell A1 enter 2 min 45 seconds 30 hundredths, do this by typing ‘2:45.30 (the leading ‘ tells excel it is text).
in Cell A2 cut an paste the following excel formula:
=IFERROR((IFERROR(LEFT(A1,FIND(“:”,A1)-1)* 60,0) ) + RIGHT(A1,LEN(A1)-FIND(“:”,A1)),VALUE(A1))
Which will convert the contents of Cell A1 from text in ‘MM:SS.00 format to decimal seconds
To convert back from decimal seconds to MM:SS.00 cut an paste the following in Cell A3:
=IF(INT(A2/60)>0,TEXT(INT(A2/60),”0″&”:”),””)&TEXT(MOD(INT(A2),60),”00″)&”.”&TEXT(100*(A2-TRUNC(A2,0)),”00″)
These formulas have worked well for me for several years, although the excel formula are not not ideal to work with because of how many times they reference the original cell, which makes them hard to move.
Here is a sample excel sheet to demonstrate these in use:
Recently I have been working to automate the generation of the Qualifying times for the Kent County Championship and have spent some time making some easier to use VB code in excel to do this. Also code to convert from SC to LC using the British swimming method. More of this in a later post….
Hi Mark. A fellow dad of swimmers here. Just wanted to drop a “thank you” for this post. Like many before me, I began tracking my kiddo’s swim times in a spreadsheet and was surprised to learn of the absence of functions for doing simple time arithmetic either in Excel or Google Sheets. Thank you for sharing your solution. The VBA solution is much more elegant, but unfortunately not compatible with Google Sheets, so this first method is what I’m currently using. THANK YOU!
Glad you found it useful.
As Google sheets now includes the ‘let()’ function the formula can be made easier to adjust for new cells e.g:
MM:SS.HH text to decimal seconds:
=let(time,A1,IFERROR((IFERROR(LEFT(time,FIND(“:”,time)-1)*60,0))+RIGHT(time,LEN(time)-FIND(“:”,time)),VALUE(TIME(B6))))
Decimal seconds to MM:SS.HH:
=Let(secs,A2,IF(INT(secs/60)>0,TEXT(INT(secs/60),”0″&”:”),””)&TEXT(MOD(INT(secs),60),”00″)&”.”&TEXT(100*(secs-TRUNC(secs,0)),”00″))
With these there is just one cell reference to adjust when moving the formula to a new place.
Regards,
Mark