Convert duration in text to HH:MM:SS format in excel

norwoodkd2001

New Member
Joined
Jun 30, 2015
Messages
10
I have an extract of data where the duration for an activity is listed as ## hour ## Minutes ## Seconds ex. 1 hour 22 minutes 29 seconds. Is there a way to convert this a time format so I can perform date math between two activities?

Not all entries have hour, minutes and seconds. There are some that have 54 minutes or 30 seconds or 3 minutes 8 seconds.

Any help is appreciated.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Are the times listed in a column? Are the words "hours", "minutes", and "seconds" used in the time?
 
Upvote 0
Yes to all. Below is a sample from the column.


1 hour 22 Minutes 29 Seconds
1 hour 23 Minutes 12 Seconds
2 hours 16 Minutes 39 Seconds
19 Minutes 25 Seconds
13 Minutes 28 Seconds
3 Minutes 37 Seconds
1 hour 23 Minutes 41 Seconds
19 hours 4 Minutes 20 Seconds
29 Minutes 27 Seconds
19 hours 36 Minutes 30 Seconds
1 Minute
33 Seconds

<colgroup><col></colgroup><tbody>
</tbody>
 
Upvote 0
This was fun and a challenge. I'm sure one of the VBA aces will come up with a better solution, but I got it to work (I think).

I used these options (slightly different from your list):

HoursMinutesSeconds
1 hour 22 Minutes 29 Seconds12229
1 hour 23 Minutes 12 Seconds12312
2 hours 16 Minutes 39 Seconds21639
19 Minutes 25 Seconds01925
13 Minutes 28 Seconds01328
3 Minutes 37 Seconds0337
1 hour 41 Seconds1041
19 hours 4 Minutes1940
29 Minutes 27 Seconds02927
19 hours 36 Minutes 30 Seconds193630
1 Minute010
33 Seconds0033
2 Hours 5 seconds205
2 Hours 5 minutes250
9 hours900

<tbody>
</tbody>


<tbody>
</tbody>
 
Last edited:
Upvote 0
Editing after posting is a real nightmare.

Macro:

Code:
Sub BreakoutTime()
Dim strg() As String, i As Integer, LR As Long, u As Long, j As Integer
LR = Cells(Rows.Count, "A").End(xlUp).Row

For i = 2 To LR
strg = Split(Range("A" & i))
u = UBound(strg)

If u = 5 Then
  If "h" = Left(LCase(strg(1)), 1) Then
    Cells(i, 2) = strg(0)
    Cells(i, 3) = strg(2)
    Cells(i, 4) = strg(4)
  End If
 ElseIf u = 3 Then
       Cells(i, 2) = 0
       Cells(i, 3) = 0
       Cells(i, 4) = 0
     If "h" = Left(LCase(strg(1)), 1) Then
       Cells(i, 2) = strg(0)
       ElseIf "m" = Left(LCase(strg(1)), 1) Then
       Cells(i, 3) = strg(0)
       ElseIf "s" = Left(LCase(strg(1)), 1) Then
       Cells(i, 4) = strg(0)
     End If
    If "m" = Left(LCase(strg(3)), 1) Then
     Cells(i, 3) = strg(2)
     ElseIf "s" = Left(LCase(strg(3)), 1) Then
     Cells(i, 4) = strg(2)
    End If
  ElseIf u = 1 Then
       Cells(i, 2) = 0
       Cells(i, 3) = 0
       Cells(i, 4) = 0
     If "h" = Left(LCase(strg(1)), 1) Then
       Cells(i, 2) = strg(0)
     ElseIf "m" = Left(LCase(strg(1)), 1) Then
       Cells(i, 3) = strg(0)
     ElseIf "s" = Left(LCase(strg(1)), 1) Then
       Cells(i, 4) = strg(0)
     End If
  End If
    
Next
End Sub
 
Upvote 0
Here's a lightly tested UDF if you prefer to use a function as in the example below.
Sheet1


AB
1DataElapsed Time
21 hour 22 Minutes 29 Seconds01:22:29
31 hour 23 Minutes 12 Seconds01:23:12
42 hours 16 Minutes 39 Seconds02:16:39
519 Minutes 25 Seconds00:19:25
613 Minutes 28 Seconds00:13:28
73 Minutes 37 Seconds00:03:37
81 hour 41 Seconds01:00:41
919 hours 4 Minutes19:04:00
1029 Minutes 27 Seconds00:29:27
1119 hours 36 Minutes 30 Seconds19:36:30
121 Minute00:01:00
1333 Seconds00:00:33
142 hours 5 Seconds02:00:05
152 hours 5 Minutes02:05:00
169 hours09:00:00

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:205px;"><col style="width:84px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
B2=HHMMSS(A2)+0

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


Code:
Option Compare Text
Function HHMMSS(S As String) As Variant
Dim V As Variant, HH, MM, SS
V = Split(S, " ")
If InStr(S, "hour") > 0 And InStr(S, "Minute") > 0 And InStr(S, "Second") > 0 Then
    HH = V(0)
    MM = V(2)
    SS = V(4)
    HHMMSS = HH & ":" & MM & ":" & SS
    Exit Function
End If
If InStr(S, "hour") = 0 And InStr(S, "Minute") > 0 And InStr(S, "Second") > 0 Then
    HH = "00"
    MM = V(0)
    SS = V(2)
    HHMMSS = HH & ":" & MM & ":" & SS
    Exit Function
End If
If InStr(S, "hour") = 0 And InStr(S, "Minute") = 0 And InStr(S, "Second") > 0 Then
    HH = "00"
    MM = "00"
    SS = V(0)
    HHMMSS = HH & ":" & MM & ":" & SS
    Exit Function
End If
If InStr(S, "hour") = 0 And InStr(S, "Minute") > 0 And InStr(S, "Second") = 0 Then
    HH = "00"
    MM = V(0)
    SS = "00"
    HHMMSS = HH & ":" & MM & ":" & SS
    Exit Function
End If
If InStr(S, "hour") > 0 And InStr(S, "Minute") = 0 And InStr(S, "Second") = 0 Then
    HH = V(0)
    MM = "00"
    SS = "00"
    HHMMSS = HH & ":" & MM & ":" & SS
    Exit Function
End If
If InStr(S, "hour") > 0 And InStr(S, "Minute") = 0 And InStr(S, "Second") > 0 Then
    HH = V(0)
    MM = "00"
    SS = V(2)
    HHMMSS = HH & ":" & MM & ":" & SS
    Exit Function
End If
If InStr(S, "hour") > 0 And InStr(S, "Minute") > 0 And InStr(S, "Second") = 0 Then
    HH = V(0)
    MM = V(2)
    SS = "00"
    HHMMSS = HH & ":" & MM & ":" & SS
    Exit Function
End If

End Function
 
Last edited:
Upvote 0
Another option:

Code:
Function TimeConv(STR As String)
Dim arr As Variant
Dim i As Integer
Dim FinalTime As String
Dim hr As Integer, min As Integer, sec As Integer
hr = 0
min = 0
sec = 0
arr = Split(STR, " ")
For i = 1 To UBound(arr)
    Select Case LCase(arr(i))
        Case "hour", "hours"
        hr = arr(i - 1)
        Case "minute", "minutes"
        min = arr(i - 1)
        Case "second", "seconds"
        sec = arr(i - 1)
    End Select
Next i
TimeConv = TimeValue(hr & ":" & min & ":" & sec)
End Function
 
Last edited:
Upvote 0
Here is the UDF (user defined function) that I came up with (it will allow abbreviations for the Hours, Minutes and Seconds in case that should ever occur)...
Code:
Function GetTime(ByVal S As String) As Date
  Dim X As Long, Z As Long, V As Variant, Part() As String, HMS(1 To 3) As Long
  S = LCase(Replace("x" & Replace(S & " ", "s ", "", , , vbTextCompare), " ", ""))
  For Each V In Array("h", "m", "s")
    Z = Z + 1
    If S Like "*" & V & "*" Then
      Part = Split(S, V)
      For X = Len(Part(0)) To 1 Step -1
        If Mid(Part(0), X, 1) Like "[!0-9]" Then
          HMS(Z) = Mid(Part(0), X + 1)
          Exit For
        End If
      Next
    End If
  Next
  GetTime = TimeSerial(HMS(1), HMS(2), HMS(3))
End Function
 
Upvote 0
One way:

=IFERROR(LEFT(A1,SEARCH("hour",A1)-1)/24,0)
+IFERROR(MID(0&A1,SEARCH("Minute",0&A1)-3,2)/1440,0)
+IFERROR(MID(0&A1,SEARCH("Second",0&A1)-3,2)/86400,0)
 
Upvote 0
A shorter one:

=SUMPRODUCT(IFERROR(MID(0&TRIM(A1),SEARCH({" h"," m"," s"},0&TRIM(A1))-2,2),0)/{24,1440,86400})
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top