Microsoft Office Tutorials and References
In Depth Information
Problems with dates
Excel’s leap year bug
A leap year , which occurs every four years, contains an additional day (February 29).
Speciﬁ cally, years that are evenly divisible by 100 are not leap years, unless they are also
evenly divisible by 400. Although the year 1900 was not a leap year, Excel treats it as such.
In other words, when you type 2/29/1900 into a cell, Excel interprets it as a valid date and
assigns a serial number of 60.
If you type 2/29/1901 , however, Excel correctly interprets it as a mistake and doesn’t
convert it to a date. Instead, it simply makes the cell entry a text string.
How can a product used daily by millions of people contain such an obvious bug? The
answer is historical. The original version of Lotus 1-2-3 contained a bug that caused it to
treat 1900 as a leap year. When Excel was released some time later, the designers knew
about this bug and chose to reproduce it in Excel to maintain compatibility with Lotus
worksheet ﬁ les.
Why does this bug still exist in later versions of Excel? Microsoft asserts that the
disadvantages of correcting this bug outweigh the advantages. If the bug were eliminated, it would
mess up millions of existing workbooks. In addition, correcting this problem would possibly
affect compatibility between Excel and other programs that use dates. As it stands,
this bug really causes very few problems because most users don’t use dates prior to
March 1, 1900.
The world, of course, didn’t begin on January 1, 1900. People who use Excel to work with
historical information often need to work with dates before January 1, 1900. Unfortunately,
the only way to work with pre-1900 dates is to enter the date into a cell as text. For
example, you can enter July 4, 1776 into a cell, and Excel won’t complain.
If you plan to sort information by old dates, you should enter your text dates with a four-digit year, followed by a
twodigit month, and then a two-digit day — for example, 1776-07-04. You won’t be able to work with these text strings as
dates, but this format will enable accurate sorting.
Using text as dates works in some situations, but the main problem is that you can’t
perform any manipulation on a date that’s entered as text. For example, you can’t change its
numeric formatting, you can’t determine which day of the week this date occurred on, and
you can’t calculate the date that occurs seven days later.
Inconsistent date entries
You need to be careful when entering dates by using two digits for the year. When you do
so, Excel has some rules that kick in to determine which century to use. And those rules
vary, depending on the version of Excel that you use.