Word: Change date formats from Excel data

May 30, 2011

When you’re using an Excel spreadsheet for mail merge data in Word, any dates come in in the ‘native’ Excel date format even if you’ve changed the date format for the relevant cells in Excel. I use the date format dd mmmm yyyy in Excel, but it comes into a Word mail merge date field as mm/dd/yyyy, which is not what I want at all.

If you want the date in the mail merged document to be displayed differently, then you have to add a switch to the mail merge field in Word.

Here’s how you do it:

  1. Insert the mail merge field for the date into the Word document as normal.
  2. Right-click on the mail merge field, and select Toggle Field Codes.
  3. Put your cursor after the closing of the field name (e.g. “Date”) and before the closing } then add a space at that position.
  4. Type in the switch: \@ “d MMMM yyyy” (this switch converts the Excel date into something like 25 December 2011; if you want 25 Dec 2011, then enter “d MMM yyyy”).
  5. Right-click on the mail merge field again, and select Toggle Field Codes.
  6. Save the document. The next time you run a mail merge, the date will be in the format you entered at Step 4. (You can quickly test it works by clicking the Preview Results icon on the Mailings tab [Word 2007].)

You can get more information from Word’s online Help for other options for this  field switch — search for Date-Time Format switch in the Help.

See also:

[Links last checked May 2011]


  1. Hi That kinda worked for me, but now word is showing the month as 00 instead of 08 (e.g)…. Help please :)

  2. Hi Sarah

    The number of ‘M’s you type is important — please check you typed only two, three or four. Two = numbers (e.g. 01 for January); three = shortened letters (e.g. Jan for January) and four = full letters (e.g. January).

    That’s exhausted all the possibilities I can think of!


  3. Hi Rhonda,

    I checked and i have definitely entered it as dd/mm/yyyy. I have no idea where to go from here…. It’s driving me mental!

    Thanks for your reply :)

  4. thanks for this.. it really helps!

  5. I have a different problem. The date I was typing is in Excel is ” July 9, 2011″. After toggling i was getting the date as “September 7 2011”. Help me solve it!

  6. Hi Sathya

    The technique above should solve it. There are more details (with screen shots) in this later blog post: https://cybertext.wordpress.com/2009/06/27/word-change-date-format-from-excel-data/


  7. @Sarah – you need to enter the month bit as MM not mm

  8. Hi. I want to ask about the dd mmmm yyyy date format in Powerpoint. I want to add that type of date in the header on the Notes and Handout, but it seems like Powerpoint default setting does not support that date format. How can I change the usual dd mm yyyy format to dd mmmm yyyy?

  9. Thank you so much. I had this problem repeatedly, but not always – no idea what it depends on, but sometimes the format changed, sometimes not. This seems to do the trick forever, practically hardcoding date format into Field code. Nice!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: