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. Paul Edstein 82,871 Reputation points Volunteer Moderator
    2016-06-22T10:56:00+00:00

    I have the same problem... any advise can fixed? Thanks

    What have you tried? Changing from the default OLEDB connection method to the DDE or ODBC connection method should resolve the issue.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2016-07-20T07:31:57+00:00

    You are probably encountering a different problem from the original post. In your case, what is more likely is that you have mixed data types in the first 8 cells in your Excel date column, in which case the OLE DB provider may decide that the column is numeric, rather than date, and will return Excel's underlying date number (a day count from a fixed date).

    If you control your data source, it's probably worth checking each of those 8 cells and seeing what is actually in them (and I have also seen some evidence that if the cell contains a formula that results in a date, that may affect the outcome).

    Connecting via DDE would typically solve that problem but suffers from other limitations. Copy/paste into Word document and using that as a data source can also solve certain problems. But the fact is that none of the builtin methods that Word has to connect to Excel data solves all the problems we frequently encounter in this group.

    I have gone through most of the posts here but i am not sure if the issue that Mark Watson raised was resolved.

    I am a beginner, and i am trying to make the mailmerge switches work. It used to work before and now all of a sudden it no longer does. 

    This is the one that i used:

    **{**MERGEFIELD DATE_OF_BIRTH @ "dd MMMM yyyy"}

    But it is still showing the number version of the date with this case 33210

    I am not sure what you meant when you said:

     "If you control your data source, it's probably worth checking each of those 8 cells and seeing what is actually in them"

    But what I did was make sure that my source file cells are formatted as "dd MMMM yyyy"  to show e.g. 20 July 2016

    This has been bugging for weeks now, and any help will be greatly appreciated.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2016-07-20T08:08:11+00:00

    I did not resolve with it.

    In the same page, the same field show once 5/11/16 and once 11/5/2016.

    Is not a problem the format but the date, it swaps between month and day. The field is the same and the format is the same ...

    what could i do?

    Was this answer helpful?

    0 comments
  4. Anonymous
    2016-07-20T12:15:49+00:00

    I have the same problem, my Excel spreadsheet has two date fields "qualification date" and "expiry date". "qualification date" is a mandatory field, "expiry date" is an optional field and therefore some rows have null values.

    All instances of "qualification date" display correctly and format such as '@ "dd-MMM-yyyy"' works correctly.

    However for "expiry date",  Word assumes the format to be "MM/dd/yyyy" unless the value of the first two positions is greater than "12" in which case it uses "dd/MM/yyyy" as the format.  My workaround is:

    {SET ED1 { MERGEFIELD expiry_date}} { IF { ED1 @ "dd" } > "12" . { ED1 @ "dd-MM-yyyy"} . {ED1 @ "MM-dd-yyyy"} }

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments