Calculating the number of days between two dates in Excel

Solution 1:

It is not a question of one formula being right and the other being wrong. It is a question of what you are looking for.

Say you are working on a task 24 hours a day. You begin the task 17 January 2000 at 9:00 AM and complete the task 18 January 2000 at 9:00 AM. If the question is How many days did the task take ? You would take the difference; get 24 hours and answer 1 day.

If, however, the question is On how many days did you work on the task ? You would immediately respond 2

Thus =B1 - A1 or =B1 - A1 + 1 might be appropriate depending on what you are trying to measure.

Solution 2:

The answer revolves around how Excel deals with dates. When days between dates are calculated using formulae such as B1-A1, Excel would turn the dates into serial numbers and use the serial numbers to calculate the number or days in between.

By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900. (Source: Microsoft)

So the days between January 1, 1900 and January 7, 1900 would equal 7 minus 1 equalling 6.

The same would apply with 1/1/2008 - 7/1/2008

January 1, 2008 is 39,447 and
January 7, 2008 is 39,453

39,453 — 39,447 = 6

If you wish to count the days inclusive you would need to add 1 to make the formula to be for example B1-A1+1.

If you want to calculate days exclusive you would need to minus 1 day making B1-A1-1