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. Anonymous
    2016-10-10T12:39:33+00:00

    In my case, the date column was empty and the first date that showed was 01.11.2016; Word had no way to know which format it was so it decided (those imperialistic Americans!) that it was January 11th, 2016, when in fact I wanted it to be November 1st, 2016. I simply added a dummy date in the date column with a clearly date identifiable date such as 31.12.2099. The date then displayed correctly in the merged document. You could alternatively add a full data line with the information.

    Was this answer helpful?

    2 people found this answer helpful.
    0 comments
  2. 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
  3. Anonymous
    2012-08-14T09:15:56+00:00

    Thanks - thats great. Worked first time.

    Rob

    Was this answer helpful?

    2 people found this answer helpful.
    0 comments
  4. Anonymous
    2012-06-19T09:51:13+00:00

    This solution works fine for me but I bump into that other Microsoft date problem - the nineteenth century.

    I'm merging data from Access 2010 to a Word document for biographical details of around 500 people, may of whome were born in the nineteenth century. 20th century dates mostly convert in good shape, but early dates revert to US standard date forms.

    So, I need to convert them manually. Any solutions?

    Was this answer helpful?

    2 people found this answer helpful.
    0 comments