time to seconds

  • Thread starter Thread starter inungh
  • Start date Start date
I

inungh

I have a spreadsheet which contains time information like follwoing
format

0h 15m 35s
7h 1m 5s

and I would like to convert in seconds,

Are there any functions or formula to do this?


Your help is great appreciated,
 
I made a custom function. Put this code into a standard module. I assumed
your hours, minutes, and seconds will have spaces between them. Plus I
assumed it all will be in 1 cell. If your data is in A1 put this formula in
B1 "=ConvertTime(A1)". See what happens. Hope this helps! If so, let me
know, click "YES" below.

Function ConvertTime(MyRange As Range) As String

Dim MyArray As Variant

MyArray = Split(MyRange)
ConvertTime = Val(MyArray(0)) * 360 + Val(MyArray(1)) * 60 +
Val(MyArray(2))

End Function
 
I assume the following:-

These are not correctly formatted times
They will all be in the format of your examples.


Try this

=(LEFT(A1,FIND("h",A1)-1)*3600)+(MID(A1,FIND(" ",A1)+1,FIND("m",A1)-(FIND("
",A1)+1))*60)+(SUBSTITUTE(MID(A1,FIND(CHAR(7),SUBSTITUTE(A1,"
",CHAR(7),2))+1,999),"s",""))

all one line
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
Are those times as strings or time-values with a custom number-format. If
the latter multiply by (24*60*60)

Regards,
Peter T
 
Back
Top