Leading the way in Microsoft Office Development

Step 1 - What is the 1904 Date System ?

Excel actually supports two dates systems: A 1900 date system and a 1904 date system.

The 1900 date system is the default if you are using Excel for Windows.

The 1904 date system is to provide compatibility with Macintosh Excel files and is the default if you are using Excel for Macintosh..

The difference between the two date systems is the starting date for the serial numbers.

The 1900 date system uses 1st January 1900 as serial number "1".

The 1904 date system uses 2nd January 1904 as serial number "1".

You can change which date system you want to use in a workbook from your (Tools > Options).

Step 2 - Check your Options

You can switch to the 1904 date system by selecting (Tools > Options)(Calculation tab, "1904 Date System").

You should be careful if you are using different date systems in workbooks that are linked together.

The workbooks will contain the same serial numbers but they will represent different dates.

When you change the date system the date serial number in your workbook do not change but the actual date displayed will change.

Step 3 - Using 1904 Dates

1904 date system: If year is between 4 and 1899 (inclusive), Excel adds that value to 1900 to calculate the year. For example, DATE(100,1,2) returns January 2, 2000 (1900+100).

1904 date system: If year is between 1904 and 9999 (inclusive), Excel uses that value as the year. For example, DATE(2000,1,2) returns January 2, 2000.

1904 date system: If year is less than 4 or is 10000 or greater or if year is between 1900 and 1903 (inclusive), then #NUM! is returned.

Step 4 - Negative Times

One advantage of the 1904 date system is that it allows you display negative times.

How can you display negative numbers ?

Step 5 - Things to Remember

If you have entered dates into a workbook using the 1900 date system and then you switch to the 1904 date system then all your dates will be out by 4 years.

You should be very careful if you are using different date systems in two linked workbooks as the same serial numbers will be interpreted as different dates.

One advantage of using the 1904 date system is that negative times can be displayed, i.e. (8:00 PM - 9:00 PM) = -1:00