Formatting Issues

How to Keep Excel Date Formatting in a Word Mail Merge

Chanuka Geekiyanage Oct 7, 2026 869 views

Question details

The user needs to maintain specific date formatting (such as dd.mm.yyyy) from an Excel spreadsheet when importing the data into a Word mail merge.

How to Keep Excel Date Formatting in a Word Mail Merge

Product: Microsoft Word and Excel
Device & OS: not provided
Scenario: Setting up a mail merge document where one of the imported fields is a date from a spreadsheet.
Observed behavior: During the mail merge, the word processor ignores the visual date format applied in Excel and defaults to a different, raw date format.

Before you start

Ensure your Excel data source is saved and closed before running the mail merge, and note the exact column header name of your date field.

Solution 1Recommended

Use a Date Picture Switch in Merge Field Codes

This is the most reliable method. By manually adding a formatting switch directly into the Word field code, you can dictate exactly how the date appears regardless of the original Excel format.

When performing a mail merge, Word pulls the raw underlying value from Excel, not the display format. To fix this, you must tell the word processor exactly how to display that raw value by appending a date picture switch to the merge field.

Reveal Document Field Codes

In your Word document, press 'Alt + F9' (or 'Option + F9' on a Mac). This will reveal the hidden field codes. Your date field will look something like { MERGEFIELD Birthdate }.

Add the Date Picture Switch

Click inside the brackets of the date merge field and type the formatting switch \@ "dd.MM.yyyy" at the end of the field name. Your code should now look exactly like this: { MERGEFIELD Birthdate \@ "dd.MM.yyyy" }.

Hide Field Codes and Update

Press 'Alt + F9' again to hide the field codes. Finally, select the field and press 'F9' to update it, or simply click 'Preview Results' in the Mailings tab to see your correctly formatted date.

Use a Date Picture Switch in Merge Field Codes

Important Formatting Rule: Always use uppercase 'MM' for months in your switch (e.g., dd.MM.yyyy). In field codes, lowercase 'mm' is reserved for minutes.

Seamless Mail Merge with WPS Office

Master Mail Merge with WPS Writer

WPS Writer offers a highly compatible and intuitive Mail Merge feature that seamlessly imports spreadsheet data. It fully supports standard field codes, allowing you to easily maintain custom date and number formatting.

1. Start the Mail Merge: Open WPS Writer, navigate to the 'Mailings' tab, and click 'Mail Merge' to activate the tools.
2. Connect Your Spreadsheet: Click 'Open Data Source' and select your WPS Spreadsheet or Excel file containing the date values.
3. Insert and Format the Field: Insert the Date field into your document. Right-click the field, choose 'Toggle Field Codes', and append \@ "dd.MM.yyyy" to the code.
4. Preview Results: Click 'View Merged Data' on the ribbon to verify that your dates are formatted exactly as needed.

Fully compatible with Microsoft Word (.docx) and Excel (.xlsx) formatsIntuitive Mail Merge wizard for bulk letters, emails, and labelsRobust field code support for advanced date and number formattingLightweight, fast, and free to use

Frequently Asked Questions

Why does my mail merge show the wrong date format?

Mail merge tools pull the raw, unformatted data from your spreadsheet. The visual formatting applied in the spreadsheet is not carried over. You must apply a specific display switch (field code) directly inside the text document to format it correctly.

How can I change the date format to Month Day, Year (e.g., January 1, 2023)?

Change the picture switch in your field code to \@ "MMMM d, yyyy". For example, your field code should look like this: { MERGEFIELD Date \@ "MMMM d, yyyy" }.

Can I apply similar formatting to numbers and currency in a mail merge?

Yes. Just like dates, you can format numbers by adding a numeric picture switch instead of a date switch. For example, use \# "$#,##0.00" at the end of your merge field to format a raw number as currency.

How do I toggle field codes for a single field instead of the whole document?

If you do not want to use Alt + F9 to toggle all field codes at once, you can simply right-click the specific merge field in your document and select 'Toggle Field Codes' from the context menu.

Document & File Issues