Google-sheets – Calculating time difference

google sheets

I have 4 columns on my sheet.

A           B           C           D
10/10/2016  10:21:00 PM 10/11/2016  11:31:00 AM

How do I calculate this time difference? The simple formula =D-B is giving me a negative value.

Best Answer

In all spreadsheet programs time is stored internally as a fraction of a day. Dates are stored as floating point number counting up from a specific day.

In your case you have split the dates and times into different cells. This means that when you subtract Column D from column B the program doesn't know about the date so it dutifully says that 11:31 AM comes before 10:21 PM and gives you a negative result. Because you are formatting the result as a number you see it as negative.

To get the value you expect you want to use the following formula to recombine the date and the time before doing the subtraction:

=C1+D1-(A1+B1)

If you format this as a number is will show you:

0.5486111111

To get in hours multiply it by 24 to get 13.16666667

Related Topic