Date format problem in Mail Merge - Word 2010

Anonymous
2011-05-04T05:45:56+00:00

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?

Microsoft 365 and Office | Word | For home | Windows

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 Reputation points Volunteer Moderator
2011-05-04T06:38:39+00:00

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.

Was this answer helpful?

700+ people found this answer helpful.
0 comments

78 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Paul Edstein 82,871 Reputation points Volunteer Moderator
    2018-06-09T02:03:03+00:00

    You probably still have the dates in a mix of d/m/y & m/d/y formats in Excel - you need to ensure they're all the same. If you format all the cells in Excel as d-mmm-yyyy, for example, you'll probably find that some are not what they presently look like.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Paul Edstein 82,871 Reputation points Volunteer Moderator
    2017-09-19T21:23:59+00:00

    The problem you're having is because your date span is not interpreted as dates but as a text string, meaning you have two different data formats in the same column. Mailmerges are designed to work with just one data format per field. If your date spans are being output correctly, that means the mailmerge is interpreting the data field as text; hence the singular dates being output like 42899. Dealing with this requires some involved field coding:

    {QUOTE{SET Val {MERGEFIELD Data}}{IF{REF Val}= {=Val} {QUOTE

    {SET jd{=2415019+{MERGEFIELD Data}}}

    {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}" @ "DD/MM/YY"} {MERGEFIELD Data}}

    Naturally, you'll need to replace 'Data' with whatever your field name is (3x). You can also change the formatting switches. However, if you're using a US format, you'll also need to swap the {dd} & {mm} fields around (i.e. {mm}-{dd}).

    Note: The field brace pairs (i.e. '{ }') for the above example are all 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 practical to add them via any of the standard Word dialogues. The spaces represented in the field constructions are all required. The line breaks in the field code are optional; they don't affect the output but do make the structure easier to follow.

    For a macro to convert the above text representation of the field code into a working field code, see Convert Text Representations of Fields to Working Fields in the Mailmerge Tips and Tricks thread at:

    http://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html

    or:

    http://windowssecrets.com/forums/showthread.php/163017-Word-Mailmerge-Tips-amp-Tricks

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  3. Paul Edstein 82,871 Reputation points Volunteer Moderator
    2017-08-12T06:39:29+00:00

    I did - and that's the basis on which I replied...

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  4. Paul Edstein 82,871 Reputation points Volunteer Moderator
    2017-08-12T02:53:23+00:00

    A possible cause is that where your document is set up as a mail merge main document connected to an OLE DB data source (any OLE DB data source), Word interprets all dates that are in aa/bb/YYYY type formats as US format dates, not just dates coming from the data source.

    ...

    As long as you know that the connection is going to be an OLE DB one, one way to avoid it is always to use/enter/construct US/format dates.

    Provided one inputs the dates in a format consistent with the PC's regional language settings, they should be interpreted correctly by the OLE DB engine - which is what Word ordinarily uses for mailmerges from Excel. The switches described in the 'Answer' will coerce them back to whatever output format is required.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments