0

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

2

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 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 at 11:23
  • Don't do an absolute value. That gives the wrong value – mhoran_psprep Mar 6 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 at 11:58
  • @devnepsys.. Even in the screenshot attached, you have shown one column with 78345.What will do with that value ???? I do not want that type of calculation . i just need the actual difference between two time. even one time is before midnight and another one is early morning .. i just need the actual difference .. not the one you have highlighted in your screenshot. i also went thru the sheet u attached . it also shows the same – Vignesh Rams Mar 6 at 12:00
1

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
  • Thanks much for the explanation.. Cheers mate !!! – Vignesh Rams Mar 6 at 18:11
0

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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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