SOLVED

Requested Formula for Time Conversion

%3CLINGO-SUB%20id%3D%22lingo-sub-2467263%22%20slang%3D%22en-US%22%3ERequested%20Formula%20for%20Time%20Conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2467263%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20looking%20for%20an%20easy%20way%20to%20automatically%20convert%20time%20entries%20to%20minutes%20rounded%20to%20the%20nearest%20minute.%20The%20problem%20is%20that%20the%20format%20given%20doesn't%20seem%20to%20want%20to%20convert%20to%20other%20formats%20and%20isn't%20recognized%20as%20a%20number.%20Additionally%2C%20sometimes%20hours%20and%20minutes%20are%20given%2C%20and%20sometimes%20minutes%20and%20seconds%20are%20given%20(if%20it's%20under%20one%20hour)%20or%20even%20only%20seconds%20if%20it's%20under%20a%20minute.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20are%20some%20examples%20of%20entries%20as%20they're%20shown%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2h%201m%3C%2FP%3E%3CP%3E54m%2036s%3C%2FP%3E%3CP%3E31s%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I'm%20looking%20for%20is%20a%20way%20to%20convert%20those%20to%20minutes%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E121%3C%2FP%3E%3CP%3E55%3C%2FP%3E%3CP%3E1%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20using%20365%20Enterprise.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2467263%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2467388%22%20slang%3D%22en-US%22%3ERe%3A%20Requested%20Formula%20for%20Time%20Conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2467388%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1084538%22%20target%3D%22_blank%22%3E%40Roger1988%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20a%20custom%20VBA%20function%2C%20to%20be%20copied%20into%20a%20module%20in%20the%20Visual%20Basic%20Editor%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EFunction%20TextToMinutes(s%20As%20String)%20As%20Long%0A%20%20%20%20Dim%20a()%20As%20String%0A%20%20%20%20Dim%20i%20As%20Long%0A%20%20%20%20Dim%20n%20As%20Long%0A%20%20%20%20a%20%3D%20Split(s)%0A%20%20%20%20For%20i%20%3D%200%20To%20UBound(a)%0A%20%20%20%20%20%20%20%20Select%20Case%20Right(a(i)%2C%201)%0A%20%20%20%20%20%20%20%20%20%20%20%20Case%20%22h%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20n%20%3D%20n%20%2B%2060%20*%20Val(a(i))%0A%20%20%20%20%20%20%20%20%20%20%20%20Case%20%22m%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20n%20%3D%20n%20%2B%20Val(a(i))%0A%20%20%20%20%20%20%20%20%20%20%20%20Case%20%22s%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20n%20%3D%20n%20%2B%20Val(a(i))%20%2F%2060%0A%20%20%20%20%20%20%20%20End%20Select%0A%20%20%20%20Next%20i%0A%20%20%20%20TextToMinutes%20%3D%20n%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EUse%20like%20this%3A%3C%2FP%3E%0A%3CP%3E%3DTextToMinutes(A1)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0529.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F290368i80BDC93EFB91BA12%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22S0529.png%22%20alt%3D%22S0529.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2467394%22%20slang%3D%22en-US%22%3ERe%3A%20Requested%20Formula%20for%20Time%20Conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2467394%22%20slang%3D%22en-US%22%3EThanks!%20This%20works%20perfectly.%3C%2FLINGO-BODY%3E
New Contributor

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

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

 

S0529.png

Thanks! This works perfectly.

@Roger1988 

As variant for

image.png

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