A reader contacted me recently with a deceptively simple Microsoft Excel question: “How do I calculate the difference between two dates?”
I say “deceptively simple” because the answer depends upon the context, namely, whether the two dates being compared are actually embedded in cells within the Microsoft Excel spreadsheet.
If they are, then the formula needed to perform the calculation is actually very simple (assuming you already know how to do simple formulas in Microsoft Excel):
If, however, the dates are not anywhere else in the spreadsheet, you’ll have to embed them in the formula itself. This is done using the DATEVALUE function:
What a lot of people don’t realize is that Microsoft Excel doesn’t actually store dates per se. It stores each date as a number calculated as the number of days from 1/1/1900, so that January 1, 1900 is “1” and all subsequent dates are successively higher numbers. The DATEVALUE function instructs Excel to convert the date back to its original number form in order to perform the arithmetic operation, in this case calculating the number of days between the two dates.
So the next time you find yourself needing to calculate the difference between the two dates, use a formula like one of those illustrated above.