ACC: Access Table with Time Field Exports Strangely to Excel

• Microsoft Access versions 1.0, 1.1, and 2.0
• Microsoft Excel for Windows, versions 4.x and 5.0

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.