Microsoft Office Tutorials and References
In Depth Information
January 2, 1900; and so on. This system makes it possible to create formulas that perform calculations with
date. For example, you can create a formula to calculate the number of days between two dates, or to determine
the date of the third Friday of January in 2020.
You may wonder about January 0, 1900. This non-date (which corresponds to date serial number 0) is actually
used to represent times that are not associated with a particular day. This will become clear later in this chapter.
To view a date serial number as a date, you must format the cell as a date. Use the Format Cells dialog box
(Number tab) to apply a date format.
Excel 2000 and later versions support dates from January 1, 1900, through December
31, 9999 (serial number = 2,958,465). Versions prior to Excel 2000 support a much smal-
ler range of dates: from January 1, 1900, through December 31, 2078 (serial number =
Choose your date system: 1900 or 1904
Excel actually 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 Mac uses the 1904 date system —
or, at least, it used to. Microsoft made a change, and now Excel 2011 for Mac (and presumably subsequent Mac
versions) uses the 1900 date system by default.
Excel for Windows supports the 1904 date system for compatibility with Mac files. You can choose to use the
1904 date system from the Excel Options dialog box. (Choose File⇒Options and navigate to the When Calculat-
ing This Workbook section of the Advanced tab.)
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 con-
tains the date 1/15/1999 in cell A1. Further assume that Book2 uses the 1900 date system and contains a link to
cell A1 in Book1. Book2 will display the date as 1/14/1995. Both workbooks use the same date serial number
(34713), but they are 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 dis-
played. When using the 1904 date system, the negative time displays as –1:30: that is, a difference of one hour
and 30 minutes.
You can enter a date directly as a serial number (if you know it), but more often, you'll enter a date using any of
several recognized date formats. Excel automatically converts your entry into the corresponding date serial
number (which it uses for calculations) and also applies a date format to the cell so that it displays as an easily
readable date rather than a cryptic serial number.