Working in Excel with MM:SS.00 swim times (part 1)

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

Leave a Comment

Your email address will not be published. Required fields are marked *