UK Datefield in Excel reverts to US format when mail merging Word

Have had excel and word 2003 installed on my PC at my place of work. Do a lot
of mailmerges.
Everything OK except when I mail merge using a date field in the SS (not
using the "today's date" update on word, - which works fine) which reverses
my UK date to US date i.e 24/02/2010 to 02/24/2010. The date appears
normally in the excel spreadsheet - until it is merged. I have tried re
formatting the cells and I have checked all the settings and they appear to
be set for English Uk - so I don't know what else to do!!

Any ideas please?

Use a date field format switch for each date field, e.g.
use Alt-F9 to show the field codes and change

{ MERGEFIELD mydate }
to
{ MERGEFIELD mydate \#"DD/MM/YYYY" }

(Delete any \*Mergeformat you see).

This results from the fact that the OLE DB provider used by default to
deliver data to Word from Excel always uses US format regardless of your
regional settings (or something like that). So a klude was introduced in
a hotfix to Word 2002 so that when Word connects to a data source via
OLE DB, Word changes its interpretation of dates (certainly those in
AA/BB/YY format) from the one in the regional settings to "US date". So
although the dates coming in are the wrong format when left unformatted,
a UK date format switch should always work correctly.

Peter Jamieson

http://tips.pjmsn.me.uk

On 03/02/2010 16:01, Gil5563 wrote:
Have had excel and word 2003 installed on my PC at my place of work. Do a lot
of mailmerges.
Everything OK except when I mail merge using a date field in the SS (not
using the "today's date" update on word, - which works fine) which reverses
my UK date to US date i.e 24/02/2010 to 02/24/2010. The date appears
normally in the excel spreadsheet - until it is merged. I have tried re
formatting the cells and I have checked all the settings and they appear to
be set for English Uk - so I don't know what else to do!!

Any ideas please?

Hi Gil5563,

To get the date format you want, you can add a formatting picture switch. In Word:
.. 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.
.. position the cursor anywhere in this field and press F9 to update it;
.. run your mailmerge.

Note: Peter's '\#' switch won't work.


--
Cheers
macropod
[Microsoft MVP - Word]


"Gil5563" wrote in message ...
Have had excel and word 2003 installed on my PC at my place of work. Do a lot
of mailmerges.
Everything OK except when I mail merge using a date field in the SS (not
using the "today's date" update on word, - which works fine) which reverses
my UK date to US date i.e 24/02/2010 to 02/24/2010. The date appears
normally in the excel spreadsheet - until it is merged. I have tried re
formatting the cells and I have checked all the settings and they appear to
be set for English Uk - so I don't know what else to do!!

Any ideas please?

As macropod points out,

{ MERGEFIELD mydate \#"DD/MM/YYYY" }

should be

{ MERGEFIELD mydate \@"DD/MM/YYYY" }

Sorry!

Peter Jamieson

http://tips.pjmsn.me.uk

On 03/02/2010 17:02, Peter Jamieson wrote:
Use a date field format switch for each date field, e.g.
use Alt-F9 to show the field codes and change

{ MERGEFIELD mydate }
to
{ MERGEFIELD mydate \#"DD/MM/YYYY" }

(Delete any \*Mergeformat you see).

This results from the fact that the OLE DB provider used by default to
deliver data to Word from Excel always uses US format regardless of your
regional settings (or something like that). So a klude was introduced in
a hotfix to Word 2002 so that when Word connects to a data source via
OLE DB, Word changes its interpretation of dates (certainly those in
AA/BB/YY format) from the one in the regional settings to "US date". So
although the dates coming in are the wrong format when left unformatted,
a UK date format switch should always work correctly.

Peter Jamieson

http://tips.pjmsn.me.uk

On 03/02/2010 16:01, Gil5563 wrote:
Have had excel and word 2003 installed on my PC at my place of work.
Do a lot
of mailmerges.
Everything OK except when I mail merge using a date field in the SS (not
using the "today's date" update on word, - which works fine) which
reverses
my UK date to US date i.e 24/02/2010 to 02/24/2010. The date appears
normally in the excel spreadsheet - until it is merged. I have tried re
formatting the cells and I have checked all the settings and they
appear to
be set for English Uk - so I don't know what else to do!!

Any ideas please?