ACC: Imported Microsoft Excel Date Fields Are Off by 4 Years (103996)


The information in this article applies to:

  • Microsoft Access 1.0
  • Microsoft Access 1.1
  • Microsoft Access 2.0
  • Microsoft Excel for Windows 2.0
  • Microsoft Excel for Windows 3.0
  • Microsoft Excel for Windows 4.0
  • Microsoft Excel for Windows 5.0
This article was previously published under Q103996
Novice: Requires knowledge of the user interface on single-user computers.

SUMMARY

If Date fields imported from a Microsoft Excel spreadsheet are off by four years in Microsoft Access, the 1904 date system was used on the original spreadsheet. This setting can be verified in Microsoft Excel by clicking Calculation on the Options menu. In the Sheet Options group, there is a check box labeled 1904 Date System. If the box is selected, the spreadsheet is based on the 1904 date system. If the box is not selected, the spreadsheet is based on the 1900 date system.

MORE INFORMATION

The date systems used by Microsoft Excel can be based on one of two different dates. By default, a serial number of 1 in Microsoft Excel represents January 1, 1900. The default for the serial number 1 can be changed to represent January 2, 1904. This option was included in Microsoft Excel for Windows to make it compatible with Excel for the Macintosh, which defaults to January 2, 1904.

If the spreadsheet you want to import into Microsoft Access was based on the 1904 date system, complete the following steps before importing the spreadsheet into Microsoft Access:
  1. Open the spreadsheet in Microsoft Excel.
  2. On the Tools menu, click Options, and then click Calculation.
  3. Click to clear the 1904 Date System check box.
  4. Save and close the spreadsheet.
The spreadsheet is now ready to import into Microsoft Access.

If you do not have Microsoft Excel available to switch the date system before you import the spreadsheet, you can use the following steps to perform an update query to correct the dates after the spreadsheet has been imported into Microsoft Access:
  1. Create a new query based on the table that was created when you imported the spreadsheet.
  2. Drag the Date field name to the Field row in the query grid. For this example, the date field's name is DateField.
  3. On the Query menu, click Update.
  4. In the Update To field, enter the following:

    [DateField] + 1462
  5. On the Query menu, click Run.
Note that a message box appears to tell you how many records were updated. Your dates should now appear correctly.

REFERENCES

For more information about date serial numbers, search for "NOW function," and then "NOW" using the Microsoft Excel for Windows, version 5.0 Help menu.
Modification Type:MajorLast Reviewed:5/6/2003
Keywords:kbinfo kbinterop KB103996
©2002 Microsoft Corporation. All rights reserved.