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: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2012-06-19T15:58:41+00:00

    One possibility is to create a query in the Access database that uses the format function to convert the date to the string format that you really want, e.g.

    SELECT birthdate, format(birthdate,'DD/MM/YYYY') as bdstring

    FROM mytable

    That may play havoc with any Memo fields that you need to merge, though.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2012-06-19T22:47:10+00:00

    (This is a considerably modified update to a previous message)

    You can try the following field codes.

    The codes assume a date in either M/D/YYYY format (or MM/DD/YYYY etc.) or D/M/YYYY format, with the separator "/". You need to know which way round the date is coming in. You then insert the following nested fields:

    { QUOTE { SET d123 { MERGEFIELD mdydate } }{ SET d12 "{ d123 }^0" }{ SET d3 { ={ QUOTE "({ d12 })/({ d123 })" } } }{ SET d2 { = { QUOTE "1/1^{ d12 })" } }{ SET d1 { ={ QUOTE "{ d2 }*{ d12 }" } } } }

    If the original sequence was M/D/Y then YYYY-MM-DD  could be inserted like this:

    { d3 #0000 }-{ d1 #00 }-{ d2 #00 }

    If the original sequence was D/M/Y then YYYY-MM-DD  could be inserted like this:

    { d3 #0000 }-{ d2 #00 }-{ d1 #00 }

    All the {} have to be the special field brace pairs that you can insert using ctrl-F9.

    To use the codes with your own date fields, you would need to substitute your own merge field name instead of "mdydate" in { SET d123 { MERGEFIELD mdydate } }

    There is a sample document containing these codes (so you can copy/paste) at

    https://skydrive.live.com/redir?resid=4DE3A20A33EEA203!185

    To try it,

    a. download the .zip, and unzip the two documents

    b. open

    20120619 format dates in 1800s.docx

    c. attach the other file (20120619 mmds.docx) as the data source for a merge

    preview the merge or merge to a new document.

    I'd previously posted a document with a much more lengthy set of field codes. I can repost if you find the above do not work.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2012-08-13T13:16:33+00:00

    Hi Paul,

    I've referenced your above answer in August 2012 and it works perfectly on an as-required basis for Date fields in mail merges. Thank you very much.

    However I'm interested in a solution that fixes the cause and not the symptoms. Is there a specific Microsoft Update that permanently resolves this OLE DB issue and leaves dates in their original regional format?

    "...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."

    Cheers,

    Greg.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  4. Anonymous
    2012-08-13T19:51:20+00:00

    "fixing the cause" is not particularly straightforward. It would require Microsoft's OLE DB provider to pass what the user sees in Excel. But it doesn't, and it's not completely obvious that it could, unless you can assume that the Excel sheet was created using the same regional settings as you want to use in Word.

    AFAICS

    a. the way the Jet/ACE/Excel OLE DB provider has not changed recently in this respect.

    b. the problem is not merely to do with the coding of the provider, but to do with a number of conceptual issues. But even if we were just talking about coding issues...

    c. ...these providers are in "maintenance mode." It wouldn't surprise me to learn that that's because hardly anyone knows enough to maintain them.

    Was this answer helpful?

    0 comments