Page 273 - Excel 2007 Bible

18_044039 ch13.qxp 11/21/06 11:05 AM Page 230
Part II
Working with Formulas and Functions
FIGURE 13.2
The author’s Extended Date Functions add-in enables you to work with pre-1900 dates.
Inconsistent date entries
You need to exercise caution when entering dates by using two digits for the year. When you do so, Excel
has some rules that kick in to determine which century to use. And those rules vary, depending on the ver-
sion of Excel that you use.
Two-digit years between 00 and 29 are interpreted as 21st century dates, and two-digit years between 30
th
and 99 are interpreted as 20 -century dates. For example, if you enter 12/15/28, Excel interprets your entry
as December 15, 2028. But if you enter 12/15/30, Excel sees it as December 15, 1930, because Windows
uses a default boundary year of 2029. You can keep the default as is or change it by using the Windows
Control Panel. In Windows XP, display the Regional And Language Options dialog box. Then click the
Customize button to display the Customize Regional Options dialog box. Select the Date tab and then spec-
ify a different year. This procedure may vary with different versions of Windows.
TIP
TIP The best way to avoid any surprises is to simply enter all years using all four digits for the year.
Date-Related Functions
Excel has quite a few functions that work with dates. These functions are accessible by choosing Formulas ➪
Function Library ➪ Date & Time.
Table 13.4 summarizes the date-related functions available in Excel.

TABLE 13.4
Date-Related Functions

Function Description
DATE Returns the serial number of a particular date
DATEVALUE Converts a date in the form of text to a serial number
DAY Converts a serial number to a day of the month
DAYS360 Calculates the number of days between two dates based on a 360-day year

230