Convert a cell containing time into correct format

[Gaetan321]

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]

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]

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]

Thanks
Please also answer my SPECIFIC questions
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]

also
14 hours 4 hours 4 seconds

 

[Gaetan321]

also
14 hours 4 hours 4 seconds

The string appears exactly as below

 

[Yongle]

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",""))

 

[CountTepes]

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

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]

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]

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

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