VBA Convert Time from weird format

Maybe:

Option Explicit

Function fCnvTime(ByRef cell As Range)

' example VBA call: MsgBox fCnvTime(Range("A3"))
' example WS call:  =fcnvtime(A3)

Dim vArray
Dim lMin As Long, lSec As Long, lMS As Long, i As Long
Dim sTime As String

On Error Resume Next

If Right(cell, 1) = Chr(160) Then cell = Left(cell, Len(cell) - 1)
vArray = Split(Trim(cell.Value))
lMin = 0: lSec = 0: lMS = 0

For i = LBound(vArray) To UBound(vArray)
    If LCase(Right(vArray(i), 2)) = LCase("Mn") Then
        lMin = lMin + --Left(vArray(i), Len(vArray(i)) - Len("Mn"))
        GoTo lblNext
    End If
    If LCase(Right(vArray(i), 2)) = LCase("ms") Then
        lMS = lMS + --Left(vArray(i), Len(vArray(i)) - Len("ms"))
        lSec = lSec + Round(lMS / 1000, 0)
        GoTo lblNext
    End If
    If LCase(Right(vArray(i), 1)) = LCase("s") Then
        lSec = lSec + --Left(vArray(i), Len(vArray(i)) - Len("s"))
        GoTo lblNext
    End If
lblNext:
Next 'i

sTime = lMin & ":" & lSec

fCnvTime = TimeValue(sTime)

On Error GoTo 0

End Function

Sub test()

MsgBox fCnvTime(Range("A3"))

End Sub

Regards, TMS

Suggest you post a sample workbook with some typical data. I'm just working with a copy of the text in your post.

Or, just format the cell as Time


Regards, TMS

If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

Looked again and, yes, you are/were right ... I missed the 0 hours part out.

I've changed the code now. Both functions work well but my version rounds the milliseconds as you suggested you would like originally. Not a lot in it

See the attached workbook.

Option Explicit

Function fCnvTime(ByRef cell As Range)

' example VBA call: MsgBox fCnvTime(Range("A3"))
' example WS call:  =fcnvtime(A3)

Dim vArray
Dim lMin As Long, lSec As Long, lMS As Long, i As Long
Dim sTime As String

On Error Resume Next

If Right(cell, 1) = Chr(160) Then cell = Left(cell, Len(cell) - 1)
vArray = Split(Trim(cell.Value))
lMin = 0: lSec = 0: lMS = 0

For i = LBound(vArray) To UBound(vArray)
    If LCase(Right(vArray(i), 2)) = LCase("Mn") Then
        lMin = lMin + --Left(vArray(i), Len(vArray(i)) - Len("Ms"))
        GoTo lblNext
    End If
    If LCase(Right(vArray(i), 2)) = LCase("ms") Then
        lMS = lMS + --Left(vArray(i), Len(vArray(i)) - Len("ms"))
        lSec = lSec + Round(lMS / 1000, 0)
        GoTo lblNext
    End If
    If LCase(Right(vArray(i), 1)) = LCase("s") Then
        lSec = lSec + --Left(vArray(i), Len(vArray(i)) - Len("s"))
        GoTo lblNext
    End If
lblNext:
Next 'i

fCnvTime = TimeSerial(0, lMin, lSec)

On Error GoTo 0

End Function

Sub test()

MsgBox fCnvTime(Range("A10"))

End Sub

Regards, TMS

Hello bfs3,

Here is another way using a function that is simpler.

Function ConvertTime(ByVal sTime As String) As Long

    Dim arr As Variant
    
        arr = Split(sTime, " ")
                
        If Right(arr(0), 1) = "s" Then
            ConvertTime = Val(arr(0)) + (Val(arr(1)) * 0.001)
        Else
            ConvertTime = 60 * Val(arr(0)) + Val(arr(1))
        End If
        
End Function