Fix merge issue between Excel and Word

Word's merge function ignores any formatting that Excel applies to the data in its cells. In the case of dates, Excel stores all dates as serial numbers, starting with 1 January 1900. That's what Word receives during the merge. You must supply the desired format by including a @ switch in the merge field. See https://wordribbon.tips.net/T006108_Controlling_Date_Formats_in_a_Mail_Merge for examples.

To resolve the issue of the date field from your Excel spreadsheet not transferring correctly to your Word mail merge, you can try the following methods:

1. Format the Date in Excel: Ensure that the cell containing the expiry date is formatted as a date in Excel. You can do this by selecting the cell, right-clicking, choosing "Format Cells," and then selecting "Date" under the Number tab. This may help retain the date format during the mail merge process.
2. Use Dynamic Data Exchange (DDE): You can connect to the Excel worksheet using DDE, which may help in retaining the formatting of the date. Here’s how to do it:
   • Start Word and open a new blank document.
   • Go to File > Options > Advanced, and check the box for "Confirm file format conversion on open."
   • In the Mailings tab, select Start Mail Merge and choose Step By Step Mail Merge Wizard.
   • Follow the prompts to select your Excel data source, and when prompted in the Confirm Data Source dialog, choose "MS Excel Worksheets via DDE (*.xls)".
   • This should allow the date to be pulled in with its formatting intact.
3. Ensure the Formula Outputs a Date: If the date in the cell is derived from a formula, ensure that the formula outputs a date value recognized by Excel. If it outputs a number, it may not be interpreted correctly in the mail merge.

By ensuring the correct formatting and using DDE, you should be able to successfully merge the date field into your Word document while retaining the formula in your Excel sheet.

References:

• Excel data doesn't retain its formatting in mail merge operations in Word

Dear @Paul Reilly, 

As I have mentioned in the message I sent you via Private Message: Because your actual workbook contains formulas, multiple fields, and live data relationships, I wasn’t able to reproduce the exact behavior end‑to‑end without impacting other merge fields with a simple Excel test file only containing dates. For that reason, rather than changing the Excel file, the safest approach is to format the date directly in the Word mail merge field.

This is the simple Excel test file only containing dates used for testing with the file that you sent me but it also impacted other fields in your file.

Following this article that I have sent: Date, Phone Number, and Currency fields are merged incorrectly when you use an Access or Excel data source in Word, I tested this on a blank Word file instead and I toggled the field codes like this: {MERGEFIELD Dates @ "MMMM d, yyyy"}

When I clicked on preview results:

I tested with this field too: {MERGEFIELD Dates @ "dd-MM-yyyy"}

Please right-click to update field:

Here is the result:

Could you please try updating the merge field in your document to the following and let me know how it looks?

{ MERGEFIELD HS_Expiry_Date @ "dd-MM-yyyy" }

This keeps your Excel formula intact and only controls how the date is displayed in Word.

To apply this:

• Select the date merge field in Word
• Press Alt + F9 to show field codes or right-click and choose Toggle Field Codes
• Add or update the @ date format
• Press Alt + F9 again, then right‑click the field and choose Update Field

Thank you for your patience.

Hi Julie

I have not been able to resolve this issue and any proposed solutions have not worked either. It was my understanding you were working on this and needed some time but confused now. Its not good Microsoft cannot resolve this problem and it has been several weeks now. I do not see why a Teams meeting with all would help resolve this??Paul

Dear @Paul Reilly, 

Good day! Welcome to Microsoft Q&A forum!

Based on your description, I understand that you're experiencing an issue where one specific date field from your Excel spreadsheet isn’t transferring correctly into your Word mail merge but appearing as a serial number instead of a formatted date while still needing to preserve the formula that retrieves that date from another sheet.

I sincerely apologize for the inconvenience caused by this issue.

Regarding this matter, another user found that by going to Word > File > Settings > Advanced > scroll down to General > Check in the box "Confirm file format conversion on open" would usually help preserve the original formatting from Excel.

Please kindly try that option and let me know if it works for you.

If not, please send me copies of your files via Private Message so that I could try to work on it on my end.

Please understand that our initial response does not always resolve the issue immediately. However, with your help and more detailed information, we can work together to find a solution.

Any updates you’re able to share would be really helpful. I appreciate your time and look forward to hearing how things are going! 

Thank you for your time and patience.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in [our documentation] to enable e-mail notifications if you want to receive the related email notification for this thread.