Microsoft Office Tutorials and References
In Depth Information
A leap year, which occurs every four years, contains an additional day (February 29). Specifically, 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 the following into a cell, Excel
does not complain. It interprets this as a valid date and assigns a serial number of 60:
If you type the following invalid date, Excel correctly interprets it as a mistake and doesn't convert it to a date.
Rather, 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 consider 1900 as a leap year. When Excel was
released some time later, the designers knew of this bug and chose to reproduce it in Excel to maintain compat-
ibility with Lotus worksheet files.
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 hundreds of thousands of exist-
ing workbooks. In addition, correcting this problem would affect compatibility between Excel and other pro-
grams that use dates. As it stands, this bug really causes very few problems because most users do not use dates
before March 1, 1900.
The world, of course, didn't begin on January 1, 1900. People who work with historical information using Excel
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 type the following into a cell, and Excel won't com-
July 4, 1776
If you plan to sort information by old dates entered as text, you should enter your text
dates with a four-digit year, followed by a two-digit month, and then a two-digit day, like
this: 1776-07-04. This format will enable accurate sorting.
You can't, however, perform any manipulation on dates recognized 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.