Date format problem in Mail Merge - Word 2010

Anonymous

How do you change the date format in Mail Merge - Word 2010?

In the data file the date is fomated as DD-MMM-YY, yet in the merge document it appears as MMM/D/YY?

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Paul Edstein 82,871 Volunteer Moderator

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 '@ "dd-MMM-yy"' 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".

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;

• run your mailmerge.

0 comments

78 additional answers

Suzanne S Barnhill 279.2K Reputation points MVP Volunteer Moderator

2015-01-25T03:55:49+00:00

The switch you need is:

@ "d MMMM yyyy"

For more, see http://wordfaqs.mvps.org/datefields.htm.
Was this answer helpful?

0 comments No comments
Anonymous

2015-01-25T03:32:31+00:00

As with the original post to this forum, I am using an Excel sheet as the data source for a mail merge document in Word. When I tried adding the trusty switch "/@d MMMM yyyy" (without the quotes) to my merge code it only shows the day. If I remove the spaces between the characters I get the full date, but without the spaces. If I put dashes between the characters I get the full date but with dashes in it. I've tried using non-breaking spaces but again all that shows in the day: everything after that is lost.

ETA: I've fixed it. I needed to use delimiters around the switch - in other words with the quotation marks. I am so rusty at this, but looks like the rust is flaking off a bit.
Was this answer helpful?

0 comments No comments
Paul Edstein 82,871 Reputation points Volunteer Moderator

2014-10-27T10:45:49+00:00

Changing the cell format in Excel is not a reliable way of handling mixed data types. An empty cell in a column recognised as having a text or date format will be output as a blank, regardless of any formatting switches that may or may not be used in the mergefield. It's especially when you start mixing numbers and dates in a column that you start having trouble with the output.

If you have a column with mixed data types in which the only 'numeric' types are dates, but they're appearing in the mailmerge output as numbers, you can turn them back into dates using a field coded as:

{QUOTE

{SET Delay{=INT({MERGEFIELD MyDate})}}

{SET jd{=2415019+Delay}}

{SET e{=INT((4*(jd+32044)+3)/146097)}}

{SET f{=jd+32044-INT(146097*e/4)}}

{SET g{=INT((4*f+3)/1461)}} {SET h{=f-INT(1461*g/4)}}

{SET i{=INT((5*h+2)/153)}}

{SET dd{=h-INT((153*i+2)/5)+1}}

{SET mm{=i+3-12*INT(i/10)}}

{SET yy{=100*e+g-4800+INT(i/10)}}

"{dd}-{mm}-{yy}" @ "dddd, d MMMM yyyy"}

Note1: The field brace pairs (i.e. '{ }') for the above example are created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from this message. Nor is it practicable to add them via any of the standard Word dialogues. The spaces represented in the field construction are all required. The line breaks shown in the code aren't necessary - they're just being used to make the code structure easier to follow.

Note2: As presented, the field code assumes Windows UK/Aus Regional Settings. For US Regional Settings you need to swap the {dd}-{mm} order on the last line. The final date format switches can be any of those indicated in my original 'Answer' post.

Note3: The above field code is derived from the 'Importing Date and Time Values From Excel and Access' example in my Microsoft Word Date Calculation Tutorial, at:

http://windowssecrets.com/forums/showthread.php/154368-Microsoft-Word-Date-Calculation-Tutorial

or:

http://www.gmayor.com/downloads.htm#Third_party
Was this answer helpful?

0 comments No comments
Anonymous

2014-10-27T09:10:04+00:00

Mark - this was posted a couple of years ago... but I feel your pain.

My issue was (I think) caused by some of the data fields in the underlying data range being blank - this was deliberate as I wanted to display a blank in the merged document where the field was empty.

For some reason this resulted in the mergefield switches for date formats not functioning as they should - exactly as you describe.

My solution was to change the underlying merge data in the Excel spreadsheet to a text string via the =TEXT(value, format_text) formula.

The merge field switches were still able to reformat the data even though, ostensibly, the data now consisted of a text string and not a date value.

Hope this helps you or someone else.
Was this answer helpful?

2 people found this answer helpful.

0 comments No comments