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.

YouTube Date difference is 1 day wrong

Watch, like, subscribe

Excel’s date difference vs human expectation

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 actually 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.

Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course  or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.

Date Axis in Excel Chart is wrong

Start of month formula in Excel

Fixing date format or sort issues in Excel

Safest way to enter a date in Excel