Forum Discussion
Mail Merge Dates Issue
I have an Excel Sheet that includes dates. I have created a Word Mail Merge Document that includes the merging of dates and other information from the Excel Sheet. In the merge, all data other than the dates are merging correctly. The dates are merging in as numbers, regardless of switches. I do not understand why I am having so many problems with this date issue. The date clearly shows as a date in Excel yet its merging in as numbers. No matter what I try, I cannot get the Date to merge in as the Date shown in Excel.
How can I make this work.
Carl
Did you make the merge field look similar to this:
{ MERGEFIELD DateField \@ "MM/dd/yyyy" }
Excel dates are just numbers (the number of days since Dec 31, 1899) So when you write "Date shown in Excel", the phrase is dependent upon that format you are displaying that number.
So, in Mail Merge, you can also set the number of the date in a specific format. From a fellow MCT page, here are some examples.Field Code Displayed Results
{ MERGEFIELD Date \@ “dddd, MMMM d, yyyy” } Saturday, July 9, 2022
{ MERGEFIELD Date \@ “MM/dd/yyyy” } 07/09/2022
{ MERGEFIELD Date \@ “M-d-yy” } 7-9-22
{ MERGEFIELD Date \@ “MMMM d, yyyy” } July 9, 2022
https://thesoftwarepro.com/date-text-mail-merge-formatting-in-word-document/
And I just saw Han's reply. Go with his
Try referring this:
- Check Excel Date Formatting:
- Ensure that the date column in your Excel sheet is formatted as a date. Even if it appears as a date, Excel might still treat it as a number.
- Use Dynamic Data Exchange (DDE):
- Using DDE can help maintain the formatting from Excel to Word. To enable DDE:
- In Word, go to File > Options > Advanced.
- Scroll down to the General section and check the box for Confirm file format conversion on open.
- Start the Mail Merge process again. When selecting your Excel file, a dialog box will appear. Choose MS Excel Worksheets via DDE.
- Using DDE can help maintain the formatting from Excel to Word. To enable DDE:
- Apply a Date Format Switch in Word:
- You can add a switch to the merge field to format the date correctly:
- In your Word document, press Alt + F9 to display the field codes.
- Locate the date merge field, which will look something like { MERGEFIELD YourDateField }.
- Modify it to include a date format switch, such as { MERGEFIELD YourDateField \@ "dd MMMM yyyy" }.
- Press F9 to update the field and Alt + F9 again to toggle back to the normal view.
- You can add a switch to the merge field to format the date correctly:
- Refresh Data Source Connection:
- Sometimes, refreshing the connection between Word and Excel can help:
- Go to the Mailings tab in Word.
- Click Select Recipients and choose Use an Existing List.
- Re-select your Excel file.
- Sometimes, refreshing the connection between Word and Excel can help:
- Check Regional Settings:
- Ensure that the regional settings in both Excel and Word are consistent, as differences can cause formatting issues.
- Check Excel Date Formatting:
