Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Gil5563 Gil5563 is offline
external usenet poster
 
Posts: 1
Default 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?
  #2   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default UK Datefield in Excel reverts to US format when mail mergingWord

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?

  #3   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default UK Datefield in Excel reverts to US format when mail mergingWord

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?

  #4   Report Post  
Posted to microsoft.public.word.mailmerge.fields
macropod[_2_] macropod[_2_] is offline
external usenet poster
 
Posts: 2,059
Default UK Datefield in Excel reverts to US format when mail merging Word

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?


Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
My zip code format is lost when merging from Excel to Word... Roy Laws Microsoft Word Help 1 May 26th 21 09:31 PM
My zip code format is lost when merging from Excel to Word... Merging Zip Code Errors Microsoft Word Help 12 October 20th 09 09:46 PM
Merging from excel but the currency format changes on word! Lee Mailmerge 4 February 15th 08 01:50 PM
Mail merging a date from excel, appears as US format not UK christina_b123 Mailmerge 1 November 15th 07 09:57 AM
Format Excel data for merging in Word BW Mailmerge 1 January 21st 05 01:25 AM


All times are GMT +1. The time now is 04:36 AM.

Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"