Dates are often a critical part of data analysis. You often ask questions such as: when was a product purchased, how long will a task in a project take, or what is the average revenue for a fiscal quarter? Entering dates correctly is essential to ensuring accurate results. But formatting dates so that they are easy to understand is equally important to ensuring correct interpretation of those results.

Important Because the rules that govern the way that any calculation program interprets dates are complex, you should be as specific as possible about dates whenever you enter them. This will produce the highest level of accuracy in your date calculations.What do you want to do?

Learn about date calculations and formats

Microsoft Office Excel stores dates as sequential numbers that are called serial values. For example, in Microsoft Office Excel for Windows, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Excel stores times as decimal fractions because time is considered a portion of a day.

Because dates and times are values, they can be added, subtracted, and included in other calculations. You can view a date as a serial value and a time as a decimal fraction by changing the format of the cell that contains the date or time to General format.

For more information, see the following help topics :

Change the way two-digit years are interpreted

Important To ensure that year values are interpreted as you intended, type year values as four digits (for example, 2001, not 01). By entering four-digit years, Excel won't interpret the century for you.

If you enter a date with a two-digit year in a text formatted cell or as a text argument in a function, such as =YEAR("1/1/31"), Excel interprets the year as follows:

In Microsoft Windows XP, you can change the way two-digit years are interpreted for all Microsoft Windows programs that you have installed.

  1. On the Windows Start menu, click Control Panel.
  2. Double click the Regional and Language Options icon or folder.
  3. In the Regional and Language Options dialog box, click the Regional Options tab.
  4. Click Customize.
  5. Click the Date tab.
  6. In the When a two-digit year is entered, interpret it as a year between box, change the upper limit for the century.

    As you change the upper-limit year, the lower-limit year automatically changes.

Change the default date format to display four-digit years

By default, as you enter dates in a workbook, the dates are formatted to display two-digit years. When you change the default date format to a different format by using this procedure, the display of dates that were previously entered in your workbook will change to the new format as long as the dates haven't been formatted by using the Cells command (under the Format menu).

  1. On the Windows Start menu, click Control Panel.
  2. Double click the Regional and Language Options icon or folder.
  3. In the Regional and Language Options dialog box, click the Regional Options tab.
  4. Click Customize.
  5. Click the Date tab.
  6. In the Short date format list, click a format that uses four digits for the year ("yyyy").

Change the date system

Excel supports two date systems: the 1900 and 1904 date systems. The default date system for Microsoft Office Excel for Windows is 1900. The default date system for Microsoft Office Excel for the Macintosh is 1904.

The following table shows the first date and the last date for each date system and the serial value associated with each date.

Date system First date Last date
1900 January 1, 1900
(serial value 1) 		December 31, 9999
(serial value 2958465)
1904 January 2, 1904
(serial value 1) 		December 31, 9999
(serial value 2957003)

The date system changes automatically when you open a document from another platform. For example, if you are working in Excel for Windows and you open a document created in Excel for the Macintosh, the 1904 date system check box is selected automatically.

You can change the date system by doing the following:

  1. Click the Microsoft Office Button button image, click Excel Options, and then click the Advanced category.
  2. Under the When calculating this workbook section, select the workbook you want, and then select or clear the Use 1904 date system check box.