Jun 21 2021 01:30 PM
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.
Jun 21 2021 01:56 PM
SolutionHere 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)
Jun 21 2021 02:45 PM
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) )
Jun 21 2021 01:56 PM
SolutionHere 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)