Formatting Issues

How to Fix Word Mail Merge Date Formatting for Specific Excel Records

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to fix an issue where Word mail merge date-formatting switches only work for some Excel records, resulting in inconsistent date formats in the final merged document.

Product: Word
Device & OS: not provided
Scenario: Performing a mail merge using an Excel spreadsheet as the data source.
Observed behavior: Date-formatting switches apply correctly to certain records but fail for others, even though the source Excel column appears to use a uniform short-date format.

Before you start

Identify which specific rows in your Excel spreadsheet are failing to format correctly in Word. Press Alt + F9 in your Word document to reveal the current merge field codes so you can verify the existing date switches.

Solution 1Recommended

Standardize Excel Data Types and Close the Workbook

Convert all mixed text and date values in your Excel column into true Excel dates, then close the workbook before running the mail merge in Word.

Inconsistent mail merge results often occur because Word determines a column's data type based on the first few rows. If your Excel column contains a mix of actual serial dates and text strings that look like dates, Word's formatting switch (like \@ "MM/dd/yyyy") will only work on the true date values.

Select the date column in Excel

Open your Excel data source and highlight the entire column containing the dates that are formatting inconsistently.

Convert text to true dates

Navigate to the 'Data' tab on the ribbon and click 'Text to Columns'. Choose 'Delimited', click 'Next' twice to reach Step 3, select 'Date' under Column data format, and click 'Finish'. This forces all text dates into true Excel serial dates.

Apply uniform date formatting

With the column still selected, go to the 'Home' tab, click the 'Number Format' dropdown, and choose 'Short Date'.

Save and completely close Excel

Save the changes to your spreadsheet. You must completely close the Excel application, as leaving the workbook open can lock the OLE DB connection and cause Word to read the data incorrectly.

Update the mail merge in Word

Open your Word document. Go to the 'Mailings' tab, click 'Edit Recipient List' to refresh the data connection, and click 'Preview Results' to verify the dates are now formatted uniformly.

Why Closing the Workbook Matters: When the Excel source file is open, Word may struggle to read the latest saved state of the workbook via its OLE DB connection, leading to bypassed formatting switches.

Seamless Document Processing

Perform Flawless Mail Merges Using WPS Office

WPS Office provides a highly integrated Mail Merge feature. By using WPS Writer and WPS Spreadsheet together, you can seamlessly merge data, accurately format dates, and avoid OLE DB connection errors commonly found in other software.

1. Open your document in WPS Writer: Launch WPS Writer, open your letter or template, and navigate to the 'References' tab on the top ribbon.
2. Import your spreadsheet data: Click on 'Mail Merge', then select 'Open Data Source' to browse and import your WPS Spreadsheet (.xlsx) file.
3. Insert merge fields and preview: Click 'Insert Merge Field' to place your date field into the document, then use the 'View Merged Data' button to preview the accurate date formats instantly.

Highly compatible with Microsoft Word (.docx) and Excel (.xlsx) formats.Built-in Mail Merge feature with intuitive data source management.Accurate rendering of dates, currencies, and numbers from spreadsheets.Free, lightweight, and fast alternative to Microsoft Office.

microsoft office alternative - wps office

Frequently Asked Questions

How do I add a date formatting switch to a merge field in Word?

Press Alt + F9 to reveal the merge field codes. Locate your date field and add the switch to the end of the brackets, for example: { MERGEFIELD Date \@ "MM/dd/yyyy" }. Press Alt + F9 again to hide the codes, then update the field by pressing F9.

Why do Excel dates appear as 5-digit numbers in my mail merge?

Excel stores dates as sequential serial numbers (e.g., 44200). If Word connects to Excel without a formatting switch or if the data type is misread, it defaults to showing the raw underlying serial number. Applying a date switch in Word forces it to format the number as a calendar date.

Does leaving the Excel file open always break the mail merge?

Not always, but having the source workbook open can lock the OLE DB connection or cause temporary synchronization issues, especially with mixed data types. Closing the workbook ensures Word reads the finalized, stable data source.

Document & File Issues