Page 272 - Excel 2007 Bible

18_044039 ch13.qxp 11/21/06 11:05 AM Page 229
Working with Dates and Times
If none of the built-in formats meets your needs, you can create a custom number format. Select the
Custom category and then type the custom format codes into the Type box. (See Chapter 24 for information
on creating custom number formats.)
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 was even thought of. And, as I describe, the Excel
designers basically emulated the Lotus 1-2-3 program’s limited date and time features, which contain a
nasty bug duplicated intentionally in Excel.
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). 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. 13
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 millions of existing work-
books. 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 before March 1, 1900.
Pre-1900 dates
The world, of course, didn’t begin on January 1, 1900. People who use Excel to work with historical infor-
mation 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.
You can’t, however, perform any manipulation on dates entered as text. For example, you can’t change its
numeric formatting, you can’t determine which day of the week this date occurred on, and you can’t calcu-
late the date that occurs seven days later.
NOTE My Power Utility Pak add-in includes eight new worksheet functions that enable you to work
NOTE
with any date in the years 0100 through 9999. Figure 13.2 shows a worksheet that uses these
extended date functions in columns E though H to perform calculations that involve pre-1900 dates. You can
download a trial version of Power Utility Pak from my Web site (http://j-walk.com/ss), or use the
coupon in the back of the book to order a copy at a discounted price.
229