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: Newest
Most helpful Newest Oldest
  1. Paul Edstein 82,871 Reputation points Volunteer Moderator
    2014-09-24T20:44:19+00:00

    To control number & currency formatting in Word, add a numeric picture switch to the mergefield. To do this:

    1. select the mergefield;
    2. press Shift-F9 to reveal the field coding. It should look something like {MERGEFIELD MyData};
    3. edit the field so that you get {MERGEFIELD MyData # $,0.00} (or whatever other numeric format you prefer - see below);
    4. position the cursor anywhere in this field and press F9 to update it.

    Note 1: The '# $,0.00' in the field is referred to as a numeric picture switch. Other possibilities include:

    • # 0 for rounded whole numbers

    • # ,0 for rounded whole numbers with a thousands separator

    • # ,0.00 for numbers accurate to two decimal places, with a thousands separator

    • # $,0 for rounded whole dollars with a thousands separator

    • # "$,0.00;($,0.00);'-'" for currency, with brackets around negative numbers and a hyphen for 0 values

    Note 2: The precision of the displayed value is controlled by the '0.00'. You can use anything from '0' to '0.000000000000000'.

    If you use a final ';' in the formatting switch with nothing following, (eg # "$,0.00;($,0.00);") zero values will be suppressed. Note that this suppresses 0s resulting from empty fields and from fields containing 0s.

    Note 3: If you use a decimal tab or right-aligned tab to align the values, wrap the switch in quotes (i.e. # "$,0.00") and insert a tab into the field code after the $ sign, you can have the values output with the decimal alignment occurring after the $ sign.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2014-09-24T17:14:02+00:00

    Very helpful answer. 

    Question: is there a similar fix for merging Currency amounts? When I merge a currency that ends in Zero, for example $5,355.40, it omits the final zero on the merge, Showing as $5355.4 (It also ommits the comma, which I would love to include as well.)

    Thanks!

    Was this answer helpful?

    0 comments
  3. Paul Edstein 82,871 Reputation points Volunteer Moderator
    2014-08-19T22:03:02+00:00

    See the reply marked as the Answer...

    Was this answer helpful?

    0 comments
  4. Anonymous
    2014-08-19T14:33:53+00:00

    My issue is that in my excel sheet I have the date written out: August 19, 2014, but when it transfers to my mail merge it re formats it to 8/19/14.  How do I get the date to stay the same?

    Was this answer helpful?

    0 comments