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