How to Change Date Format in Excel Mail Merge (with Quick Steps)

Mail Merge is an outstanding feature of Microsoft Word. Using this feature of Microsoft Word and an Excel datasheet, we can create as many copies of documents as we want. This article will show you the step-by-step procedure to change the date format in Excel Mail Merge. If you are curious to know about the process, download our practice workbook and follow us.

How to Change Date Format in Excel Mail Merge: with Quick Steps

This content will demonstrate the step-by-step procedure to change the date format in Excel Mail Merge. We are going to send a promotion letter to our employees. The process is explained elaborately below step-by-step.

Step 1: Create Dataset

At the beginning of this task, we have to create an Excel datasheet according to our desired requirements. As we want to send a promotion letter to our employees, our dataset contains only their names and some necessary dates regarding the process.

First of all, input the name of the employees and their joining date in your organization in columns B and C.

We are going to promote our employees after four months of their joining. So, in cell E3, write down the following formula to get the joining dates of their new position.

=C3+120

Now, press Enter.

The promotion letter requires to issue at least one week before the joining date. To get the value of these dates, write down the following formula into cell D3.

=E3-7

Again, press Enter.

Finally, select the range of cells D3:E3.
Then, double-click on the Fill Handle icon to copy the formula up to row 12.
Save the file at your desired location.
Our required Excel spreadsheet will be ready.

Thus, we can say that we have completed the first step to change the date format in Excel Mail Merge.

Step 2: Design Template in Microsoft Word

In this step, we will design the template of our promotion letter that we will send to our employees.

First, launch Microsoft Word on your device.
Then, write down the letter as shown in the image below.
Now, Bold and Uppercase format to those strings that will be replaced by the data of the Excel spreadsheet. It will help you to trace them easily.

At last, we can say that we have finished the second step to change the date format in Excel Mail Merge.

Step 3: Create Relation Between Both Files

Now, we are going to create the relation between the Word file and the Excel file. The connection will be set from the Word file.

At first, click on the Mailing tab in Microsoft Word.
Now, select the drop-down arrow of the Select Recipients > Use an Existing List option from the Start Mail Marge group.

As a result, a small window called Select Data Source will appear.
Then, go to that location where you saved the Excel sheet. In our case, we saved that file on the Desktop.
After that, select the file and click on Open.

Another small dialog box entitled Select Table will appear in front of you.
Check the small box First row of data contains column headers and click OK in that window.

You will notice that some new commands will be available in the Mailing tab.
Now, select Name sting, and in the Mailing tab, click on the drop-down arrow of the Insert Merge Field from the Write & Insert Fields group.
Choose the Name field from the drop-down list.

You will see that the previous text will disappear, and the field is inserted in a different format.

Similarly, insert the rest of those three date fields into the letter.

Finally, in the Mailing tab, click on the Preview Results option from the Preview Results group to check all the fields inserted accurately.

You will be able to see the letter for every employee.

In the end, we can say that we have accomplished the third step to change the date format in Excel Mail Merge.

Step 4: Modify Date Format

If you look at the letter, you will notice all the dates are showing in the same format. Here, we will show you how to change their format. We are to add the weekday’s name with the fields joining date and joining date at the new position. Besides it, we also want to show the month’s name instead of the month number.

First, right-click on the field name and select the Edit Field option.

As a result, a dialog box called Field will appear.
Now, change the Categories option All to Date and Time.
You will see all types of available date formats in the Field Properties option.
Then, click on any format, and you will see the date format in the empty box below the Date formats text.
After that, select the Field Codes option.

You will see the field code.
Afterward, select those parts of that code as shown in the image below and press ‘Ctrl+C’ to copy.
At last, click Cancel to close the box.

Similarly, right-click on the field «Joining_Date» and from the context menu, select the Toggle Field Codes option.

The code of that field will appear.
Now, press ‘Ctrl+V’ to paste the following codes after the field name inside the parenthesis.

\@"dddd, MMMM d, yyyy"

Again, right-click on your mouse and select the Toggle Field Codes option to get the field name back.
Similarly, use the same format code and follow the same process to modify the date format of the Joining Date at New Position field.
After that, to change the date format of the Promotion Letter Issue Date, write down the following codes inside the parenthesis of the corresponding field.

\@ “MMMM d, yyyy”

Go back to the field name using the Toggle Field Codes option.
Finally, click on the Preview Results option from the Preview Results group.
You will see that our date format is changed from our previous steps.

Finally, we can say that our code worked successfully, and we can change the date format in Excel Mail Merge.

💬 Things You Should Know

Do not click on the OK button to insert any field format code from the Field dialog box. The OK button will permanently insert the value at that position. As a result, the date will remain the same for the rest of the dataset.

Download Practice Workbook

Download these practice workbooks for practice while you are reading this article.

Change Date Format in Mail Merge.xlsx

Promotion Letter Template.docx

Conclusion

That’s the end of this article. I hope that this article will be helpful for you and you will be able to change the date format in Excel Mail Merge. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations. Keep learning new methods and keep growing!

<< Go Back To Mail Merge Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!