# 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 – Vignesh Rams 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). – devnepsys Mar 6 '19 at 11:23
• Don't do an absolute value. That gives the wrong value – mhoran_psprep 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. – devnepsys 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 – Vignesh Rams 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
``=RIGHT(E4, 2)+MID(E4, 4, 2)*60+LEFT(E4, 2)*60*60`` 