Convert a cell containing time into correct format

Gaetan321

New Member
Joined
Jan 18, 2017
Messages
14
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
Joined
Mar 11, 2015
Messages
4,958
Office Version
365
Platform
Windows
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
Joined
Jan 18, 2017
Messages
14
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
Joined
Mar 11, 2015
Messages
4,958
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Mar 11, 2015
Messages
4,958
Office Version
365
Platform
Windows
also
14 hours 4 hours 4 seconds
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,958
Office Version
365
Platform
Windows
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 :eek:
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
Joined
Nov 8, 2010
Messages
144
Office Version
365, 2016
Platform
Windows
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
Joined
Jan 18, 2017
Messages
14
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 :eek:
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
Joined
Jan 18, 2017
Messages
14
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
 

Forum statistics

Threads
1,085,758
Messages
5,385,720
Members
401,970
Latest member
xPukwe

Some videos you may like

This Week's Hot Topics

Top