Duration to seconds calculation

Question

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

Answer 1

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.

Answer 2

Looking at you example spreadsheet the calculation in seconds is done with the following formula:

=86400*(if(D4-C4<0,1,0)+D4-C4)

This is the breakdown of the formula:

• time and dates in spreadsheets are floating point numbers.
• D4-C4 is subtracting the start time from the end time.
• The if statement is cheeking if the subtraction is negative, that means you have to add a day because the time frame crossed over midnight. Taking the absolute value doesn't work.
• 86400 is the number of seconds in a day.

looking at the row where midnight is crossed:

 Start time 11:38:56 PM end time 1:53:11 AM
 D4-C4 is -0.90677
 It is negative so add 1: 0.09323
 now multiply by 86400: 8055 seconds

looking at one where the initial subtraction is positive

 start time 5:52:33 AM  end time 7:29:11 AM
 D4 - C4 is 0.06711
 No need to add 1
 multiply by 86400:  5798 seconds

Answer 3

=RIGHT(E4, 2)+MID(E4, 4, 2)*60+LEFT(E4, 2)*60*60