Part II: Working with Formulas and Functions
program’s limited date and time features, which contain a nasty bug that was duplicated
intentionally in Excel (described next). If Excel were being designed from scratch today, I’m
sure it would be much more versatile in dealing with dates. Unfortunately, users are cur-
rently stuck with a product that leaves much to be desired in the area of dates.
Excel’s leap year bug
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 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 con-
vert 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
1-2-3 worksheet files.
Why does this bug still exist in later versions of Excel? Microsoft asserts that the disadvan-
tages 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.
Pre-1900 dates
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 exam-
ple, 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 two-
digit 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 per-
form manipulation on a date that’s entered as text. For example, you can’t change its
numeric formatting, you can’t determine on which day of the week this date occurred, and
you can’t calculate the date that occurs seven days later.
