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.


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:


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


To get in hours multiply it by 24 to get 13.16666667

| improve this answer | |

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.