How to import date field from Excel to Word?

[exceltm]

Using Microsoft Word Mail Merge, how do I import dates from a MS Excel Spreadsheet? Using the mail merge within MS Word, everything imports fine except dates. For example, my date of 5-22-10 in MS Excel turns into 40374 when it is imported into word. In Excel the date is formatted as a date. I want it to stay as 5-22-10 when it is imported into word. What should I do to make it import properly?

 

[VoG]

This may help http://cybertext.wordpress.com/2009/06/27/word-change-date-format-from-excel-data/

 

[exceltm]

Great answer! Worked well. Thanks so much for your quick reply.

******** type=text/javascript charset=utf-8><!--//--><![CDATA[//><!--PDRTJS_settings_2310382_post_2024={"id":2310382,"unique_id":"wp-post-2024","title":"Word: Change date format from Excel data","permalink":"http:\/\/cybertext.wordpress.com\/2009\/06\/27\/word-change-date-format-from-excel-data\/","item_id":"_post_2024"}//--><!]]>*********>Heres what worked, below:

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):
   
   
2. Right-click on the mail merge field, and select Toggle Field Codes.
   
   
3. Put your cursor after “StartDate” and before the closing } and add a space.
4. Type in the switch: \@ “dd MMMM yyyy”
   
   
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.