Mailmerge calculated dates from Excel

[MervynW1954]

If I calculate a date in excel I can format it as dddd d mmmm yyyy (for
example)

(I want to calculate the date, at three weeks before a date entered for
example).

However, if I use this in Word, it displays the date as a number only. By
using the switch I cannot get it to format as I want it (as above). It
displays only as a number. I never had this problem with Office 2000. A
date that I have manually entered in the spreadsheet displays correctly.

Any ideas

 

[Peter Jamieson]

What sort of date does "dddd d mmmm yyyy" actually give you in Excel? How
does that value appear in Word exactly? (Can you give an example?)

Word needs something it recognises as a date (e.g. YYYY-MM-DD format is a
pretty good starting point). Once you have that, you can usually use the \@
format switches within Word fields successfully.

Peter Jamieson

 

[MervynW1954]

The date format displays exactly as I want it in excel, (d mmmm yyyy)

However, in Word it displays as the 'number' only, ie the number of days
from 1 Jan 1900.

BUT I have now found that if I move the column in excel to nearer the
beginning of the spreadsheet (ie, Column K to Column E) it works as it should
with the date switch working (\@"d MMMM yyyy")

Puzzling why it does not work in Column K though.....! (although it displays
all right there)

 

[Peter Jamieson]

Puzzling why it does not work in Column K though.....! (although it

displays
all right there)

I don't know for sure, but in Word 2002 and later, by default Word uses
OLEDB to get the data from the Excel worksheet. It tends to get the
"underlying data", and first of all the relevant OLEDB provider has to work
out what kind of data is in each column. It does not always get it right.
Although it isn't immediately obvious why moving a column would make any
difference, it might be that moving the column changes the data in a way
that makes the provider see it correctly. Who knows?

Word 2000 used DDE by default, and that basically gets the data from the
Excel program which knows how each cell is supposed to be displayed. You can
revert to that method by checking Word Tools|Options|General|Confirm
conversion at open, reconnecting to your data source and selecting DDE when
offered.

Peter Jamieson