View Full Version : How do I convert seconds to true time
ecarranco
Nov 9, 2009, 04:34 PM
I have a bunch of values that are in seconds 1815632 seconds. Dividing that by 60 and the results of that by 60 I get 504.34 hours. This is close, but I don't want decimal. I need it to tell me it is 504 hrs and XX Minutes and xx seconds. This is the requirement now, but I'm sure it will change to take the seconds and convert it to days/hours/minutes/seconds eventually.
Thanks you in advance for your assistance.
Synnen
Nov 9, 2009, 05:09 PM
Multiply the decimal (.34) by 60 to get the number of minutes, then the decimal of THAT again by 60 to get the number of seconds.
JBeaucaire
Nov 9, 2009, 06:06 PM
In an adjacent cell, enter this formula:
=A1/86400
Then apply a custom number format to it like so:
[h]:mm:ss
Result: 504:20:32
Or to get it spelled out, a custom number format of:
[h] "hours" mm "minutes" ss "seconds"
Result: 504 hours 20 minutes 32 seconds
========
To include days:
d:h:mm:ss
Result: 21:00:20:32
d "days" h "hours" mm "minutes" ss "seconds"
Result: 21 days 0 hours 20 minutes 32 seconds
ecarranco
Nov 9, 2009, 06:12 PM
You folks are awesome! Thanks so very much.
Solution was.
=TEXT(A1/86400,CHOOSE(MATCH(A1,{0,60,3600},1),"s ""sec""","m ""min"" s ""sec""","[h] ""hrs"" m ""min"" s ""sec"""))
Your prompt reply is sooo very much appreciated!