Forum Discussion
Mail merge and Excel
NikolinoDE nope, that does not work for me. I am not a programmer, so maybe I have syntax wrong but I followed your instructions I think and it retains the undesired date format. I can format the Excel cells and that looks the way I want it (dd/MM/yyyy).
The issue happens when I merge the data into the mailmerge Word doc. It simply refuses to use the date format from the Excel file.
Ok, so I did as instructed, I clicked on the offending field in the merged document , clicked Edit Field, (it told me it was a mergefield with the name Date), I then clicked on Field Codes in the bottom left of the dialogue box which changed the Date name in the upper right box to MERGEFIELD Date , I then clicked on Options in the lower left of the dialogue which gave me General Switches and Field Specific Switches. The General dialogue related to Text so I clicked on the Field Specific sSitches which gave me a set of 4 options \b, \f, \m, \v. That is, no \a option. Does this mean there is no \a option?
Undeterred, I went back to the Mergefield Date dialogue and manually entered \a "dd/MM/yyyy" after Date but that did not change the field, so I assume it does not recognise the \a switch?
Also, when I then went back to my merged document and selected the date field and then Toggle Field codes, my manually added switch is shown but the date remains as the offending MM/dd/yyyy.
I have run out of ideas and abilities. Do you have any suggestions?
You can try the following alternative approach:
- In your Excel data source, create a new column next to the date column.
- In the new column, use the formula =TEXT(A1, "dd/MM/yyyy") (assuming the date column is in column A and the first row of data is row 1). This formula will convert the dates to the desired format.
- Copy the formula down to apply it to all the rows containing dates.
- Save and close the Excel file.
Now, when performing the mail merge in Microsoft Word, follow these steps:
- Open your Word document and go to the "Mailings" tab.
- Click on the "Select Recipients" button and choose "Use an Existing List."
- Browse to select your modified Excel data source file (the one with the additional column) and click "Open."
- In the "Mail Merge Recipients" dialog box, make sure that the new column with the formatted dates is recognized correctly.
- Click "OK" to close the "Mail Merge Recipients" dialog box.
- Insert the formatted date field from the new column into your document using the "Insert Merge Field" button in the "Write & Insert Fields" section of the "Mailings" tab.
- Complete the mail merge as usual.
By creating a new column in Excel and using the TEXT function to convert the dates to the desired format, you provide Word with the formatted dates during the mail merge process. This should help retain the correct date format in the merged document.
Thanks. That process you have provided seems to work now.
It is a rigmarole and I do not see why one has to be a programmer to make what on the surface seems like a simple issue - and one that worked fine a little while ago. I assume that the problem has arisen due to a recent update. Maybe this is something that Microsoft can fix?
Thanks for your assistance.
John