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

3 Answers 3


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 Commented Mar 6, 2019 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).
    – knilkantha
    Commented Mar 6, 2019 at 11:23
  • Don't do an absolute value. That gives the wrong value Commented Mar 6, 2019 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.
    – knilkantha
    Commented Mar 6, 2019 at 11:58
  • 1
    Oh thanks much @devnepsys .. now i see attached document contains the updated one which i was looking for .. thanks much Commented Mar 6, 2019 at 17:44

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


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
  • Thanks much for the explanation.. Cheers mate !!! Commented Mar 6, 2019 at 18:11

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



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.