Word: Change date format 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. From what I can gather the ‘native’ Excel date format is m/dd/yyyy (e.g. 9/30/2009 for September 30, 2009).

If you want the date in the mail merged document to be displayed differently, e.g. dd MMMM yyyy (30 September 2009), then you have to add a switch to the mail merge field.

Here’s how you do it in Word 2003 (Word 2007 is probably the same, though I haven’t tested it):

1. Insert the mail merge field for the date into the Word document as normal. It will look something like this (where StartDate is the name of the mail merge field in this example):
   
2. Right-click on the mail merge field, and select Toggle Field Codes.
   
3. Put your cursor after “StartDate” and before the closing } and add a space.
4. Type in the switch: \@ “dd MMMM 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.

This example shows just one date format switch — experiment with other combinations to get the date format you want. For example, “MMMM dd, yyyy” for September 30, 2009; “dd-mm-yy” for 30-9-09, etc.

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