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

In Part one I gave a method to work with MM:SS.00 times in excel, stored as text to prevent excel corrupting them. Converting them to decimal seconds works well, and you can then manipulate the times with ease, but the conversion method using built in excel functions in Part one is a bit cumbersome and can’t handle some formats.

After being frustrated by this for some time I decided to write some VBA code to do the same thing and implement the much easier to remember function =MMSSHH2dec(). Having done that seemed like a good idea to write the opposite function =dec2MMSSHH().

So now we can type in a bunch of swim times, quickly convert them to decimal seconds, then add them, compare them, round them up or down, whatever you like and then convert them back to their original format to display.

To try this, you can just download the demo spread sheet to see them in action. Excel will probably warn you that the spread sheet contains macros, that is the VBS code. You will need to click on the box to enable the macros for the sheet to work.

The demo sheet includes test cases to show how the functions handle various correct and incorrect MM:SS.00 Strings.

If you are not happy with the security of macro enabled spreadsheets from my random site on internet, then you can examine the code below and paste it in to the Excel VBA editor yourself.

I welcome any suggestions or improvements to the code. I am not a VB programmer, I spend my life making electronics and writing code for small microcontrollers in C. So I am sure others can improve on my efforts and look forward to learning from them.

Here is the code:

``````' dec2MMSSHH2()
' Convert swim times from decimal seconds to minutes,seconds,hundredths "MM:SS.00" to display
' (C) August 2019 mark@leman.net / www.leman.net and released under MIT Licence https://opensource.org/licenses/MIT

Function dec2MMSSHH(dectime As Variant) As Variant

If dectime = "" Then         ' if dectime is empty then
dec2MMSSHH = ""             ' return an empty result
'dec2MMSSHH = "00:00.00"    ' uncomment to return 00:00.00 instead
Exit Function
End If

If Not IsNumeric(dectime) Then   ' is dectime a number, if it is not
dec2MMSSHH = CVErr(xlErrValue) ' return an error
Exit Function
End If

If Abs(dectime >= 60000) Then  ' is dectime too big or too small
dec2MMSSHH = CVErr(xlErrValue)       ' return an error
Exit Function
End If

If Abs(dectime) >= 60 Then
Dim MM As Long
MM = Int(dectime / 60)
dec2MMSSHH = CStr(MM) + ":" + Format(CStr(dectime - (60 * MM)), "00.00")
Else
dec2MMSSHH = Format(CStr(dectime), "00.00")
End If

End Function
``````
``````' MMSSHH2dec()
' Convert swim times in format minutes,seconds,hundredths "MM:SS.00" to decimal seconds to allow easy maths
' (C) August 2019 mark@leman.net / www.leman.net and released under MIT Licence https://opensource.org/licenses/MIT

Function MMSSHH2dec(timestring As String) As Variant

timestring = Trim(timestring)  ' trim off and leading or trailing spaces

Dim timestring_len As Long     ' find out how long the MM:SS:PP string is
timestring_len = Len(timestring)

If timestring_len = 0 Then      'if the string has no length
MMSSHH2dec = ""                'then return an empty result
'MMSSHH2dec = 0                 ' alternatively we could return 0
Exit Function
End If

If timestring_len > 9 Then      'if the string is too long (999:59.99 is 9 chars) then
MMSSHH2dec = CVErr(xlErrValue) ' return an error
Exit Function
End If

' check that the input string only contains the chars "0123456789.:"
Dim RegEx As Object
Set RegEx = CreateObject("VBScript.RegExp")
RegEx.Global = True
RegEx.Pattern = "^[0-9.:]*\$"
If Not RegEx.Test(timestring) Then
MMSSHH2dec = CVErr(xlErrValue)   'return an error
Exit Function
End If

'find out how many .: we have
RegEx.Pattern = "[.:]"
Set matches = RegEx.Execute(timestring)

'Useful Debug
'Debug.Print "===" & timestring & "===="
'Debug.Print "#" & matches.Count&; " Delimiters"
'For Each Match In matches
'        Debug.Print "Item=" & Match
'        Debug.Print "Index=" & Match.FirstIndex
'Next Match

Dim MM As String
Dim SS As String
Dim PP As String
Dim max_seconds As Integer
max_seconds = 59   ' For M:SS.PP formats then only accept up to 59 seconds but for other formats, like SSS.PP we can modify this

Select Case matches.Count
Case Is = 0
SS = timestring
max_seconds = 100

Case Is = 1
If timestring_len = 1 Then
'          Debug.Print "Error: SS.PP string needs to be longer then 1 char"
MMSSHH2dec = CVErr(xlErrValue)
Exit Function
End If

If matches(0) = "." Then
SS = Left(timestring, matches(0).FirstIndex)
max_seconds = 100
PP = Right(timestring, timestring_len - matches(0).FirstIndex - 1)
ElseIf matches(0) = ":" Then
MM = Left(timestring, matches(0).FirstIndex)
SS = Right(timestring, timestring_len - matches(0).FirstIndex - 1)
Else
'           Debug.Print "This should never happen!"
MMSSHH2dec = CVErr(xlErrValue)
Exit Function
End If

Case Is = 2
If timestring_len = 2 Then
'           Debug.Print "Error: MM:SS.PP string needs to be longer then 2 chars"
MMSSHH2dec = CVErr(xlErrValue)  ' return and error
'MMSSHH2dec = 0                 ' alternatively we could return 0
Exit Function
End If

MM = Left(timestring, matches(0).FirstIndex)
SS = Mid(timestring, matches(0).FirstIndex + 2, matches(1).FirstIndex - matches(0).FirstIndex - 1)
PP = Right(timestring, timestring_len - matches(1).FirstIndex - 1)

Case Else
MMSSHH2dec = CVErr(xlErrValue)
Exit Function
End Select

'Debug.Print "Raw   MM:'" & MM; "' SS'" & SS & "' PP:'" & PP; "'"

If Val(MM) > 999 Then
'   Debug.Print "MM > 999"
MMSSHH2dec = CVErr(xlErrValue)
Exit Function
End If

If Val(SS) > max_seconds Then
MMSSHH2dec = CVErr(xlErrValue)
Exit Function
End If

If Len(PP) = 1 Then
PP = PP + "0"
End If

If Val(PP) > 99 Then
MMSSHH2dec = CVErr(xlErrValue)
Exit Function
End If

'Debug.Print "Final MM:'" & MM; "' SS'" & SS & "' PP:'" & PP; "'"

MMSSHH2dec = CDbl((Val(MM) * 60) + Val(SS) + (Val(PP) / 100))
End Function`````` 1. Swati says:

Hi,
How to convert 01-JUN-18 12.25.13.031000000 AM into 00:25:13 as the string is in AM.

Also, how I convert the same string to time format of in PM to 12:25:13.

Can you help me with a formula with AM and PM?

Thank you.

1. lemanadmin says:

Hi Swati,

The article is about how to deal with times in minutes:seconds.hundredths (particularly for swimming competitions), if you have a question about that I can probably help, but I don’t think your question is about this?

Regards,
Mark

1. lemanadmin says:

I had a few minutes spare so…..

If you paste:
’01-JUN-18 12.25.13.031000000 AM
in to cell A1 and then use the formula

=TIMEVALUE(SUBSTITUTE(MID(A1,SEARCH(” “,A1,1),9),”.”,”:”)&” “&RIGHT(A1,2))

And format that cell to show time in 24 hour time, I think it will do what you want.

This depends on:
1) There being a space after the date to find the start of the time
2) The time to always be 9 letters long eg; 12.12.13, although adding another search for the 3rd dot would allow variable length times.
3) the AM/PM to be the last two chars.

The formula could be made in to a function in VBS.

Hope that helps.