[Andy Roberts]

I am pulling a date from an excel spreadsheet into a mail merge doc. The
format in the spread sheet is correct (i.e. dd/mm/yy) however the doc is
formatting the date differently (i.e. m/d/yyyy).

How do I get word to maintain the format from Excel?

--
Regards

Andy

Andy Roberts
Win XP, Office 2007

[Peter Jamieson]

Typically what you have to do is use a "date format switch" in your Word
document to output the format you need.

e.g. if your excel column is called mydate, open your mail merge main
document, press Alt-F9, and look for something like

{ MERGEEFIELD mydate }
or
{ MERGEEFIELD mydate \*Mergeformat }

and modify the field code to be

{ MERGEEFIELD mydate \@"DD/MM/YY" }

then test your merge.

As background,
a. Word has a number of different methods of connecting to mail merge
data sources
b. by default, from Windows Word 2002 onwards, Word uses OLE DB
c. 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 (almost) all dates as if they are in the unhelpful "U.S. Format"
dd/mm/yy format, regardless of the format in Excel, your regional
options etc.
d. applying a "date format switch" should fix that in all fully
patched versions of Windows Word from Word 2002 onwards.

Peter Jamieson

http://tips.pjmsn.me.uk

On 21/03/2010 17:38, Andy Roberts wrote:
I am pulling a date from an excel spreadsheet into a mail merge doc. The
format in the spread sheet is correct (i.e. dd/mm/yy) however the doc is
formatting the date differently (i.e. m/d/yyyy).

How do I get word to maintain the format from Excel?

[Andy Roberts]

Peter

Thanks for your response which works as described. However to move this
slightly forward i'd like the date format to represent 2 characters / 2
characters / 2 characters. To explain i'd like the 3rd March 2010 to be
represented as 03/03/10 and your suggestion gives me 3/3/10.

Is this possible?

--
Regards

Andy

Andy Roberts
Win XP, Office 2007
"Peter Jamieson" wrote in message
...
Typically what you have to do is use a "date format switch" in your Word
document to output the format you need.

e.g. if your excel column is called mydate, open your mail merge main
document, press Alt-F9, and look for something like

{ MERGEEFIELD mydate }
or
{ MERGEEFIELD mydate \*Mergeformat }

and modify the field code to be

{ MERGEEFIELD mydate \@"DD/MM/YY" }

then test your merge.

As background,
a. Word has a number of different methods of connecting to mail merge
data sources
b. by default, from Windows Word 2002 onwards, Word uses OLE DB
c. 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 (almost) all dates as if they are in the unhelpful "U.S. Format"
dd/mm/yy format, regardless of the format in Excel, your regional options
etc.
d. applying a "date format switch" should fix that in all fully patched
versions of Windows Word from Word 2002 onwards.

Peter Jamieson

http://tips.pjmsn.me.uk

On 21/03/2010 17:38, Andy Roberts wrote:
I am pulling a date from an excel spreadsheet into a mail merge doc. The
format in the spread sheet is correct (i.e. dd/mm/yy) however the doc is
formatting the date differently (i.e. m/d/yyyy).

How do I get word to maintain the format from Excel?

[Andy Roberts]

Peter

Apologies - a bit quick off the mark. Your suggestion does exatly what i
need.

--
Regards

Andy

Andy Roberts
Win XP, Office 2007
"Andy Roberts" wrote in message
...
Peter

Thanks for your response which works as described. However to move this
slightly forward i'd like the date format to represent 2 characters / 2
characters / 2 characters. To explain i'd like the 3rd March 2010 to be
represented as 03/03/10 and your suggestion gives me 3/3/10.

Is this possible?

--
Regards

Andy

Andy Roberts
Win XP, Office 2007
"Peter Jamieson" wrote in message
...
Typically what you have to do is use a "date format switch" in your Word
document to output the format you need.

e.g. if your excel column is called mydate, open your mail merge main
document, press Alt-F9, and look for something like

{ MERGEEFIELD mydate }
or
{ MERGEEFIELD mydate \*Mergeformat }

and modify the field code to be

{ MERGEEFIELD mydate \@"DD/MM/YY" }

then test your merge.

As background,
a. Word has a number of different methods of connecting to mail merge
data sources
b. by default, from Windows Word 2002 onwards, Word uses OLE DB
c. 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 (almost) all dates as if they are in the unhelpful "U.S. Format"
dd/mm/yy format, regardless of the format in Excel, your regional options
etc.
d. applying a "date format switch" should fix that in all fully patched
versions of Windows Word from Word 2002 onwards.

Peter Jamieson

http://tips.pjmsn.me.uk

On 21/03/2010 17:38, Andy Roberts wrote:
I am pulling a date from an excel spreadsheet into a mail merge doc.
The
format in the spread sheet is correct (i.e. dd/mm/yy) however the doc is
formatting the date differently (i.e. m/d/yyyy).

How do I get word to maintain the format from Excel?