Format mail-merge date fields in Microsoft Word
Microsoft Word will format dates as mm/dd/yyyy as the default in mail merge documents. The field codes can be changed to enable dates in any format.
The problem with Microsoft Word’s mail merge is that it doesn’t matter what format the dates may be in the data source (eg: Excel), the dates will inevitably be displayed as mm/dd/yy. In Australia (like most of the English-speaking world) we display dates as dd/mm/yyyy, so the default formatting is problematic. This can easily be remedied.
This tutorial assumes that a mail merge document has been successfully created in Microsoft Word using a data source in Microsoft Excel.
Procedure
Step 1
Open the mail merge document that you wish to edit. On the “Mailings” ribbon, click both “preview results” and “highlight merge fields”.
Step 2
Right-click on the field that you wish to edit, and choose “toggle field codes”. The date should transform into a code string in the format {MERGEFIELD Source_of_Date}
Step 3
Edit the code after the reference to the source field, but before the closing curly braces. For example:
{MERGEFIELD Source_of_Date \@ "dd MMMM yyyy"}Step 4
Right-click on the field that you were editing and select “toggle field codes” again. The changes should be reflected in the format of the date.
Formatting dates
The date format can be customised as required. Simply combine the characters below (d, M and y) with any other characters (eg: “/” or “-“) to create a date in the desired format.
Day (d)
- d = The date as a number without a trailing zero (eg: 6th of the month is “6”)
- dd = The date as a number with a trailing zero for the first nine days (eg: 6th of the month is “06”)
- ddd = The day as a three-letter abbreviation (eg: Thursday is “Thu”)
- dddd = The name of the day in full (eg: “Friday”)
Month (M)
(Note: Months are an upper-case ‘M’ to distinguish them from minutes)
- M = The month as a number without a trailing zero for single-digit months (eg: May is “5”)
- MM = The month as a number with a trailing zero for single-digit months (eg: May is “05”)
- MMM = The month as a three-letter abbreviation (eg: September is “Sep”)
- MMMM = The name of the month in full (eg: “August”)
Year (y)
- yy = The last two digits of the year (eg: 1998 is “98”; 2015 is “15”)
- yyyy= The year in full (eg: “2015”)
Examples
If the date today is Thursday 16 June 2015, then:
{MERGEFIELD Source_of_Date \@ "dd MMMM yyyy"}= 16 July 2015{MERGEFIELD Source_of_Date \@ "dddd d MMMM yyyy"}= Thursday 16 July 2015{MERGEFIELD Source_of_Date \@ "d/MM/yy"}= 16/07/15{MERGEFIELD Source_of_Date \@ "d-MMM-yy"}= 16-Jul-15
Comments
7 responses to “Format mail-merge date fields in Microsoft Word”
Above is very useful. Thanks.
However, is it possible to convert a field containing only a three letter abbreviation of months e.g: Jan, Feb, Mar… into full months e.g. Jan to display as January etc. ?
just click on merge option go to toggle and write \@ ” DD MMM YYY”…three letter of MMM is use for Jan, Feb
Hi,
Is any way in word mail merger toggle option that we can write “21st Sep 2011” format
How can I display the month and date in a mail merge document where Excel contains my data. I have tried all methods suggested online. Cannot get it to work.
I want it to print as 6/21. I put in the mergefield Date \@ “M/d”.
This does not work. AND I had it defined in Excel as text and entered ‘6/21.
Hello Marie, this will only work in Excel if your text is formatted as date. If, for instance, you had a date (let’s say in Australian format) of “21/6/2018” in your Excel sheet, you could then set your mail merge in Word to have the format
@\ "M/d"and expect the date to print as “6/21”. The format of dates in Excel and Word are independent.Thank you so much for this tip Adam.
Yes, great tip. But what do you do if you also have to include other escape sequences like \b or \f for generating line breaks? They don’t seem to work with \@.