Microsoft KB Archive/92823

= ACC: Access Table with Time Field Exports Strangely to Excel =

Article ID: 92823

Article Last Modified on 5/9/2003

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Excel 4.0 Standard Edition
 * Microsoft Excel 4.0a
 * Microsoft Excel 4.0c
 * Microsoft Excel 5.0 Standard Edition

-



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.

