h1

Word: Change date format from Excel data

June 27, 2009

When you’re using an Excel spreadsheet for mail merge data in Word, any dates come in in the ‘native’ Excel date format even if you’ve changed the date format for the relevant cells in Excel. From what I can gather the ‘native’ Excel date format is the US date format of m/dd/yyyy (e.g. 9/30/2009 for September 30, 2009).

If you want the date in the mail merged document to be displayed differently, e.g. UK/Australian date format dd MMMM yyyy (30 September 2009), then you have to add a switch to the mail merge field.

Here’s how you do it in Word 2003 (Word 2007 is probably the same, though I haven’t tested it):

  1. Insert the mail merge field for the date into the Word document as normal. It will look something like this (where StartDate is the name of the mail merge field in this example):
    date_format_mail_merge01
  2. Right-click on the mail merge field, and select Toggle Field Codes.
    date_format_mail_merge02
  3. Put your cursor after “StartDate” and before the closing } and add a space.
  4. Type in the switch: \@ “dd MMMM yyyy”
    date_format_mail_merge03
  5. Right-click on the mail merge field again, and select Toggle Field Codes.
  6. Save the document. The next time you run a mail merge, the date will be in the format you entered at Step 4.

This example shows just one date format switch — experiment with other combinations to get the date format you want. For example, “MMMM dd, yyyy” for September 30, 2009; “dd-mm-yy” for 30-9-09, etc.

46 comments

  1. You are the only one SHOWING how to change the date, so far the explanation didn’t means anything but your sample it was so easy.
    Thanks to the team.
    Pascale


  2. Thanks that a real help. this is the only place on the web i could find the answer and it works a treet!


  3. Hi, the sample was perfect… but now I have another issue…..I have a word merged document with more than one date fields, and some appear in English an another ones in Spanish… do you know if there is any instuction to select the language?

    hope you can help me….


  4. Sorry, I’ve never worked with any other language than English. That said, in Word 2003 you can select the entire document (Ctrl+A), then go to Tools > Languages > Set Language and choose the language. That might solve the Word problem; not sure if you can do anything like this in Excel.


  5. I’m having trouble getting it to merge the correct YEAR. My dates on my excel spread sheet are in 2008 and 2009, and it keeps putting them in my letters as 2004 or 2005. Help?


  6. Thank you! Word’s help is abysmally bad and doesn’t even mention this as far as I can tell. Putting the link on del.icio.us ;-)


  7. who ever noted down how to do ‘american date change to UK in word mail merge from excel doc’ using toggle codes you just save my ar#e THANK YOU, YOU LEGEND!!!


  8. Brilliant.

    Thankyou!

    FIXED!

    LEGEND!!!


  9. Wonderful. Thanks a mill buddy!!


  10. Thank-you so much! This is what I have been looking for. all of the other websites I have searched only have shown me the date function in excel, which doesn’t do anything. Very helpful


  11. Simple – the idea was great and works perfectly in Office 2007.
    Thank you.


  12. Great Stuff! Saved some time!
    Also works in Word 2010!
    Just what I needed!
    Thanks


  13. Thanks a lot. your explanation was simple. ms help is very complicated


  14. Absolute legend! it was seriosly doing my head in. One happy little bee now:) Thank you very much!


  15. Glad I could help!

    –Rhonda


  16. THANKS VERY MUCH VERY EASY TRIC


  17. Perfect, exactly what I needed!! Thank you!


  18. Thank you, this surely helped.


  19. This doesn’t work! What do I do?!


  20. Hi Nel

    Make sure you’re typing inside the { } and that you put in the spaces correctly or leave them out if they aren’t listed — spaces (or no spaces) are important. For example, it won’t work if you have \@, instead of \@.

    –Rhonda


  21. Excellent. It Worked. Thank you for the post — SATYA KUMAR


  22. Excellent. A solution without having to worry about formatting in Excel nor how the data is read in. Simple and easy!


  23. Ive got a date that I have merged and its coming through as

    07/00/2012

    Basically it doesnt recognise the month. Its just replacing the mm with 00.

    Any ideas


  24. Hi Deanne

    What did you put in the field code? Did you surround the date format with double quote marks?

    Have you tried the other variations — “dd MMMM yyyy”, “dd-mm-yy”, “MMMM, dd yyyy”? Did they work? If they worked, then I suspect there’s something wrong with the way you’ve entered the format in the field code (e.g. if you didn’t close the double quote marks, it’s likely that it wouldn’t work).

    –Rhonda


  25. Hey Everyone,

    Im having a major problem with a mail merge and dates. I have tried all the codes above, and I have changed language settings to english uk, but no matter what date combination I try it will not do what I want, which is to output in DD/MM/YYYY. The only time it will output in the right format is if the date is too big to be a month, so example 31/03/2012 comes out fine, but 06/03/2012 comes out as 03/06/2012, and even when I try putting the format as «MERGEFIELD “Invoice_Date” \@ “MMMM d, YYYY” \@ MERGEFORMAT» it will come out as “June 3, 2012″ instead of “March 6, 2012″. The same happens when I try «MERGEFIELD “Invoice_Date” \@ “MMMM d, YYYY”» and every other date format I can think of.

    Any help PLEASE, I’ve been trying to do this for hours, and I can’t help but think I’m doing somet silly because the formula seems to work for everyone else on all the forums I come across.

    Thank you
    Andy


  26. first link I clicked on and it was spot on. cheers!


  27. hey,

    Thanks, This trick worked well in word 2010. But now I have another problem. I put ID# “612-00009231″ in the excel sheet, but it appears as “00009231-612″ in hte merged word document. any idea on how to solve this?


  28. Hi Salah

    It may depend on a couple of things — the format of the Excel cell (number, date, time etc.) AND the field switches used in Word. You’ll have to check the online Help in Word for field switches for the type of cell formatting you’re using in Excel.

    That’s about as far as my knowledge goes on this. So also try the Microsoft Answers forum for Word and/or Excel for some help.

    –Rhonda


  29. That is awesome, the example was perfect. I spent hours upon hours trying to get this right for an urgent job and ended up doing it manually to 30 files….thanks so much


  30. It worked well for me… Thanks for the team


  31. I used the above to change the date to UK which was great. I wondering if you could help me with another problem.
    I have coloured text in excel which I would like to be merged into the word letter. Could you tell me if this is possible.
    Thanks


  32. Hi Jane

    I have no idea if that can be done. I rarely use Excel and rarely need to bring its data into Word, so I suggest you hop on over to the Microsoft Answers forum (http://answers.microsoft.com/en-us/office) and ask your question on the Word and/or Excel forums.

    –Rhonda


  33. Hello. i am having a slightly different problem. The weird thing is this seems to have happened without me changing anything. Excel is set to UK format (02/07/2012) the word mail merge has \@ “dddd, dd MMMM yyyy” on it. But when I do the merge it puts 7 February 2012 when it should say 2 July 2012. What’s really weird is if i put 24/07/2012 on Excel the merge comes up correctly (24 July) it seems it uses US format if the day is lower than 12 but the UK if the date doesn’t work the other way. Crazy huh???

    Whatever I do I cant seem to get it to always be in UK on the merge.

    Any ideas????

    Thanks


  34. Hi Daniel

    According to your comment, you’re also adding ‘dddd,’ to the string — have you tried removing that and just using the string in the main article?

    I’m in Australia and we use the day-month-year format all the time, and this worked fine for me.

    –Rhonda


  35. THANK YOU


  36. This has not worked for me – any suggestions on how to fix my problem ….
    when i put this in to the word doc {MERGEFIELD “Date” \@ “dd/MM/yyyy” }i still get 41044 it should read 15/05/2012

    anyone HELP


  37. Hi DC

    I assume you inserted a field first and that what you’re seeing (the stuff surrounded by the curly braces) is the field code when toggled? If you’ve just typed this ‘code’ in, then it’s NOT a field and therefore won’t work.

    Assuming it’s a toggled field code, then I can’t see any reason why it’s not working. Check that your have spaces in all the places they are meant to be, and check that you have quote marks where they are meant to be.

    Have you tried to see if it will work using a different date format (i.e. without the slashes), such as “dd MMM yyyy”? If that works, but the one with slashes doesn’t work, then it could be to do with the slashes.

    You can also check this later blog post on the same issue: http://cybertext.wordpress.com/2011/05/30/word-change-date-formats-from-excel-data/ and the info on date-time field switches near the very end of this web page: http://www.gmayor.com/formatting_word_fields.htm

    –Rhonda


  38. Hi Rhonda,

    Something I discovered, which may be of help to your readers:
    “mm” won’t work for month, they need to be capitalized: “MM”, “MMM” or “MMMM”. Similarly for day and year, they need to be lower case. This may or may not be a standard bug to everyone, but it’s the only way I got it to work for me!

    Users who get their month coming through as “00” are likely using lowercase m’s for month instead of uppercase.

    Just a thought!


  39. Thanks Ross

    –Rhonda


  40. Hi All
    I was also having the same problem with the month showing “00” with Word 2010 after adding the switch to change MM/dd/yyyy to dd/MM/yyyy.

    I tried endless formats trying to fix the “00” month issue although it turned out my formatting was not the issue. Once I did “finish and merge” the output finally took notice of my desired format and fixed to “00” motnh issue.

    { MERGEFIELD “Period_End” \@ “d/MM/yyyy” }

    Cheers
    Damian


  41. I was very impressed that I found your fix and it worked like a dream. Thank you, I will be using it a lot


  42. You made my day! Thank you for the clear directions. I have had the formula but could not make it work until I saw how you did it. I was thrilled that I could complete my directory and the dates merged as I had planned. Angelica


  43. it worked, thank you!


  44. you are the BEST!


  45. This really helped me a lot. Thank you very much..



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 345 other followers

%d bloggers like this: