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.
Table of contents
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.
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.
Related
Date Axis in Excel Chart is wrong
Start of month formula in Excel