Google Sheets – Convert Duration to Seconds

formulasgoogle sheetsgoogle-sheets-cell-formatgoogle-sheets-dates

There are 3 columns in my google sheet with heading Start time (col C), End Time (col D) and time taken (col E). Column E is arrived by finding the difference between col D and col C. Now when I try to convert col E into seconds, I am not getting the required output. Pls help me in solving this query by having the correct value in column E

Example Worksheet

Best Answer

Set your start and end fields to time format (Format -> Number -> Time) and your time difference calculation field to duration format (Format -> Number -> Duration).

Once you have done that you can just add and subtract times like normal number to get the time difference. However, you will get a negative time if one event stretches past midnight. So, apply if condition and add 24 hours whenever the event stretches midnight.

=IF(F13-E13>0, F13-E13, F13-E13+1)

or you can manually prevent this condition like here.

A time value is stored as a fraction in google sheet, where 1 would be exactly 24 hours. E.g. 12 o'clock midnight is 0, 6 o'clock in the morning is 0.25 12 o'clock midday is 0.5 and 18 o'clock is 0.75

To calculate the number of seconds, you can use the Hour(), Minute() and Second() functions on that field.

=(((HOUR(G14))*60+(MINUTE(G14)))*60 +SECOND(G14))

Here is the link to illustration sheet.

Related Topic