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 "12-21-2008" but should look like "Sunday, 21 December 2008." 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 if you are using a version of Word prior to Word 2007:

1. Choose Options from the Tools menu. Word displays the Options dialog box.
2. Choose the General tab. (Click here to see a related figure.)
3. Make sure the Confirm Conversion At Open check box is selected.
4. Click on OK.

If you are using Word 2007, follow these steps instead:

1. Click the Office button and then click Word Options. Word displays the Word Options dialog box.
2. Click Advanced at the left side of the dialog box.
3. Scroll through the available options until you come to the General section. (Click here to see a related figure.)
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, 31 December 2008." Full information on how to put together the date patterns can be found at the Word MVP site, here:

http://word.mvps.org/FAQs/TblsFldsFms/DateFields.htm