How to Fix Incorrect Month and Day Values in Word Mail Merge Dates
Question details
Users experience reversed month and day values when performing a mail merge in Word using an Excel spreadsheet as the data source.
- Product
- Microsoft Word / Excel
- Device & OS
- not provided
- Scenario
- Merging dates from an Excel spreadsheet into a Word document.
- Observed behavior
- Dates display incorrectly with the month and day reversed in Word, despite using UK date formats in Excel and a basic date switch in the Word field.
Ensure your Excel data source contains true date values rather than plain text strings, and check that your computer's regional settings match your desired date format.
Apply a Specific Date Formatting Switch in Word
Use a field code switch in Word to force the merged date to display in the correct day-month-year format.
By default, Word connects to Excel using OLE DB, which often reads date values in the US format (MM/DD/YYYY) regardless of how they are formatted in the spreadsheet. Adding a date switch explicitly tells Word how to render the final output.
Press `Alt + F9` (or `Fn + Alt + F9` on some keyboards) in your Word document to reveal the mail merge field codes.
Find the field code corresponding to your date, which will look something like `{ MERGEFIELD Date }`.
Modify the field to include the date format switch at the end. For a UK format, change it to: `{ MERGEFIELD Date \@ "dd MMMM yyyy" }` for text months, or `{ MERGEFIELD Date \@ "dd/MM/yyyy" }` for numbers.
Press `Alt + F9` again to hide the field codes. Select the date field (or the whole document with `Ctrl + A`) and press `F9` to update the results.
Convert Excel Dates to Text Before Merging
Use an Excel formula to convert the dates into exactly formatted text strings, preventing Word from misinterpreting the values.
Easily Manage Mail Merge and Date Formats with WPS Office
WPS Office provides a highly compatible and intuitive Mail Merge feature in WPS Writer. You can seamlessly link WPS Spreadsheet data and format dates precisely using built-in tools without complex coding.
- 1. Open Mailings in WPS Writer: Open WPS Writer and navigate to the 'References' tab, then click the 'Mailings' button.
- 2. Connect Data Source: Click 'Open Data Source' and select your WPS Spreadsheet containing the source dates.
- 3. Insert Merge Field: Place your cursor in the document where the date should appear, then click 'Insert Merge Field' and select your date column.
- 4. Toggle Field Codes: Highlight the inserted field, right-click, select 'Toggle Field Codes', and append `\@ "dd/MM/yyyy"`.
- 5. Preview Results: Click 'View Merged Data' to preview your perfectly formatted dates before finalizing the merge.
Frequently Asked Questions
Why does Word change my UK dates to US dates during a mail merge?
Word uses an OLE DB connection to read data from Excel. This protocol often interprets date values based on the default US format (MM/DD/YYYY), regardless of the visual formatting applied within the Excel spreadsheet.
How do I show the month name instead of a number in a merged date?
You can display the full month name by adding the switch `\@ "dd MMMM yyyy"` to your merge field code. For example, 05/12/2023 will merge as 05 December 2023.
What if toggling field codes doesn't work?
Ensure you are pressing `Alt + F9` (or `Fn + Alt + F9` on some laptops). Make sure your text cursor is placed inside the field code brackets when editing, and always update the field by pressing `F9` or clicking the preview button to see your changes applied.
Can I format dates directly in Excel to fix the merge?
Changing the cell format (e.g., Right-click > Format Cells) in Excel only alters its visual appearance, not the underlying serial number read by Word. To fix it entirely in Excel, use the TEXT formula to convert the date into a text string.
