Forum Discussion

Ashar2200's avatar
Ashar2200
Copper Contributor
Aug 14, 2023
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. 

 

 

  • 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
  • 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
    • Ashar2200's avatar
      Ashar2200
      Copper Contributor
      Thank you so much for this, it worked perfectly and i apricate your help.
  • Ashar2200 

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

    • Ashar2200's avatar
      Ashar2200
      Copper Contributor

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

Resources