Set Excel to use the UK date format in mail merge
Q All the dates are appearing in the wrong format when I take information from an Excel spreadsheet into a Word document using mail merge. They are set to 'dd/mm/yyyy' in Excel but appear in the American 'mm/dd/yyyy' format in the Word document.
I have double-checked all the Region settings in both Windows and Office and they are all correctly set to the UK. The Excel column is also correctly formatted as a date. I have Microsoft Office 2003.
Khalid Hasan
A We were also perplexed when we looked at this problem. We recreated the problem on our computer although the Windows and Office Regional settings were all correct.
The fix is to tell Word how to format the date in the mail merge document. Follow the mail merge wizard until Step 5 ‘Preview your letters’. You will see the date in the document in the incorrect format.
Press Alt and F9 together and the date text will be replaced by some
different text:
{ MERGEFIELD “date” }
Click inside this grey text and add the following text:
{ MERGEFIELD “date” \@”DD/MM/YYYY”\*Mergeformat}
Press Alt and F9 again to return to the preview of the merged field. You can use different date codes for different fields.
Baffled by jargon? See our free online jargon buster.
Article tags
Related articles
Q.Why are some of the keys on my keyboard doing strange...
Q.Is my phone’s Bluetooth any use?
Q.Can I switch boot drives so that I can work on older...
St Helena, a 'small British village' in the mid-Atlantic, is seeking support and funding for a broadband connection
|
|
|
|
|
Computeractive Excel (2010) Online tutorialPrice: £19.99 |
Computeractive Word (2010) Online TutorialPrice: £19.99 |
Computeractive Powerpoint (2010) Online TutorialPrice: £19.99 |
Angry BirdsPrice: £9.99 |
Back Issue CD-Rom 14 (2011)Price: £15.99 |
Change mail-merge date format in Excel
Yours was the simplest answer and I have read so many that did not work. Thank You
Posted by Liz, 22 Jul 2010
Thank You
A simple solution - a pity the problem exists at all.
Posted by Graham, 06 Nov 2011
Date format corrected
Many thanks for this solution. It isn't exactly intuitive but a very simple (when you know) solution. Thanks.
Posted by @crazypigg, 07 Mar 2012
Change date format in mialmerge
Excellent solution. Just had a problem with quotes but once solved was up and running, many thanks
Posted by Brian Houston, 25 Mar 2012
Brilliant!
Thanks - just a note to others - the case is important!
Posted by Isla Dennis, 20 Apr 2012