Duration to seconds calculation

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

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.

• Thanks. I understand this . But my job is to find the actual difference. I do not know how i gonna convert 21 and odd hrs for one row, where one time is in Early morning 1:53 AM and other time is late night 11:38 PM.Kindly provide solution for this . @devnepsys Mar 6 '19 at 10:42
• @VigneshRams As I mention in the answer above, find differences of two times by Set your start and end fields to time format (Format -> Number -> Time) and your time difference calculation field to duration format (Format -> Number -> Duration). Mar 6 '19 at 11:23
• Don't do an absolute value. That gives the wrong value Mar 6 '19 at 11:50
• @mhoran_psprep If we select the correct column format then it will give correct value. I have also included the tested sheet in an answer. Mar 6 '19 at 11:58
• Oh thanks much @devnepsys .. now i see attached document contains the updated one which i was looking for .. thanks much Mar 6 '19 at 17:44

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 