Forum Discussion
Mail merge and Excel
I have an Excel sheet data source. When I use it as the source data for mail merge the date format is changed from eg 27/04/2023 to 4/27/2023. I cannot find how to keep the original format - is it in Excel or the mailmerge Word file? How can I stop the unwanted format change?
The date format issue you are experiencing during mail merge is typically due to the regional settings in Microsoft Excel and Microsoft Word. Here are a few steps you can follow to preserve the original date format:
- Open Microsoft Excel and select the column containing the date values.
- Right-click on the column header and choose "Format Cells" from the context menu.
- In the "Format Cells" dialog box, go to the "Number" tab.
- Select the desired date format that matches your original format (e.g., "DD/MM/YYYY").
- Click "OK" to apply the format to the selected column.
Next, you will need to adjust the date format settings in Microsoft Word:
- Open your Word document and navigate to the section where you are performing the mail merge.
- Select the date field that is displaying the incorrect format.
- Right-click on the field and choose "Toggle Field Codes" from the context menu. This will reveal the underlying field code.
- Modify the field code to include the desired date format. For example, if the field code looks like { MERGEFIELD Date }, change it to { MERGEFIELD Date \@ "DD/MM/YYYY" }.
- Press "Alt+F9" to toggle back to the merged data view. The date should now be displayed in the original format.
By specifying the date format in the field code, you can ensure that the desired format is preserved during the mail merge process.
Note: It is important to keep in mind that the formatting of merged data can also be affected by the destination application (e.g., email client, printer, etc.). Make sure to double-check the output format in the final medium to confirm that it retains the desired date format. It is worth noting that the exact user interface and options may slightly vary between different versions of Excel and Word.
If it is not what you want, please add detailed information. How step by step (cell by cell) explanation if necessary a photo or file (without sensitive data). Information such as Excel version, operating system, storage medium, etc. would also help.
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.
- NikolinoDE thank you! This was such a big help!
- I'm glad you came to a solution.
I also wish you much success with Excel.
