Share via

Mail merge changes date format from UK to US

Anonymous
2010-12-02T17:18:43+00:00

I have an excel spreadsheet with columns of dates formatted as UK dates dd/mm/yyyy. When I attempt to merge this data with a word doc the dates are converted to US format mm/dd/yyyy. Even if I use dd/month/yyyy it still changes it to mm/dd/yyyy. Changing the format on the excel spreadsheet does not help. The only solution I have found is to type the date in as text starting with '. This is tedious when I have over two hundred dates to type.

Microsoft 365 and Office | Word | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Doug Robbins - MVP - Office Apps and Services 323.1K Reputation points MVP Volunteer Moderator
2010-12-02T21:46:49+00:00

See the Date Format item on the following page of fellow MVP Cindy Meister's website:

http://homepage.swissonline.ch/cindymeister/MergFram.htm

If adding the switch to the field does not work, see the Dates: day/month reversed item on the following page of Cindy's website:

http://homepage.swissonline.ch/cindymeister/MM2002/MM2002.htm

-- Hope this helps.

Doug Robbins - Word MVP,

dkr[atsymbol]mvps[dot]org

Posted via the Community Bridge

"Geascal" wrote in message news:*** Email address is removed for privacy ***...

I have an excel spreadsheet with columns of dates formatted as UK dates dd/mm/yyyy. When I attempt to merge this data with a word doc the dates are converted to US format mm/dd/yyyy. Even if I use dd/month/yyyy it still changes it to mm/dd/yyyy. Changing the format on the excel spreadsheet does not help. The only solution I have found is to type the date in as text starting with '. This is tedious when I have over two hundred dates to type.

Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org

Was this answer helpful?

7 people found this answer helpful.
0 comments

Answer accepted by question author

Anonymous
2010-12-02T18:47:58+00:00

Add explicit date formatting to the date field in word ie @ "dd/mm/yyyy"

If this proposed solution has resolved your issue(s), please mark it as  Propose as Answer  to let others know the solution that worked for you

http://office.microsoft.com/en-us/outlook-help/learn-where-menu-and-toolbar-commands-are-in-office-2010-HA101794130.aspx - Silverlight applets Mapping 2003 to 2010 commands for all Office apps.

http://office.microsoft.com/en-us/training/HA102295841033.aspx - flash applets mapping Office 2003 menus to 2007 ribbon.

Was this answer helpful?

4 people found this answer helpful.
0 comments

11 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2013-04-13T19:30:20+00:00

    Or, for the non-tech-savvy out there, see this page:  

    http://www.ashbee.co.uk/index.php/faq/48-word/97-mail-merge-reformat-date-time-and-number-fields-imported-from-excel

    Even I managed to get it sorted.

    Was this answer helpful?

    20+ people found this answer helpful.
    0 comments
  2. Anonymous
    2013-03-19T15:26:19+00:00

    I know that I'm writing this as a reply for a question written three years ago, but I was facing the same problem two days ago and googled it and found this question and the replies.

    The links posted by Doug Robbins are very helpful and interesting .. only they mysteriously solved the problems for some mergefields and some not .. still don't know the reason for that.

    So I've tried this and it worked:

    created another column in excel and let it take the dates information into the TEXT() function but with the following format TEXT(A1,"dd/mm/yyyy"), where A1 contains the date.

    I hope it works for whoever falls in the same trouble.

    Was this answer helpful?

    5 people found this answer helpful.
    0 comments
  3. Anonymous
    2015-04-26T20:37:12+00:00

    However, what would be really helpful is if Microsoft fixed the bug. 

    From Cindy Meister's site, for completeness.

    On Windows installations where the short date format in the Regional settings is not the North American standard month/day/year, Excel and Access users will find that, in cases where the day is 12 or less, the month and day will come across the link reversed.

    This is a bug; and comes from the OLEDB driver Microsoft provides for these two applications. Not only Word Mail merge users struggle with this problem - it also affects database programmers.

    In this case, simply adding a formatting switch to the merge fields will not resolve the problem.

    You have two choices: The data will have to be pre-formatted, as text, as described in thegeneral Mail merge section; or use a DDE or ODBC connection.

    Bow to Cindy.  Thank you!

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments