Hi there Macropod,
I have attached a sample Word document (.docx) and a corresponding sample of data (.xlsx). The document and data is factitious, but representative of my problem.
Referring back to my original post, I think I was misleading though in what I said.
I posted ... "However, when attempting to mail merge that field, MS Word will not take any account of the mail merge merge formatting switches and defaults to showing it in the US format MMMM dd yyyy. It is not taking any account of my switch which is {MERGEFIELD End_Date \@ "dd MMMM yyyy"}".
In actual fact, the above is not what I should have said. Nothing of the like - it is actually all to do with how MS Word is interpreting the dates - not how it shows/formats the dates.
What is actually happening is that MS Word is showing the date is "dd MMMM yyyy" as I want it to. However, it is interpreting the date in some instances as being US format, and not UK format. That is the nub of my problem. I am sorry and I've no idea why I wrote the problem down as I did originally. It made sense to me at the time (!).
It is very evident that the dates it is having problems with are those where the dd and mm are both less than the number 12. If the date has 13 or higher in the first 2 digits (e.g. 13/02/2011), then of course MS Word *has* to interpret it as 13th February.
However, if there's a date of 02/05/2011, then instead of determining 2nd May 2011, it is concluding that it's 5th February 2011 instead. It is doing that reasoning on a record-by-record basis and not continuing the formatting based on what it has processed previously.
If you look at the attached spreadsheet, you can see the last two columns are ones I've put in. They show what I would want to show in the mail merge, compared to what it does show.
Hope the above makes sense and apologies for giving you a false impression of the problem.
In the end, to get around the problem, within Excel, in my Date_Ending column I, changed its formula...
From : =IF(D2="","",IF(AND(E2<>"",E2<D2),E2,""))
To : =IF(D2="","",IF(AND(E2<>"",E2<D2),TEXT(E2,"dd MMMM yyyy"),""))
That results of that formula then correctly got passed into the mail merge. However, I'd be curious if I could get around the problem within MS Word.
Thanks if you can help.
|