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"}.

When I complete the merge, half of the final document has worked perfectly ie 05/06/2013 displays as "5 June 2013" while the other half of the document is incorrect ie 05/06/2013 displays as "6 May 2013".

I have tried using a calendar control to enter the dates but am still getting the same issue

How do I fix this?

That suggests you may have mixed data formats in the source (ie some data is stored as dates, whilst others are stored as text) or, if there's more than one instance of the date in the document, that the field switch has been entered incorrectly for the one producing the wrong output.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

The issue is not mixed data format in the source as I have checked this (I even recreated the column from scratch). Also there isn't any problem with the coding for the switch - my mail merge is to create letters that contain the date ie the date format is correct on some letters but not others.

I don't think I have explained my problem very well. Below is an example of the output of my mail merge:

10/01/2013 Tuesday, 1 October 2013 (This is incorrect)
15/01/2013 Tuesday, 15 January 2013 (This is correct)
05/03/2013 Friday, 3 May 2013 (This is incorrect)
27/03/2013 Wednesday, 27 March 2013 (This is correct)

It seems that the Word can only create the date in the format that I want when the day is greater than "12".

Surely there has to be a work around.

The issue is not with the date switch. I've used them extensively will all manner of dates, with no such '12' problem. See, for example, my Microsoft Word Catalogue/Directory Mailmerge Tutorial, in the 'Sticky' thread at the top of this forum:
Microsoft Word Catalogue/Directory Mailmerge Tutorial

What short date format does your system's regional date setting use, and what date format does your Excel workbook use?

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Cross-posted at: http://answers.microsoft.com/en-us/o...3-9174a552f9f0
For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184
FWIW, you can also use the ODBC connection method.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]