Controlling Date Formats in a Mail Merge

David is performing a mail merge using data stored in an Excel workbook. Part of the information being merged is a date. The date is formatted correctly in Excel, but when it is merged into the Word document it loses the formatting. Specifically, it appears as "09-23-2018" but should look like "Sunday, 23 September 2018." David is wondering how to get the formatting on the date that he needs.

There are two things you can try. First, you could check to see what method Word is using to grab the information from the Excel workbook. The default data transfer method is OLE (object linking and embedding), which means that the data is transferred as raw data and Word does it's best to format what it fetches. You can change the data transfer method when you select the Excel workbook in the merge process. Word should display a dialog box that asks how you want to have the data transferred; you should select either a DDE transfer or the Excel by Conversion option.

If you don't see the dialog box, then it means you need to configure Word so that it will present the dialog box. Just exit from the merge and follow these steps:

1. Display the Word Options dialog box. (In Word 2007 click the Office button and then click Word Options. In Word 2010 or a later version display the File tab of the ribbon and then click Options.)
2. Click Advanced at the left side of the dialog box.
3. Scroll through the available options until you come to the General section. (See Figure 1.)



Figure 1. The General section of the Advanced options of the Word Options dialog box.

4. Make sure the Confirm File Format Conversion On Open check box is selected.
5. Click on OK.

If all this talk about conversion methods, OLE, DDE, and the rest has your head spinning, take a moment and breathe deeply. You are in luck; there is another way you can get the date format you want without worrying about any method of data transfer. You can add a formatting switch to the merge field used in your document. If you display the field codes (instead of their results), the field usually looks similar to this:

{ mergefield MyDate }

This presumes that the name of the data field, from Excel, is MyDate. You can modify the merge field so that it includes a formatting switch, in this manner:

{ mergefield MyDate \@ "dddd, dd MMMM yyyy" }

The use of the \@ characters indicate that what follows is a pattern for how the date should be formatted. In this case, the date will show similar to "Wednesday, 26 September 2018." Full information on how to put together the date patterns can be found at the Word MVP site, here:

https://wordmvp.com/FAQs/TblsFldsFms/DateFields.htm

WordTips is your source for cost-effective Microsoft Word training. (Microsoft Word is the most popular word processing software in the world.) This tip (6108) applies to Microsoft Word 2007, 2010, 2013, 2016, 2019, and 2021. You can find a version of this tip for the older menu interface of Word here: Controlling Date Formats in a Mail Merge.