PDA

View Full Version : [SOLVED] Separate Time HMS



Ringhal
11-26-2013, 10:22 AM
I have a workbook with a column, but the data isn't user-friendly and sortable. Each cell has something like this: "1h 17m 40s" or "14m 6s" or simply, "8s". I would like to separate this into 3 columns, one for hour, one for minute and one for second, so it would appear as the table below:



Hr
Min
Sec


1
17
40



14
6




8


10870

Kenneth Hobs
11-26-2013, 01:24 PM
As I see it, you need to add either 1 or 4 helper columns. That extra column is for a time or fraction of a day. That is then sortable. For the 1 extra column method:

Sub ParseTime()
Dim r As Range, c As Range
Dim s() As String, i As Integer
Dim d As Date

Set r = Range("A2", Range("A" & Rows.Count).End(xlUp))

For Each c In r
s() = Split(c.Value2)
d = 0
For i = UBound(s) To 0 Step -1
Select Case UBound(s)
Case 2
d = TimeSerial(num(s(0)), num(s(1)), num(s(0)))
Case 1
d = TimeSerial(0, num(s(1)), num(s(0)))
Case 0
d = TimeSerial(0, 0, num(s(0)))
Case Else
End Select
Next i
If d <> 0 Then
c.Offset(0, 1).Value = d
'c.Offset(0, 1).NumberFormat = Format(d, "h:mm:ss AM/PM")
End If
Next c
End Sub

Function num(ByVal ref As String)
With CreateObject("VBScript.Regexp")
.Pattern = "\d+"
num = .Execute(ref)(0)
End With
End Function

Ringhal
11-27-2013, 12:46 AM
Thanks Kenneth Hobs

After running the code, it didn't work as expected. In the final column the second value was replaced with the hour value, for example, "7h 39m 11s" turned into "07:39:07". Figuring it had something to do with:

Select Case UBound(s)
Case 2
d = TimeSerial(num(s(0)), num(s(1)), num(s(0)))
Case 1
d = TimeSerial(0, num(s(1)), num(s(0)))
Case 0
d = TimeSerial(0, 0, num(s(0)))
Case Else
End Select

I changed:

Case 2
d = TimeSerial(num(s(0)), num(s(1)), num(s(0)))

to:

Case 2
d = TimeSerial(num(s(2)), num(s(1)), num(s(0)))

but then it appeared in reverse, as in "7h 39m 11s" to "11:39:07".

I formatted the column as "[h]:mm:ss". Also the column is more of a duration than an actual time, so there will be cases when there is more than 24 hours.

snb
11-27-2013, 02:30 AM
I'd prefer


Sub M_snb()
[B2:B26] = [index(substitute(substitute(substitute(choose(len(A2:A26)-len(substitute(A2:A26," ",""))+1,"0:0:","0:","")&A2:A26,"h ",":"),"m ",":"),"s",""),)]
End Sub

The resulting column is perfectly sortable.

Ringhal
11-27-2013, 04:20 AM
Thanks snb

The code works extremely well. There was a problem with it when the time was more than 24 hours, so I just redid the formatting to include days. Is there a way to modify the code so that so it can work on a dynamic range?

snb
11-27-2013, 04:47 AM
Sub M_snb()
Columns(1).SpecialCells(2).Offset(1).SpecialCells(2).Name = "snb_002"
[snb_002].Offset(, 5) = [index(substitute(substitute(substitute(choose(len(snb_002)-len(substitute(snb_002," ",""))+1,"0:0:","0:","")&snb_002,"h ",":"),"m ",":"),"s",""),)]
End Sub

Kenneth Hobs
11-27-2013, 06:26 AM
In my example for time, one just needs to add the 2.

d = TimeSerial(num(s(2)), num(s(1)), num(s(0)))

That is the problem with not knowing your data, I did not know that you might have more than one day. In that case, one would parse the hours into days and hours for a decimal day date format. I would do that for you but if you like snb's solution, then that will suffice.

Please post your final snb code and thanks for marking this thread solved.

Ringhal
11-27-2013, 06:47 AM
In my sample workbook, I forgot to add that there may be more than 24 hours.

Kenneth Hobs, If you would like to, you can post your final code so that if someone else sees this thread they can use your code instead of snb's, if they want.

The code I used is the same as snb's last post, as it was perfect.


Sub M_snb()
Columns(1).SpecialCells(2).Offset(1).SpecialCells(2).Name = "snb_002"
[snb_002].Offset(, 5) = [index(substitute(substitute(substitute(choose(len(snb_002)-len(substitute(snb_002," ",""))+1,"0:0:","0:","")&snb_002,"h ",":"),"m ",":"),"s",""),)]
End Sub

Kenneth Hobs
12-23-2013, 09:56 AM
I recently posted this code to: http://www.mrexcel.com/forum/excel-questions/746673-text-time-time.html

It was similar to your question but it had a day part as well.


Sub ParseTime()
Dim r As Range, c As Range
Dim s() As String, i As Integer
Dim d As Double

Set r = Range("A2", Range("A" & Rows.Count).End(xlUp))

For Each c In r
s() = Split(c.Value2)
d = 0
For i = 0 To UBound(s)
Select Case UBound(s)
Case 3
d = num(s(0)) + TimeSerial(num(s(1)), num(s(2)), num(s(0)))
Case 2
d = TimeSerial(num(s(0)), num(s(1)), num(s(2)))
Case 1
d = TimeSerial(0, num(s(0)), num(s(1)))
Case 0
d = TimeSerial(0, 0, num(s(0)))
Case Else
End Select
Next i
If d <> 0 Then
c.Offset(0, 1).Value = d
c.Offset(0, 1).NumberFormat = "[h]:mm:ss;@"
End If
Next c
End Sub

Function num(ByVal ref As String)
With CreateObject("VBScript.Regexp")
'.Pattern = "[A-Z]?\d+x?X?\d+\s?\-?_?\s?\d+"
.Pattern = "\d+"
num = .Execute(ref)(0)
End With
End Function