Forum Discussion
Solved
Converting Time from text input to time format
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.
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
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 this, it worked perfectly and i apricate your help.
Others will undoubtedly come up with a clever solution using TEXTSPLIT.
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 FunctionE2 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.
Thank you so much for your help on this. I appreciate your time.
