Hi there,

I am using Excel (2007) as a data source and picking up a date column which can either be populated or not. The date is an 'end' date to a product. If the cell is populated, that means the product will discontinue to be available from that date. If the cell is empty, the product will be available indefinitely.

The cell is populated by an IF statement in Excel, the result being that the cell can have a date in it or not (blank).

The cell is definitely behaving as a date field. i.e. it is an underlying numerical value, which is formatted as a date.

As stated though, the cell in some circumstances may be blank - since there is no product end date.

It is also formatted, for what it's worth, as a date cell in Excel.

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"}

If I use a non-calculated date field (i.e. a typed in value), the switch behaves as expected, and shows it in the UK format.

The problem is only manifesting itself on that cell which is the consequence of an IF statement.

I'm very familiar with the usual date formatting issues in Word, but I cannot find an answer to this one.

Any takers ?



best regards, borntorun75.

Hi borntorun75,

Care to post a copy of the Excel wb with enough data to demonstrate the issue?

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

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.

Attached Files

	Sample_Document.docx (11.2 KB, 14 views)
	Sample_Data.xlsx (9.7 KB, 18 views)

Hi borntorun75,

I suspect the fault has something to do with the empty cell in F2. As a workaround, you could change the formatting in column F to 'dd-mmm-yyyy'. Alternatively, you might try changing the data connection method to DDE.

Also, FWIW, you could also replace the 'Date_Ending' IF test in your mailmerge main document with a mergefield coded as: {MERGEFIELD Date_Ending \@ "dd MMMM yyyy" \b "Item available until "}

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]