Why is excel always 1 day short when subtracting dates

A common question is why is Excel always 1 day short when subtracting dates? As a result you always need to add 1 day if you want the answer that a human expects.

So below, I would expect to see 31 days in cell B3 being the number of days between 1 January and 31 January.

1 day short when subtracting dates

This is actualyl a human issue. Excel is assuming that each day is at the same starting point e.g. beginning of the day or end of the day on BOTH dates. But humans have become accustomed to assuming that the calculation is from the START of the first date till the END of the last date.

This only happens with dates. If I asked you the answer for 31 minus 1 you would have no problem with the answer of 30. But when it comes to dates humans tend to assume a different process.

What this means is that when you are doing date calculations in Excel, to be human correct, you will probably need to either add or subtract one. Just check that you are doing it the correct way.