Microsoft Word/Mail merge dates from excel
I'm embarrased asking you another question so soon, but since it involves working with the excel file you just helped me with, I thought it might be better to ask you than someone that has no idea what my excel file is like.
The attendance rosters in excel are now all working great, thanks to your help. I embarked on making the corresponding mail merge letters for the class rosters using MS word and found that the date/time merges don’t work the way I need them to. I’m getting 5 digit numbers instead of dates and times.
I’ve seen a lot of online suggestions to switch the data source from OLE to DDE.
When I get to the “Confirm Data Source” dialog box and choose the MS Excel Worksheets via DDE, I get a message “Word could not re-establish DDE connection to Microsoft Excel to complete the current task”
There are many recommendations to edit the merge field in MS word by adding something like “\@ "dddd, d MMMM yyyy"”, but I’m still keep getting a 5 digit number instead of a date of any kind.
My goal is for the date to merge from excel into word using a “Wednesday, January 1, 2014” date format which is the way I formatted them in Excel. I’m having a similar problem with time merges in the same appointment letters. I suspect the solution to one is going to be similar to the other, with a bit of modification.
Windows 7/Office 2010
Both problems are the same problem in essence - although Excel DISPLAYS the data in the format you want, it is stored as a serial number - with 1 being the 1st January 1900 and the 1st October 2014 being 41913. The time values are stored as decimal fractions (so 0.5 would be mid-day). Although you would think that Word would realise that a date is a date, it doesn't! I also run into problems in that as I'm in the UK a date like 1/2/14 could be 1st February or 2nd January depending upon the national preference (in the UK it would be 1st February). SO to the solution - as has been suggested, using a formatting switch in the field SHOULD work - I'm a bit worried that you are still getting a number as the format switch you mentioned should work ( \@ "dddd, dd MMMM yyyy" ). One option I've used in the past is to get Excel to do the work for you - rather than have it return a date, use the TEXT worksheet function to turn that date into the text - which then WILL merge correctly
=TEXT(A1,"dddd, d MMMM yyyy")
where A1 is the cell or formula you want to have correctly displayed. As you say, doing similar for the times will work as well.
I hope that this helps but do let me know if I can help further!
About Microsoft Word
This topic answers questions related to Microsoft Word stand-alone or Microsoft Office Word including Word 2003, Word 2007, Office 2000, and Office XP. You can get Word help on formatting text, tables, tabs, fonts, styles, general Word layouts, bullets, headings, and outlines, using templates, toolbar modifications, and using Track Changes. You may also find tips on linking Word and Excel embedded objects including charts. This site does not provide a general Word tutorial nor the basics of using a word processor. It provides specific answers to using Microsoft Word only. If you do not see your Word question answered in this area then please ask a Word question here