Requested Formula for Time Conversion

[Roger1988]

I'm looking for an easy way to automatically convert time entries to minutes rounded to the nearest minute. The problem is that the format given doesn't seem to want to convert to other formats and isn't recognized as a number. Additionally, sometimes hours and minutes are given, and sometimes minutes and seconds are given (if it's under one hour) or even only seconds if it's under a minute.

Here are some examples of entries as they're shown:

2h 1m

54m 36s

31s

What I'm looking for is a way to convert those to minutes like this:

121

55

1

I'm using 365 Enterprise.

[#2 · Accepted solution]

@Roger1988 

Here is a custom VBA function, to be copied into a module in the Visual Basic Editor:

Function TextToMinutes(s As String) As Long
    Dim a() As String
    Dim i As Long
    Dim n As Long
    a = Split(s)
    For i = 0 To UBound(a)
        Select Case Right(a(i), 1)
            Case "h"
                n = n + 60 * Val(a(i))
            Case "m"
                n = n + Val(a(i))
            Case "s"
                n = n + Val(a(i)) / 60
        End Select
    Next i
    TextToMinutes = n
End Function

Use like this:

=TextToMinutes(A1)

 

[#3]

Thanks! This works perfectly.

[#4]

@Roger1988 

As variant for

is

=LET(v, A1,
 isS,  ISNUMBER(SEARCH("s", v)),
 isM,  ISNUMBER(SEARCH("m", v)),
 isH,  ISNUMBER(SEARCH("h", v)),
 strH, IF( isH, SUBSTITUTE(v,"h",":"), "00:" & v ),
 strM, IF( isM, SUBSTITUTE(strH, "m",":"), "00:" & strH  ),
 strS, IF( isS, SUBSTITUTE(strM, "s",""), strM & "00"  ),
 str,  SUBSTITUTE(strS, " ", ""),
 ROUND(str*24*60,0) )