# Convert a cell containing time into correct format

#### Gaetan321

##### New Member
Hello.

I've been googling around for a few hours and searched the forum for an answer.

I have cells containing text "14 hr 05 min 21 sec" and "4 min 20 sec" which I'd like to convert into decimal seconds and I'm looking into the formula but can't find one

Much appreciated
Regards
G

#### Yongle

##### Well-known Member
Could the value exceed 24 hours ?

Which string appears is in the cell for
- EXACTLY 14 hours (ie zero minutes and seconds)
- EXACTLY 14 hours 5 minutes (ie zero seconds)
- 14 hours and 10 seconds (ie zero minutes)

#### Gaetan321

##### New Member
Could the value exceed 24 hours ?

Which string appears is in the cell for
- EXACTLY 14 hours (ie zero minutes and seconds)
- EXACTLY 14 hours 5 minutes (ie zero seconds)
- 14 hours and 10 seconds (ie zero minutes)
Yes it can exceed 24 h

It always displays as below. i.e. if it doesn't exceed an hour, it doesn't display it. See below

"53 hr 00 min 50 sec"
"23 min 00 sec"

#### Yongle

##### Well-known Member
Thanks
Which string appears is in the cell for
- EXACTLY 14 hours (ie zero minutes and seconds)
- EXACTLY 14 hours 5 minutes (ie zero seconds)
- 14 hours and 10 seconds (ie zero minutes)

#### Yongle

##### Well-known Member
also
14 hours 4 hours 4 seconds

#### Gaetan321

##### New Member
also
14 hours 4 hours 4 seconds

The string appears exactly as below

#### Yongle

##### Well-known Member
Using helper cell B2
With value in A2
formula in B2
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"hr",":"),"min",":"),"sec","")," ","")
formula in C2
=IF(B2>TIME(24,0,0),TEXT(B2,"[h]:mm:ss")*24*60*60,B2)

single formula
formula in B2
=IFERROR(VALUE(LEFT(\$A2,SEARCH("hr",\$A2)-1)),0)*3600+VALUE(LEFT(IFERROR(RIGHT(\$A2,LEN(\$A2)-SEARCH("hr",\$A2)-2),\$A2),2))*60+VALUE(TRIM(SUBSTITUTE(RIGHT(\$A2,6),"sec","")))

the second option could probably be simplified
EDIT
here's a start!
=IFERROR(LEFT(\$A2,SEARCH("hr",\$A2)-1),0)*3600+LEFT(IFERROR(RIGHT(\$A2,LEN(\$A2)-SEARCH("hr",\$A2)-2),\$A2),2)*60+TRIM(SUBSTITUTE(RIGHT(\$A2,6),"sec",""))

Last edited:

#### CountTepes

##### Board Regular
Here is something I knocked up.
Put the code into a module or it won't work

VBA Code:
Option Compare Text

Function ConvertHMS(HMS As String) As Long

application.volatile ' If you want it to behave and recalculate like any other Excel function leave this line in. Otherwise delete/rem it.

h = InStr(1, HMS, "Hr")
If h > 0 Then
hr = Val(Left(HMS, h - 1))
End If
m = InStr(1, HMS, "Min")
If m > 0 Then
If h = 0 Then h = 1 Else h = h + 2
Min = Val(Mid(HMS, h, m - h - 1))
End If
s = InStr(1, HMS, "sec")
If s > 0 Then
If m = 0 Then m = 1 Else m = m + 3
Sec = Val(Mid(HMS, m, s - m - 1))
End If
ConvertHMS = hr * 3600 + Min * 60 + Sec

End Function
Then assuming you have 55 hr 22 min 33 sec in cell A1 if you type into cell B1, =ConvertHMS(A1)
it will display 199353 in B1

I hope this helps.

#### Gaetan321

##### New Member
Using helper cell B2
With value in A2
formula in B2
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"hr",":"),"min",":"),"sec","")," ","")
formula in C2
=IF(B2>TIME(24,0,0),TEXT(B2,"[h]:mm:ss")*24*60*60,B2)

single formula
formula in B2
=IFERROR(VALUE(LEFT(\$A2,SEARCH("hr",\$A2)-1)),0)*3600+VALUE(LEFT(IFERROR(RIGHT(\$A2,LEN(\$A2)-SEARCH("hr",\$A2)-2),\$A2),2))*60+VALUE(TRIM(SUBSTITUTE(RIGHT(\$A2,6),"sec","")))

the second option could probably be simplified
EDIT
here's a start!
=IFERROR(LEFT(\$A2,SEARCH("hr",\$A2)-1),0)*3600+LEFT(IFERROR(RIGHT(\$A2,LEN(\$A2)-SEARCH("hr",\$A2)-2),\$A2),2)*60+TRIM(SUBSTITUTE(RIGHT(\$A2,6),"sec",""))
Thanks a million. That does it.

#### Gaetan321

##### New Member
Here is something I knocked up.
Put the code into a module or it won't work

VBA Code:
Option Compare Text

Function ConvertHMS(HMS As String) As Long

application.volatile ' If you want it to behave and recalculate like any other Excel function leave this line in. Otherwise delete/rem it.

h = InStr(1, HMS, "Hr")
If h > 0 Then
hr = Val(Left(HMS, h - 1))
End If
m = InStr(1, HMS, "Min")
If m > 0 Then
If h = 0 Then h = 1 Else h = h + 2
Min = Val(Mid(HMS, h, m - h - 1))
End If
s = InStr(1, HMS, "sec")
If s > 0 Then
If m = 0 Then m = 1 Else m = m + 3
Sec = Val(Mid(HMS, m, s - m - 1))
End If
ConvertHMS = hr * 3600 + Min * 60 + Sec

End Function
Then assuming you have 55 hr 22 min 33 sec in cell A1 if you type into cell B1, =ConvertHMS(A1)
it will display 199353 in B1

I hope this helps.
Will try that too