 SOLVED

# Requested Formula for Time Conversion

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.

3 Replies
best response confirmed by Roger1988 (New Contributor)
Solution

# Re: Requested Formula for Time Conversion

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) # Re: Requested Formula for Time Conversion

Thanks! This works perfectly.

# Re: Requested Formula for Time Conversion

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) )``````