Microsoft Office Tutorials and References

In Depth Information

**Chapter 6: Working with Dates and Times**

A particularly useful custom number format for displaying times is

[h]:mm:ss

Using square brackets around the hour part of the format string causes Excel to display

hours beyond 24 hours. You will find this useful when adding times that exceed 24

hours. For an example, see the “Summing times that exceed 24 hours” section later in

this chapter.

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, before the acronym Y2K became a household term.

And, as I describe, the Excel designers basically emulated the Lotus 1-2-3 limited date and time

features, which contain a nasty bug duplicated intentionally in Excel. In addition, versions of Excel

show inconsistency in how they interpret a cell entry that has a two-digit year. And finally, how

Excel interprets a date entry depends on your regional date settings.

If Excel were being designed from scratch today, I’m sure it would be much more versatile in

dealing with dates. Unfortunately, we’re currently stuck with a product that leaves much to be

desired in the area of dates.

The Excel 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 the following into a cell, Excel does not complain. It interprets this as a valid date and

assigns a serial number of 60:

2/29/1900

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:

2/29/1901

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 compatibility 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