Part II: Working with Formulas and Functions
250
A serial number of 1 corresponds to January 1, 1900; a serial number of 2 corresponds to January
2, 1900, and so on. This system makes it possible to deal with dates in formulas. For example, you
can create a formula to calculate the number of days between two dates (just subtract one from the
other).Excel support dates from January 1, 1900, through December 31, 9999 (serial number =
2,958,465).You may wonder about January 0, 1900. This nondate (which corresponds to date serial number 0)
is actually used to represent times that aren’t associated with a particular day. This concept
becomes clear later in this chapter (see “Entering times”).To view a date serial number as a date, you must format the cell as a date. Choose Home ➪
Number ➪ Number Format. This drop-down control provides you with two date formats. To
select from additional date formats, see “Formatting dates and times,” later in this chapter.Entering dates .........................................................................................................
You can enter a date directly as a serial number (if you know the serial number) and then format it
as a date. More often, you enter a date by using any of several recognized date formats. Excel auto-
matically converts your entry into the corresponding date serial number (which it uses for calcula-
tions), and it also applies the default date format to the cell so that it displays as an actual date
rather than as a cryptic serial number.Excel supports two date systems: the 1900 date system and the 1904 date system. Which system you
use in a workbook determines what date serves as the basis for dates. The 1900 date system uses
January 1, 1900 as the day assigned to date serial number 1. The 1904 date system uses January 1,
1904, as the base date. By default, Excel for Windows uses the 1900 date system, and Excel for
Macintosh uses the 1904 date system. Excel for Windows supports the 1904 date system for compati-
bility with Macintosh files. You can choose the date system for the active workbook in the Advanced
section of the Excel Options dialog box. (It’s in the When Calculating This Workbook subsection.) You
can’t change the date system if you use Excel for Macintosh.Generally, you should use the default 1900 date system. And you should exercise caution if you use
two different date systems in workbooks that are linked. For example, assume that Book1 uses the 1904
date system and contains the date 1/15/1999 in cell A1. Assume that Book2 uses the 1900 date system
and contains a link to cell A1 in Book1. Book2 displays the date as 1/14/1995. Both workbooks use the
same date serial number (34713), but they’re interpreted differently.One advantage to using the 1904 date system is that it enables you to display negative time values.
With the 1900 date system, a calculation that results in a negative time (for example, 4:00 PM–5:30
PM) cannot be displayed. When using the 1904 date system, the negative time displays as –1:30 (that
is, a difference of 1 hour and 30 minutes).Choose Your Date System: 1900 or 1904