In this article we will discuss why calculating the number of elapsed days between two dates in Excel sometimes gives “wrong results”.
Check out the dates and times spreadsheet to see the example illustrated here.
Checkout the Dates and Times example spreadsheet (XLS 56 KB) for these and other date and time examples.
Very often a simple calculation of the number elapsed days, between two correctly entered dates, gives surprising results. The correct simple difference formula, for cell C4, is shown on the right. When this formula is entered and copied down the column, instead of getting the expected result, we get the rather disconcerting result below!
The dates in our example might look unusual to some readers! The date convention used in New Zealand is dd/mm/yyyy rather than mm/dd/yyyy as used in North America and elsewhere.
What has gone wrong here?
The calculation is actually correct but what we have in fact is a simple formatting problem. When the formula is entered into column C, Excel automatically (and stupidly!) formats the column using the same date format as in the adjacent columns. Reformatting the column using a number format (I chose -1234 to make the negative numbers red) fixes the problem and gives the display above.
How do you explain the incorrect “dates” displayed in column C?
In case you are wondering there is also a simple explanation for these as well.
Excel stores dates as serial numbers. I wrote this article on 25/10/2006 and the serial number that day was 39,015. (How did I work this out? I entered 25/10/06 into a spreadsheet cell and reformatted it as number.) The next day's number would be 39,016. This series is based on day number 1 being 01/01/1900. Thus if the correct answer for the number of elapsed days is 30, as in C4, it incorrectly displays as 30/01/1900. Similarly, instead of 60 we get 29/02/1900. Because there is no such thing (in Excel) as a negative date serial number, -247 in cell C7, it displays as #############.
An interesting fact about spreadsheet dates!
Did you know that there was no such date as 29/02/1900? The 60th day should really be 01/03/1900 because leap years occur every four years except for the first year in centuries not divisible by 4. Excel didn’t invent the idea of using serial numbers to store dates and Microsoft chose to implement dates the same way and thus inherited this problem from earlier spreadsheets such as Lotus 123.