Chapter 12: Working with Dates and Times
255
FIGURE 12.1Use the Number tab in the Format Cells dialog box to change the appearance of dates and times.
Problems with dates ................................................................................................
Excel has some problems when it comes to dates. Many of these problems stem from the fact that
Excel was designed many years ago. Excel designers basically emulated the Lotus 1-2-3 program’s
limited date and time features, which contain a nasty bug that was duplicated intentionally in
Excel. (You can read why in a bit.) If Excel were being designed from scratch today, I’m sure it
would be much more versatile in dealing with dates. Unfortunately, users are currently 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- 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. 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 treat 1900 as a leap
year. When Excel was released some time later, the designers knew of this bug and chose to repro-
duce it in Excel to maintain compatibility with Lotus worksheet files.