ACC: Imported Microsoft Excel Date Fields Are Off by 4 Years |
The information in this article applies to:
-
Microsoft Access versions 1.0, 1.1, 2.0
-
Microsoft Excel for Windows, versions 2.0, 3.0, 4.0, 5.0
SUMMARY
Novice: Requires knowledge of the user interface on single-user computers.
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:
- Open the spreadsheet in Microsoft Excel.
- On the Tools menu, click Options, and then click Calculation.
- Click to clear the 1904 Date System check box.
- 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:
- Create a new query based on the table that was created when you
imported the spreadsheet.
- Drag the Date field name to the Field row in the query grid. For this
example, the date field's name is DateField.
- On the Query menu, click Update.
- In the Update To field, enter the following:
[DateField] + 1462
- 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.
Additional query words:
Keywords : kbinterop IntpOff IsmExl5
Version : WINDOWS:1.0,1.1,2.0,3.0,4.0,5.0
Platform : WINDOWS
Issue type : kbinfo
|
|
 |
