Date formats incorrect after mail merge using Word 2007

I was asked to look at something by a friend yesterday

She was doing a mail merge using Word 2007 with an Excel spreadsheet as the data source

One of the fields was a date and was in the British DD/MM/YY format on the spreadsheet but when she did the merge it was in the US MM/DD/YY format (It also had the time on the end as well)

My initial thought was that I just needed to tell Word to change the format of the merge field but couldn’t see anywhere on the mailings tab to do that

Then I found this KB article

304387 – Date, Phone Number, and Currency fields are merged incorrectly when you use an Access or Excel data source in Word

Basically the underlying data source stores the date as a number and applies what ever formatting you need (if you have any Excel experience this wont be a shock!)

So when it get’s passed through to Word for use in the merge it then applies it’s own formatting

To change the formatting do the following

Find the merge field you want to change and right click it. Then click the “Toggle Field Codes” option

It should change it so it looks something like this (if the merge field is called “Birthday”)

{MERGEFIELD Birthday }

You then have to change the field to tell it you want a specific date format

{MERGEFIELD Birthday \@ "dd/MM/yy" }

The case is really important here

M means “month” whereas  m means “minute”

You can tweak the format as you need for example “dddd dd MMMM yyywy” would give you Friday 3 October 2008

I’ve tried this in Word 2007 but the KB article says it applies to Word 2003 as well

The KB article also points out how can change currently and phone numbers using the same technique