Tip of the Week                    

 

November 29, 2004

 

Click HERE for Adobe version of this Tip

 

What’s so special about 1904?

Microsoft Excel supports two different date systems: the 1900 date system and the 1904 date system.  The 1904 system isn't any less "normal" than 1900. In fact, 1904 is the original date system... when a Windows-based PC was still a Bill Gates dream.

 

Background

In the 1900 date system, the first day that is supported is January 1, 1900.  This is the factory default for Microsoft Excel on a PC.  It is also the system to which all Source Group workbooks are set.  The 1900 date system allows greater compatibility between Excel and other spreadsheet programs, such as Lotus 1-2-3, that are designed to run under MS-DOS or Microsoft Windows.

 

In the 1904 date system, the first day that is supported is January 1, 1904. By default, Excel for the Macintosh uses the 1904 date system. Because of the design of early Macintosh computers, dates before January 1, 1904 were not supported; this design was intended to prevent problems related to the fact that 1900 was not a leap year.  It also comes in handy when dealing with negative times, something the 1900 system cannot do easily.

 

In either system, when you enter a date, Excel converts the date into a serial number that represents the number of elapsed days since the baseline date.  The difference between the two date systems is 1,462 days; that is, the serial number of a date in the 1900 Date System is always 1,462 days greater than the serial number of the same date in the 1904 date system. 1,462 days is equal to four years and one day (including one leap day).

 

Setting the Date System for a Workbook

In Microsoft Excel, each workbook can have its own date system setting, even if multiple workbooks are open. You can set the date system for a workbook by following these steps:

 

  1. Open or switch to the workbook.
  2. On the Tools menu, click Options. Then, click the Calculation tab (see Figure).
  3. To use the 1900 date system in the workbook, click to clear the 1904 date system check box. To use the 1904 date system in the workbook, click to select the check box.
  4. Click OK.

 

Note that if you change the date system for a workbook that already contains dates, the dates shift by four years and one day.

 

Problems Linking and Copying Dates between Workbooks

If two workbooks use different date systems, you may encounter problems when you link or copy dates between workbooks. Specifically, the dates may be shifted by four years and one day.

 

For example, if you copy a cell that contains the date July 5, 1998 from a workbook using the 1900 system to one using the 1904 system, the date will actually change to July 6, 2002.

 

Correcting Shifted Dates

If you link from or copy dates between workbooks using two different date systems, or if you change the date system for a workbook that already contains dates, the dates must be shifted by four years and one day. You can correct shifted dates by following these steps:

 

  1. In an empty cell, enter the value 1,462.
  2. Select the cell. On the Edit menu, click Copy. 
  3. Select the cells that contain the shifted dates. On the Edit menu, click Paste Special. 
  4. In the Paste Special dialog box, click Values. Then, select either ADD or SUBTRACT depending on the following (see Figure):     

 

Select This

If

Add

The dates must be shifted up by four years and one day (i.e., copying data from a 1904 system to a 1900 system).

Subtract

The dates must be shifted down by four years and one day (i.e., copying data from a 1900 system to a 1904 system).
  1. Click OK. 
  2. Repeat these steps until all of the shifted dates have been corrected.

 

If you are using a formula to link to a date in another workbook, and if the date returned by the formula is incorrect because the workbooks use different date systems, modify the formula to return the correct date, for example:

  

=[Book2]Sheet1!$A$1+1462

=[Book1]Sheet1!$A$1-1462

                                               

Book1 is using the 1900 system; Book2 is using the 1904 system.  In the formulas, 1,462 is added or deleted from the date value.

 

Dealing With Negative Time Values

Because Excel stores dates and times as numeric values, it's possible to add or subtract one from the other.

 

However, if you have a workbook containing only times (no dates), you may have discovered that subtracting one time from another doesn't always work. If the workbook is set with the 1900 date system, negative time values appear as a series of hash marks (########), even though you've assigned the [h]:mm format to the cells.

 

The solution is to use the optional 1904 date system.  By changing to this system, your negative times will now be displayed correctly.  Be careful, though, since now if your workbook contains links to other files that don't use the 1904 date system, the mismatch of date systems could cause erroneous results.