A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Word » Mailmerge
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Word Mail Merge Date Format



 
 
Thread Tools Display Modes
  #1  
Old March 21st, 2010, 06:38 PM posted to microsoft.public.word.mailmerge.fields
Andy Roberts
external usenet poster
 
Posts: 183
Default Word Mail Merge Date Format

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


  #2  
Old March 21st, 2010, 09:34 PM posted to microsoft.public.word.mailmerge.fields
Peter Jamieson
external usenet poster
 
Posts: 4,550
Default Word Mail Merge Date Format

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?

  #3  
Old March 21st, 2010, 11:03 PM posted to microsoft.public.word.mailmerge.fields
Andy Roberts
external usenet poster
 
Posts: 183
Default Word Mail Merge Date Format

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?



  #4  
Old March 21st, 2010, 11:07 PM posted to microsoft.public.word.mailmerge.fields
Andy Roberts
external usenet poster
 
Posts: 183
Default Word Mail Merge Date Format

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?





 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:56 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.