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
    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
  2. Anonymous
    2017-08-12T06:37:39+00:00

    Why not do the experiments and see for yourself what actually happens in the type of scenario I have described? "Should" is fine as an initial hypothesis but sometimes does not turn out to be correct.

    Was this answer helpful?

    0 comments
  3. 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
  4. Anonymous
    2017-08-12T01:40:29+00:00

    Not sure how the PDF converter would know to do that.

    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.

    So as a rather unrealistic example, if your regional settings have the date in D/M/YYYY format you might hope that if you wanted 1st August 2017 you could insert a field

    { QUOTE "1/8/2017" @DD-MM-YYYY } would have result 01-08-2017. But in fact, as soon as you are using an OLE DB source, it is interpreted as 8th of January and you end up with 08-01-2017. The same is true of any "ambiguous" day/month combinations, but Word gets it right if the day/month is unambiguous, e.g. 20/1/2017. It's the same whether your date is in D/M/YYYY, M/D/YYYY, D-M-YYYY or M-D-YYYY format. It would be the same for any date that is basically constructed as a piece of text (e.g., entered via an ASK field or constructed from a calculation) and which has a date format applied. 

    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. If you don't know what the connection type will be you cannot rely on that, but as far as I know, dates in YYYY-MM-DD format are always interpreted correctly by Word.

    Was this answer helpful?

    0 comments