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 email@example.com / 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 firstname.lastname@example.org / 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
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?
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?
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.