Posted by: mmeazaw | August 4, 2010

The 1900 Date System vs. the 1904 Date System (XL: System)


The 1900 Date System

In the 1900 date system, the first day that is supported is January 1, 1900. When you enter a date, the date is converted into a serial number that represents the number of elapsed days since January 1, 1900. For example, if you enter July 5, 1998, Microsoft Excel converts the date to the serial number 35981.

By default, Microsoft Excel for Windows and Microsoft Excel for Windows NT use the 1900 date system. The 1900 date system allows greater compatibility between Microsoft Excel and other spreadsheet programs, such as Lotus 1-2-3, that are designed to run under MS-DOS or Microsoft Windows.

The 1904 Date System

In the 1904 date system, the first day that is supported is January 1, 1904. When you enter a date, the date is converted into a serial number that represents the number of elapsed days since January 1, 1904. For example, if you enter July 5, 1998, Microsoft Excel converts the date to the serial number 34519.

By default, Microsoft 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. Note that if you switch to the 1900 date system, Microsoft Excel for the Macintosh does support dates as early as January 1, 1900.

The Difference between the Date Systems

Because the two Date Systems use different starting days, the same date is represented by different serial numbers in each date system. For example, July 5, 1998 can have two different serial numbers.

Serial number

Date system of July 5, 1998

———————————-

 

1900 date system 35981

1904 date system 34519

                

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 (or Edit menu in Excel 2001 for Mac), click Options or Preferences. Then, click the Calculation tab.
  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. For information about correcting shifted dates, see the “Correcting Shifted Dates” section in this article.

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.

To see an example of this behavior, follow these steps:

  1. In Microsoft Excel, create two new workbooks (Book1 and Book2).
  2. Use the steps in the “Setting the Date System for a Workbook” section to use the 1900 date system in Book 1. Use the 1904 date system in Book2.
  3. In Book1, enter the date July 5, 1998.
  4. Select the cell that contains the date, and click Copy on the Edit menu.
  5. Switch to Book2, select a cell, and click Paste on the Edit menu.The date is pasted as July 6, 2002. Note that the date is four years and one day later than the date in step 3 because Book2 uses the 1904 date system.

  6. In Book2, enter the date July 5, 1998. Select the cell that contains the date and click Copy on the Edit menu.
  7. Switch to Book1, select a cell, and click Paste on the Edit menu.

The date is pasted as July 4, 1994. It has been shifted down by four years and one day because Book1 uses the 1900 date system.

Correcting Shifted Dates

If you link from or copy dates between workbooks, or if you change the date system for a workbook that already contains dates, the dates may 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 1462.
  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 of the following option buttons.
Advertisements

Responses

  1. i like your post mazi, it is a nice work, keep it up!!!!

  2. thank you for your postive comments!
    i will try to post any relevant informations that have a postive meaning to my profession and my country; your comments accelerate my devotion for the technology transfer through our country keep your comments and start using blog to post your idea for our world.
    keep in touch!!

  3. Nice copy/paste “work”.

    Only you forgot to include the last part of the article at http://support.microsoft.com/kb/180162

  4. this is absolutely retarded by microsoft…

    they KNOW the “date system” for each spreadsheet… its stored in the header of the spreadsheet!!!

    why on earth cant they adjust the date as necessary?

  5. This is a Question of all human being !
    How Can ajust the natural Date sysem?
    Can a human being chang this process ?


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories