CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
demprin
demprin

Reputation: 3

Converting Time Formats in Excel

How would I convert time values that are output in as a string (21m49s) into the Excel time value (00:21:49)? If there is no hour value to be given, the cell is not assigned a 0h value. It will display only minutes and seconds in that case.

Will also need to account for readings above 60 minutes which would display as 1h34m14s.

This is how I receive the time values:

image

This is what I need them to look like:

image

Upvotes: 0

Views: 485

Answers (2)

Scott Craner
Scott Craner

Reputation: 152505

Try this:

=TIME(IF(ISNUMBER(FIND("h",A1)),LEFT(A1,FIND("h",A1)-1),0),IF(ISNUMBER(FIND("m",A1)),IF(ISNUMBER(FIND("h",A1)),MID(A1,FIND("h",A1)+1,FIND("m",A1)-1-FIND("h",A1)),LEFT(A1,FIND("m",A1)-1)),0),IF(ISNUMBER(FIND("s",A1)),IF(ISNUMBER(FIND("m",A1)),MID(A1,FIND("m",A1)+1,LEN(A1)-1-FIND("m",A1)),LEFT(A1,FIND("s",A1)-1)),0))

enter image description here

Though @Jeeped beat me, I will post my UDF:

Function TimeChange(str As String) As Date
Dim strArr() As String
Dim i As Integer
Dim hr As Integer
Dim min As Integer
Dim sec As Integer

str = Replace(str, "h", "h ")
str = Replace(str, "m", "m ")
str = Replace(str, "s", "s ")

strArr = Split(Trim(str))

For i = 0 To UBound(strArr)
    Select Case Right(strArr(i), 1)
        Case "h": hr = Left(strArr(i), Len(strArr(i)) - 1)
        Case "m": min = Left(strArr(i), Len(strArr(i)) - 1)
        Case "s": sec = Left(strArr(i), Len(strArr(i)) - 1)
    End Select
Next i

TimeChange = TimeSerial(hr, min, sec)

End Function

enter image description here

Upvotes: 1

user4039065
user4039065

Reputation:

Possibly as a VBA UDF¹.

Function realTime(str As String) As Double
    Dim t As Long, vTMs As Variant, vTMP As Variant

    vTMs = Array("h", 0, "m", 0, "s", 0)
    vTMP = Array(str & ChrW(8203))

    For t = LBound(vTMs) To UBound(vTMs) Step 2
        vTMP = Split(vTMP(0), vTMs(t))
        If IsNumeric(vTMP(0)) Then
            vTMs(t + 1) = Int(vTMP(0))
            vTMP(0) = vTMP(1)
        End If
    Next t

    realTime = TimeSerial(vTMs(1), vTMs(3), vTMs(5))

End Function

        realTme_UDF

¹ A User Defined Function (aka UDF) is placed into a standard module code sheet. Tap Alt+F11 and when the VBE opens, immediately use the pull-down menus to Insert ► Module (Alt+I,M). Paste the function code into the new module code sheet titled something like Book1 - Module1 (Code). Tap Alt+Q to return to your worksheet(s).

Upvotes: 1

Related Questions