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

The following two tabs change content below.

• Bio
• Latest Posts

Andy Parkes

Andy Parkes is Technical Director at Coventry based IT support company IBIT Solutions. Formerly, coordinator of AMITPRO and Microsoft Partner Area Lead for 2012-2013. He also isn't a fan of describing himself in the third person.

Latest posts by Andy Parkes (see all)

• Dishley parkrun, Loughborough - August 1, 2023
• Woodgate Valley Country Park parkrun - July 22, 2023
• Abbey Park parkrun - June 8, 2023