Part II: Working with Formulas and Functions
256
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 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 histori-
cal 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 example, you can enter
July 4, 1776 into a cell, and Excel won’t complain.Tip
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. This format will enable accurate
sorting. n
Using text as dates works in some situation, but the main problem is that you can’t perform any
manipulation on a date that’s entered as text. For example, you can’t change its numeric format-
ting, you can’t determine which day of the week this date occurred on, and you can’t calculate the
date that occurs seven days later.On the CD
The companion CD-ROM contains a workbook named XDATE demo.xlsm. This workbook contains eight cus-
tom worksheet functions written in VBA. These functions enable you to work with any date in the years 0100
through 9999. Figure 12.2 shows a worksheet that uses these extended date functions in columns E though G
to perform calculations that involve pre-1900 dates.
FIGURE 12.2The author’s Extended Date Functions add-in enables you to work with pre-1900 dates.