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

6 thoughts on “Date formats incorrect after mail merge using Word 2007

  • Dear Andy
    I just wanted to let you know I have spent several hours trying to work out how to stop my date from an Access query changing to US format when merging into Word. I have sifted through all sorts of articles but none helped. Then I found yours and it worked! Thank you very much.

    Kind Regards
    Kate

  • I can’t believe we are now with Office 2013 in the year 2014 and MS can’t develop a simple solution for this – how about for people who set their region to a country that uses DD/MM/YY for Office to use that format. Or even simpler, how about a simple option within Office??

    Especially given the US is about the only place that uses mm/dd/yy

    Typical US centric software

Leave a Reply

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