Menu
Multiply the hours by 60 to convert it to minutes, i.e., 1 hr × 60 = 60 minutes . Multiply the minutes by 60 to obtain the number of seconds, i.e., 60 minutes × 60 = 3600 seconds .
The duration between two times is easy to find in Google Sheets. Just minus one time from another and apply the Duration number format > number > duration.
Because each hour can be represented as 1/24, you can convert an Excel time into decimal hours by multiplying the value by 24, convert to decimal minutes by multiplying the value by 1440 (24 * 60) , and convert to seconds by multiplying by 86400 (24 * 60 * 60). The Excel time 6:00 converts to 21,600 seconds.
To convert duration to an integer expressing the number of seconds, use a formula such as
=value(A1*24*3600)
Time values are recorded so that 1 is one day. Multiplying by 24 (hours/day) and 3600 (seconds/hour) converts that to seconds. Then value makes it a number rather than duration.
Old answer, about formatting only.
You don't need any formulas to format duration as the number of seconds.
I have found this solution:
let the cell A1 filled with duration like 1:22:33, than formula
=HOUR(A1)*3600+MINUTE(A1)*60+SECOND(A1)
will do the trick.
For example, 1:01:01 -> 3661
=HOUR(A1) will NOT work if your hours in the duration is > 24 of course. So the last example is not correct.
What will work is the following.
Given: A duration in hours and minutes. eg 225:04 or 9:20 or 62:35
Format must be set as this (Elapsed hours:minutes)
=INDEX(SPLIT(A1, ":"), 0, 1)*60 + INDEX(SPLIT(A1, ":"), 0, 2)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With