SOLVED

Converting Time from text input to time format

Copper Contributor

Hello everyone, 

 

I have a report with time shown in a text format and inconsistent with the format. Can someone assist with how to extract time out a format like x days x minutes x hours? please see the screenshot. 

 

Ashar2200_0-1692015139984.png

 

4 Replies

@Ashar2200 

Others will undoubtedly come up with a clever solution using TEXTSPLIT.

HansVogelaar_0-1692017769207.png

Here is a workbook using a custom VBA function:

Function Text2Time(s As String) As Date
    Dim v() As String
    Dim i As Long
    Dim r As Date
    v = Split(s)
    For i = 0 To UBound(v) Step 2
        Select Case v(i + 1)
            Case "Days"
                r = r + v(i)
            Case "Hours"
                r = r + v(i) / 24
            Case "Minutes"
                r = r + v(i) / 1440
            Case "Seconds"
                r = r + v(i) / 86400
        End Select
    Next i
    Text2Time = r
End Function

E2 contains the formula

=Text2Time(D2)

I applied the custom number format [hh]:mm:ss to E2, then filled down. [hh] instructs Excel to display the hours as duration, not as clock time.

best response confirmed by Ashar2200 (Copper Contributor)
Solution

@Ashar2200 

As variant

=TEXTBEFORE(D2, "Days", , , , 0) +
    RIGHT(TEXTBEFORE(D2, " Hours", , , , 0), 2) / 24 +
    RIGHT(TEXTBEFORE(D2, " Minutes", , , , 0), 2) / 24 / 60 +
    RIGHT(TEXTBEFORE(D2, " Seconds", , , , 0), 2) / 24 / 60 / 60

Thank you so much for your help on this. I appreciate your time.

Thank you so much for this, it worked perfectly and i apricate your help.
1 best response

Accepted Solutions
best response confirmed by Ashar2200 (Copper Contributor)
Solution

@Ashar2200 

As variant

=TEXTBEFORE(D2, "Days", , , , 0) +
    RIGHT(TEXTBEFORE(D2, " Hours", , , , 0), 2) / 24 +
    RIGHT(TEXTBEFORE(D2, " Minutes", , , , 0), 2) / 24 / 60 +
    RIGHT(TEXTBEFORE(D2, " Seconds", , , , 0), 2) / 24 / 60 / 60

View solution in original post