Mail merge date format problem

Question

I have an Excel 2003 spreadsheet as my data source with the date cell formatted as DD/MM/YYYY. In my main document I setup the merge field as {MERGEFIELD "Unit_Start_Date" @ "D MMMM YYYY"}.

The output of my mail merge:

• 15/01/2013 Tuesday, 15 January 2013 (This is the correct format)
• 05/03/2013 Friday, 3 May 2013 (This is incorrect).

How do I fix this?.

Answer 1

This is an old bug that affected Word 2002/3 and relates to the OLEDB driver provided with those versions and which affects data imported from Excel. You must either switch to DDE (see below) or format the dates in Excel as text

From the Tools menu in Word, select Options and then go to the General tab and check the box against the "Confirm conversions at open" item.  Then when you attach the data source to the mail merge main document, you will be given the option of using the DDE method of connection which should read the data as you have it formatted in the table. 

You can also force the use of the old DDE connection method by activating the MailMerge Helper. This is covered at the end ofMail merge with Word 2002/2003

Answer 2

Cross-posted at: http://www.msofficeforums.com/mail-merge/15232-issue-date-format-mail-merge-document.html

For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184

FWIW, you can also use the ODBC connection method.

Answer 3

Thanks Graham. I switched to DDE and my mail merge is working perfectly now.