How to Convert Excel Serial Dates in a Linked Access Table
Question details
Users need to convert five-digit serial numbers back into standard readable dates when linking or importing an Excel worksheet into an Access database.
- Product
- Microsoft Excel and Access
- Device & OS
- not provided
- Scenario
- Linking or importing an Excel worksheet into an Access database where date fields are incorrectly interpreted by the system.
- Observed behavior
- Dates from the Excel worksheet appear as five-digit text values or integers instead of standard formatted dates in Access.
Before modifying your database, ensure that the original Excel file contains valid date values rather than mixed text, and check that the Excel column is consistently formatted.
Format the Control to Short Date in Access
The quickest way to resolve display issues is by changing the format property of the specific form or report control without altering underlying data.
Access stores dates as double-precision numbers where the integer represents the days since December 31, 1899. If you see a five-digit number, Access is displaying the underlying numeric value instead of the formatted date.
Open your Access form or report in Design View by right-clicking its tab and selecting 'Design View'.
Right-click the specific control containing the five-digit serial date and select 'Properties' from the context menu.
Go to the 'Format' tab in the Property Sheet, locate the 'Format' row, and select 'Short Date' (or your preferred date format) from the drop-down list.
Save the changes and switch back to Form View or Report View to verify that the numbers now display as readable dates.
Convert Text Serial Dates Using a Query
If the linked Excel dates are being imported as text rather than numbers, use a query with the CDate function to convert them into a readable format.
Looking for a Seamless Spreadsheet Experience? Try WPS Office
While Microsoft Access and Excel require careful formatting for data linking, you can simplify your daily spreadsheet and document tasks with WPS Office. It provides a highly compatible, lightweight, and free alternative for managing complex datasets without a steep learning curve.
- 1. Download and Install: Get WPS Office for free from the official website and install it on your device in minutes.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing Microsoft Excel files seamlessly without losing formatting.
- 3. Format Dates Easily: Select your data columns, right-click to choose 'Format Cells', and apply your desired date format with a single click.
Frequently Asked Questions
Why does Excel store dates as five-digit numbers?
Excel stores dates as sequential serial numbers so they can be easily used in calculations. By default, January 1, 1900, is serial number 1, and subsequent days are counted from there, resulting in five-digit numbers for modern dates.
Will changing the date format in Access alter my original Excel file?
No, changing the display format in an Access form, report, or query only affects how the data is viewed within the Access database. Your original Excel workbook remains completely unchanged.
How do I handle blank date fields when using the CDate function?
You can use an IIF statement in your Access query to check for null or non-numeric values before converting. For example, use 'ConvertedDate: IIf(IsNumeric([DateColumn]), CDate([DateColumn]), Null)' to avoid errors.
Can I prevent this issue before linking the Excel file to Access?
Yes. Before establishing the link in Access, open your Excel spreadsheet and ensure the entire date column is explicitly formatted as 'Short Date' rather than 'General' or 'Text', and verify there are no mixed data types in the column.
