format date in Excel to output in Word mail merge doc

[Davidprince]

Ladies and gentlemen
This seems fairly trivial and I apologize for the question if that is the case, but my extensive searching of Micrsoft and other sites has failed to find an answer (there may indeed not be one). I am doing a mail merge with data from Excel and a Word letter. I would like to have the letter have a different date on each time and I use a merge fild to achieve that (letters go to clients on different dates and the date is stored in the Excel spreadsheet for future reference). I would like the date to be something like November 20, 2009 or 20 November, 2009. Whilst I can enter the date in Excel as text '20 November 2009, I thought I could use the short form 20/11/09. When I use the short form date however, the 20/11/09 comes up in Word. Is there a way around this issue or do I need to have the users enter the date as text?
Thanks for your assistance
David Prince

 

[Lilliabeth]

http://www.bing.com/search?q=excel+date+format+mail+merge&form=QBRE&qs=n

As you can see there are a couple of ways to do this. You didn't say what version of Office you use, but if it is 2003 or earlier, you can select one check box in Word:

On the Tools menu, click Options, and then click the General tab.
Click Confirm conversion at Open.

--Lilliabeth

 

[macropod]

Hi David,

To get the date format you want, you can add a formatting picture switch. In Word:
. select the mergefield;
. press Shift-F9 to expose the field coding. It should look something like {MERGEFIELD MyDate} where 'MyDate' is your mergefield's name;
. delete anything appearing after the mergefield's name and add '\@ "d MMMM yyyy"' to the field, as in {MERGEFIELD MyDate \@ "d MMMM yyyy"}. With this switch your date will come out as '2 August 2008' (or whatever the current date is). Other possible date formatting switches include:
. \@ "dddd, d MMMM yyyy";
. \@ "ddd, d MMMM yyyy";
. \@ "d MMM yyyy";
. \@ "dd/MMM/yyyy";
. \@ "d-MM-yy";
Note: Note: you can swap the d, M, y expressions around, but you must use uppercase 'M's for months.
. position the cursor anywhere in this field and press F9 to update it;
. run your mailmerge.

One thing you'll notice about this is that the date in the mailmerge output document will update whenever you re-open the document, which can be problematic. There are two main ways you can handle this:
1. Unlink the fields after each mailmerge, which you can do via Ctrl-A then Ctrl-Shift-F9; or
2. Embed the Date field in a QUOTE field, thus:
{QUOTE{MERGEFIELD MyDate \@ "d MMMM yyyy"}}
or
{QUOTE«MyDate»}
To do this, simply select the date field, press Ctrl-F9 to embed in in another field, and type 'QUOTE' inside the left field brace. With this, the filed will automatically unlink immediately the mailmerge output document is created.



Cheers
[MS MVP - Word]

 

[Davidprince]

Ladies and Gentlemen
Thank you for your prompt responses. As an interim step I am triallng the Excel spreadsheet using the date in text format so that the mail merge picks up the actual date rather than one contrived by microsoft' rules. The other soluton of course is to write amail merge progam in VBA and resolve the date issue there. Unfortunately for the moment I don't have the time to do that, it will no doubt become a project for a rainy day.
By the way I am using Office 2007.
Thanks for your input it is much appreciated.
Regards
Davi

 

[macropod]

Hi David,

The solution I posted takes about 30 seconds to implement, on a once-for-all basis for all Word versions.

Your response suggests you haven't tried it, which is a shame, since reformatting your dates in the Excel data source probably takes as long or longer - and needs to be done every time you add a record to it. Plus, the field code solution makes the mailmerge date format independent of the format in the data source.


Cheers
[MS MVP - Word]

 

[Davidprince]

Thanks Macropod, I apologize for not persisting with your solution. I finally got it working after several botched efforts. This will please the computer illiterate users at the office.
Regards
David