Formatting Issues

How to Keep the Correct Date Format in a Word Mail Merge

WPS Editor Sep 27, 2026 872 views

Question details

The user wants to preserve the original date format (dd/mm/yyyy) from an Excel spreadsheet when executing a Mail Merge in a Word document.

Product: Word
Device & OS: not provided
Scenario: Importing date values from an Excel data source into a Word document via the Mail Merge feature.
Observed behavior: Dates formatted as dd/mm/yyyy in the Excel source document automatically display as mm/dd/yyyy in Word during the mail merge process.

Before you start

Ensure your Excel data source is saved and closed, and verify the exact column header name containing your dates, as you will need it for the field code.

Solution 1Recommended

Use a Field Code Switch to Format the Date

You can manually apply a formatting switch directly within Word's merge field to force the date to display as dd/mm/yyyy.

By default, Word pulls the raw data from Excel without keeping its formatting. Adding a date format switch to the merge field overrides Word's default regional settings.

Toggle field codes

In your Word document, right-click the inserted date merge field and select 'Toggle Field Codes'. The field will change to look similar to { MERGEFIELD DateField }.

Edit the field code

Click inside the brackets and add the date switch to the end of the text so it reads: { MERGEFIELD DateField \@ "dd/MM/yyyy" }. Replace 'DateField' with your actual Excel column header if it differs.

Update the field

Highlight the entire field code and press 'F9' on your keyboard, or right-click and select 'Update Field'. This will apply the new formatting to your merged date.

Use a Field Code Switch to Format the Date

Uppercase M Requirement: It is crucial to use an uppercase 'M' for the month format ('MM'). In Word field code syntax, a lowercase 'm' represents minutes.

Solution 2

Format Excel Dates as Text Before Merging

Converting your date column to plain text in Excel prevents Word from applying its default date formatting during the merge.

Easy Mail Merge with WPS Office

Perform Mail Merge Flawlessly in WPS Writer

WPS Writer offers a highly compatible and intuitive Mail Merge feature. You can seamlessly connect Excel spreadsheets to generate letters, labels, and emails while maintaining full control over your data formats.

1. Open Mail Merge: Open your document in WPS Writer and navigate to the 'Mailings' tab on the top ribbon.
2. Connect Data Source: Click 'Open Data Source' and select your saved WPS Spreadsheet or Microsoft Excel file.
3. Insert and Format Fields: Use 'Insert Merge Field' to place your data. You can right-click any inserted date field and select 'Toggle Field Codes' to apply custom date formatting like \@ "dd/MM/yyyy".
4. Finalize Merge: Click 'Merge to New Document' to generate your customized files with the correct formatting applied.

Fully compatible with Microsoft Word documents and Excel data sources.Intuitive Mail Merge wizard makes connecting data sources effortless.Supports advanced field code editing for precise date and number formatting.Free, lightweight, and features a familiar user interface for seamless migration.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Word change my Excel date format during a mail merge?

When Word connects to an Excel data source, it uses OLE DB by default. This protocol transfers raw data rather than visually formatted data. Word then applies its own default regional date format (often mm/dd/yyyy) unless instructed otherwise by a specific field code switch.

What does 'Merge field is used in the main document but does not exist in the data source' mean?

This error occurs if the field name in your Word field code does not exactly match the column header in your Excel file. Ensure you replace the placeholder text in the code (e.g., 'DateField') with the exact header name used in your spreadsheet.

Can I use field codes to display dates in a long format?

Yes, you can modify the formatting switch to match any desired date format. For example, appending \@ "MMMM d, yyyy" to the field code will display the date in a long format, such as 'January 1, 2024'. Just remember to always use uppercase 'M' for months.

Document & File Issues