Word: Change date formats from Excel data

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:

• Word: Change decimals from Excel data: http://cybertext.wordpress.com/2009/06/18/word-change-decimals-from-excel-data/

[Links last checked May 2011]

Has this tip helped you? saved you time? saved your skin? You can thank me by clicking on the cup and buying me a coffee. (An E-Junkie shopping cart page will open where you can pay for my coffee via PayPal.)