How to Format Excel Dates Correctly in a Word Mail Merge
Question details
The user needs to ensure dates from an Excel data source are displayed correctly in a Word mail merge instead of appearing as sequential serial numbers.
- Product
- Microsoft Word and Excel
- Device & OS
- not provided
- Scenario
- Performing a mail merge in Word using an Excel spreadsheet as the source data for date fields.
- Observed behavior
- Word displays the underlying numerical values (serial numbers) for dates, ignoring the date formatting previously applied to the cells within Excel.
Make sure your Excel spreadsheet is fully saved and closed before initiating the mail merge in Word to prevent file-locking issues.
Use a Date Switch Code in Word Merge Fields
The most reliable method to format dates in a mail merge is to manually add a formatting switch directly into the Word merge field code.
Excel stores dates as sequential numbers (representing the days since December 31, 1899) to make calculations easier. By default, Word imports these raw numbers instead of the visual formatting. You can force Word to display the correct date structure by applying a simple Date Switch.
Open your Word document containing the mail merge fields and press 'Alt + F9' on your keyboard. This reveals the background field codes.
Find the merge field that contains your date data. It should look something like { MERGEFIELD YourDateField }.
Click inside the bracket, right after the field name, and add your desired date switch. For example, to get a standard format, type: \@ "MM/dd/yyyy". The final code should look like { MERGEFIELD YourDateField \@ "MM/dd/yyyy" }.
Press 'F9' to update the field code, then press 'Alt + F9' again to hide the codes and return to the normal view. Your dates will now appear correctly formatted.
Enable Dynamic Data Exchange (DDE)
Use DDE to instruct Word to preserve and import the exact formatting displayed in your Excel spreadsheet.
Easily Manage Mail Merges with WPS Office
WPS Office provides a highly compatible and user-friendly Mail Merge feature. It seamlessly integrates WPS Spreadsheet data into WPS Writer, offering intuitive field formatting tools to prevent date and number errors effortlessly.
- 1. Open Mail Merge in WPS Writer: Launch WPS Writer, navigate to the 'References' tab on the top ribbon, and click 'Mail Merge'.
- 2. Import Your Data Source: Click 'Open Data Source' and browse your computer to select the WPS Spreadsheet containing your date records.
- 3. Insert the Merge Fields: Place your cursor where you need the date to appear, click 'Insert Merge Field', and choose your date column.
- 4. Preview and Complete: Click 'View Merged Data' to check your date formatting. If necessary, you can toggle field codes just like in standard Word processors.
Frequently Asked Questions
Why does my Word mail merge show Excel dates as 5-digit numbers?
Excel fundamentally stores dates as sequential serial numbers starting from December 31, 1899. When Word imports this data during a mail merge, it pulls the raw underlying serial number (e.g., 44197) rather than the visual date formatting you applied in Excel.
Do I need to re-apply the date switch every time I update the Excel file?
No. Once you add the date switch (e.g., \@ "MM/dd/yyyy") to the merge field in your Word document and save it, the formatting rule will automatically apply to any new or updated data pulled from the connected Excel file.
Can I format currency and percentages using field switches too?
Yes. Similar to date switches, you can use numeric picture switches for currency and percentages. Press Alt+F9 and append a switch like \# "$#,##0.00" for standard currency or \# "0.00%" for percentages to your merge fields.
What should I do if the Alt+F9 shortcut isn't toggling my field codes?
On some laptops or compact keyboards, the function keys share media controls. You may need to press 'Fn + Alt + F9' simultaneously to successfully toggle the field codes in Word.
