hi Dan,



There are two parts to this issue:
• the date format in Excel; and
• the date format in Word.

Word has a number of different methods of connecting to mail merge data sources, including DDE and OLE DB. Word 2002 and later use the OLE DB coonection by default, though you can change this (to DDE, for example). To work around a limitation in the OLE DB provider used to get data from Excel etc., when Word is connected to an OLE DB data source, it treats dates as if they are in the US mm/dd/yy format, regardless of the format in Excel, your regional settings etc. Applying a date format switch fixes that - and gives the mailmerge document the ability to format the date independently of whatever format is used in the data source.

To get the date format you want, you can add a formatting picture switch as follows:
• select the mergefield;
• press Shift-F9 to expose the field coding. It should look something like {MERGEFIELD MyDate} where 'MyDate' is your mergefield's name;
• delete anything appearing after the mergefield's name and add '\@ "d MMMM yyyy"' to the field, as in {MERGEFIELD MyDate \@ "d MMMM yyyy"}. With this switch your date will come out as '2 August 2008'. Other possible date formatting switches include:
. \@ "dddd, d MMMM yyyy";
. \@ "ddd, d MMMM yyyy";
. \@ "d MMM yyyy";
. \@ "dd/MMM/yyyy";
. \@ "d-MM-yy";
Note: Note: you can swap the d, M, y expressions around, but you must use uppercase 'M's for months - lowercase 'm's are for minutes.
• position the cursor anywhere in this field and press F9 to update it.

I assume that in Excel you're using the Calendar Control. If you use that, whatever date you pick should be inserted into your destination cell in that cell's format. If the day & month order for that format differs from your regional settings, that may cause problems for your mailmerge.

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

Thanks for your reply Paul but I am using {MERGEFIELD Appt_date \@ "dddd d MMMM YYYY"} in the merge field. I understand that the format needs to be defined on the mail merge as well but this doesn't solve the problem. It shows it in the correct format but recognises the month and day the other way aroun ONLY if the day is before the 12th of the month!

So the Microsoft date picker says 03/07/2012 in UK format (3rd July). The linked cell on excel then dispays 03/07/2012 in UK format (I have also tried longer formats on both (3 Jul 2012 etc). The the merge field is set to {MERGEFIELD Appt_date \@ "dddd d MMMM YYYY"} but comes out as Tuesday 7 March 2012. The same if I use \@"dd/MM/YY" (7/3/2012). But if i select 13 July everything is correct in Excel and Mailmerge.

Weird. You help is much appriciated.

Thanks

Dan