Mail merge will not format date field generated by Excel IF statement

[borntorun75]

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.