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

N

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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
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.


[TABLE="width: 251"]
<colgroup><col></colgroup><tbody>[TR]
[TD]1 hour 22 Minutes 29 Seconds [/TD]
[/TR]
[TR]
[TD]1 hour 23 Minutes 12 Seconds [/TD]
[/TR]
[TR]
[TD]2 hours 16 Minutes 39 Seconds [/TD]
[/TR]
[TR]
[TD]19 Minutes 25 Seconds [/TD]
[/TR]
[TR]
[TD]13 Minutes 28 Seconds [/TD]
[/TR]
[TR]
[TD]3 Minutes 37 Seconds [/TD]
[/TR]
[TR]
[TD]1 hour 23 Minutes 41 Seconds [/TD]
[/TR]
[TR]
[TD]19 hours 4 Minutes 20 Seconds [/TD]
[/TR]
[TR]
[TD]29 Minutes 27 Seconds [/TD]
[/TR]
[TR]
[TD]19 hours 36 Minutes 30 Seconds [/TD]
[/TR]
[TR]
[TD]1 Minute [/TD]
[/TR]
[TR]
[TD]33 Seconds [/TD]
[/TR]
</tbody>[/TABLE]
 
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):


<tbody>
[TD="class: xl65"][/TD]
[TD="class: xl65, width: 64"]Hours[/TD]
[TD="class: xl65, width: 64"]Minutes[/TD]
[TD="class: xl65, width: 64"]Seconds[/TD]

[TD="class: xl66, width: 255"]1 hour 22 Minutes 29 Seconds[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]22[/TD]
[TD="class: xl65, align: right"]29[/TD]

[TD="class: xl66, width: 255"]1 hour 23 Minutes 12 Seconds[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]23[/TD]
[TD="class: xl65, align: right"]12[/TD]

[TD="class: xl66, width: 255"]2 hours 16 Minutes 39 Seconds[/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: right"]16[/TD]
[TD="class: xl65, align: right"]39[/TD]

[TD="class: xl66, width: 255"]19 Minutes 25 Seconds[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]19[/TD]
[TD="class: xl65, align: right"]25[/TD]

[TD="class: xl66, width: 255"]13 Minutes 28 Seconds[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]13[/TD]
[TD="class: xl65, align: right"]28[/TD]

[TD="class: xl66, width: 255"]3 Minutes 37 Seconds[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl65, align: right"]37[/TD]

[TD="class: xl66, width: 255"]1 hour 41 Seconds[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]41[/TD]

[TD="class: xl66, width: 255"]19 hours 4 Minutes[/TD]
[TD="class: xl65, align: right"]19[/TD]
[TD="class: xl65, align: right"]4[/TD]
[TD="class: xl65, align: right"]0[/TD]

[TD="class: xl66, width: 255"]29 Minutes 27 Seconds[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]29[/TD]
[TD="class: xl65, align: right"]27[/TD]

[TD="class: xl66, width: 255"]19 hours 36 Minutes 30 Seconds[/TD]
[TD="class: xl65, align: right"]19[/TD]
[TD="class: xl65, align: right"]36[/TD]
[TD="class: xl65, align: right"]30[/TD]

[TD="class: xl66, width: 255"]1 Minute[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]0[/TD]

[TD="class: xl66, width: 255"]33 Seconds[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]33[/TD]

[TD="class: xl66, width: 255"]2 Hours 5 seconds[/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]5[/TD]

[TD="class: xl66, width: 255"]2 Hours 5 minutes[/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]0[/TD]

[TD="class: xl66, width: 255"]9 hours[/TD]
[TD="class: xl65, align: right"]9[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]

</tbody>

[TABLE="width: 447"]
<tbody>[TR]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
 
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
DataElapsed Time
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 41 Seconds
19 hours 4 Minutes
29 Minutes 27 Seconds
19 hours 36 Minutes 30 Seconds
1 Minute
33 Seconds
2 hours 5 Seconds
2 hours 5 Minutes
9 hours

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:205px;"><col style="width:84px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]01:22:29[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]01:23:12[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: right"]02:16:39[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: right"]00:19:25[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="align: right"]00:13:28[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="align: right"]00:03:37[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="align: right"]01:00:41[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="align: right"]19:04:00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

[TD="align: right"]00:29:27[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]

[TD="align: right"]19:36:30[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]

[TD="align: right"]00:01:00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]

[TD="align: right"]00:00:33[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]14[/TD]

[TD="align: right"]02:00:05[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]15[/TD]

[TD="align: right"]02:05:00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]16[/TD]

[TD="align: right"]09:00:00[/TD]

</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
You must log in or register to reply here.

Similar threads

T
  • Solved
Time difference (conditional format)
Replies
3
Views
238
JamesCanale
JamesCanale
A
  • Question Question
Need to convert days, hours, minutes and second text into hh:mm:ss
Replies
7
Views
853
Aadya
A
D
  • Solved
Highlighting cells with date/time greater than 29 minutes past the hour
Replies
6
Views
608
Peter_SSs
Peter_SSs
AOB
  • Question Question
Calculate the "true" duration of up/down times accounting for gaps and overlaps
Replies
1
Views
671
AOB
AOB
D
  • Question Question
Rounding Negative time to the nearest minute in Excel
Replies
4
Views
658
Scott Huish
Scott Huish

Forum statistics

Threads
1,223,214
Messages
6,170,774
Members
452,353
Latest member
strainu

Share this page

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