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 con-
vert 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.
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 con-
vert 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.
No comments:
Post a Comment