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

3 Comments

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

Leave a Comment

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