Formatting Issues

How to Fix Excel Dates Showing as Serial Numbers in Word Mail Merge

Steve K Oct 7, 2026 869 views

Question details

Users experience an issue where formatted dates from an Excel spreadsheet are displayed as unformatted five-digit serial numbers when imported into a Word mail merge document.

How to Fix Excel Dates Showing as Serial Numbers in Word Mail Merge

Product: Microsoft Word and Excel
Device & OS: not provided
Scenario: Generating letters, labels, or emails using the Mail Merge feature in Word, with an Excel spreadsheet acting as the data source.
Observed behavior: Instead of showing standard date formats like '10/25/2023', the Word mail merge output displays raw Excel date serial numbers such as '45224'.

Before you start

Ensure your Excel data source is saved and closed before applying formatting changes in Word to prevent file-locking conflicts and ensure the connection updates smoothly.

Solution 1Recommended

Apply a Date Switch to the Word Merge Field

Modify the underlying field code in Word to explicitly tell the application to format the incoming raw serial number as a calendar date.

This is the most reliable method, as it forces Word to format the data accurately regardless of how it visually appears in your Excel spreadsheet.

Reveal Field Codes

In your Word document, press 'Alt + F9' (or 'Option + F9' on a Mac). This toggles your document view to show field codes, changing your placeholder from a standard field to something that looks like { MERGEFIELD Date }.

Add the Date Format Switch

Click inside the field braces and add the date switch \@ "MM-dd-yy" right after the field name. Your code should now look like { MERGEFIELD Date \@ "MM-dd-yy" }. Note that the curly braces must remain the original ones generated by Word; do not type them manually.

Update and View the Field

Press 'F9' to update the selected field. Then, press 'Alt + F9' again to hide the underlying field codes and display the newly formatted date. Preview your results to confirm the serial numbers have vanished.

Apply a Date Switch to the Word Merge Field

Customizing the Date Format: You can change the switch to match your desired format. For example, using \@ "MMMM d, yyyy" will output 'October 25, 2023', and \@ "dd/MM/yyyy" will output '25/10/2023'.

Solution 2

Use a Helper Column in Excel using the TEXT Function

Convert the date values into plain text directly within the Excel data source, forcing Word to read it exactly as it appears.

Free Microsoft Office alternative

Experience Hassle-Free Mail Merge with WPS Office

Struggling with complicated formatting switches in Microsoft Word? WPS Office provides a highly compatible, streamlined alternative for word processing and spreadsheets, offering flawless data integration without the premium price tag.

1. Download WPS Office: Get the official WPS Office suite for free and install it on your computer.
2. Open Existing Documents: Launch WPS Writer and open your standard .docx file, and use WPS Spreadsheets for your .xlsx data files.
3. Execute the Mail Merge: Navigate to the 'References' tab and use the intuitive Mail Merge wizard to flawlessly pair your data.

Highly compatible with Microsoft Word (.docx) and Excel (.xlsx) file formatsRobust and user-friendly mail merge tool that perfectly imports spreadsheet dataFamiliar user interface ensuring a zero-learning-curve transitionCompletely free to download and lightweight enough for any PC or Mac

microsoft office alternative - wps office

Frequently Asked Questions

Why does Word show my Excel dates as serial numbers?

Excel fundamentally stores all dates as sequential serial numbers starting from January 1, 1900, to allow for mathematical calculations. By default, Word’s OLE DB connection imports this raw underlying data rather than the visual date format applied in your spreadsheet.

Will the date format switch work for currency or percentages?

No, the \@ switch is explicitly designed for dates and times. To format numbers, currency, or percentages, you must use a numeric picture switch, such as \# "$#,##0.00" for currency.

What if pressing Alt+F9 doesn't reveal the field codes?

On many modern laptops and Mac keyboards, the Function (F) keys serve dual purposes like controlling volume or brightness. You may need to press 'Fn + Alt + F9' or 'Option + F9'. You can also simply right-click the specific merge field and select 'Toggle Field Codes'.

Can I permanently fix this by changing how Word connects to Excel?

Yes, you can force Word to connect using Dynamic Data Exchange (DDE) by going to Word Options > Advanced > General, and checking 'Confirm file format conversion on open'. However, DDE is considered a legacy technology and may cause your merge process to run significantly slower.

Document & File Issues