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.
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,049
Office Version
365, 2010
Are the times listed in a column? Are the words "hours", "minutes", and "seconds" used in the time?
 

norwoodkd2001

New Member
Joined
Jun 30, 2015
Messages
10
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>
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,049
Office Version
365, 2010
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:

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,049
Office Version
365, 2010
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
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,867
Office Version
2010
Platform
Windows
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:

Nishant94

Well-known Member
Joined
May 8, 2015
Messages
508
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:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,960
Office Version
2010
Platform
Windows
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
 

Phuoc

Board Regular
Joined
Apr 29, 2016
Messages
193
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)
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,542
A shorter one:

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

Watch MrExcel Video

Forum statistics

Threads
1,100,140
Messages
5,472,751
Members
406,835
Latest member
steve43040

This Week's Hot Topics

Top