ACC: Access Table with Time Field Exports Strangely to Excel (92823)
The information in this article applies to:
- Microsoft Access 1.0
- Microsoft Access 1.1
- Microsoft Access 2.0
- Microsoft Excel for Windows 4.0
- Microsoft Excel for Windows 4.0a
- Microsoft Excel for Windows 4.0c
- Microsoft Excel for Windows 5.0
This article was previously published under Q92823 SUMMARY
A Microsoft Access table that has a Time-formatted field displays a
strange date format when exported to Microsoft Excel. The Time column
on the spreadsheet displays the time as the date 1/0/00. This behavior
occurs because of the way Microsoft Excel interprets serial numbers.
To display the time correctly, apply a Microsoft Excel time format to
the affected information.
MORE INFORMATION
Microsoft Access and Microsoft Excel both use serial numbers to
display dates and times. A Microsoft Access table that is exported to
Microsoft Excel automatically formats all serial numbers as dates. The
spreadsheet column of serial numbers simply needs the format changed
to time.
To correctly display a time format in the versions of Microsoft Excel
prior to version 5.0, select the column of dates on the spreadsheet. From
the Format menu, choose Number, and select one of the time formats (for
example, hh:mm:ss AM/PM).
In Microsoft Excel version 5.0, select the column of dates on the
spreadsheet. From the Format menu, choose Cells. From the Number tab,
select one of the time formats.
REFERENCES
For more information, search for "Serial Numbers" and "Date and Time
Numbers" using the Microsoft Excel Help menu.
| Modification Type: | Major | Last Reviewed: | 5/9/2003 |
|---|
| Keywords: | kbinfo kbinterop KB92823 |
|---|
|