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