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

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.