Mailmerge calculated dates from Excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MervynW1954 - 21 Apr 2005 12:53 GMT

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

Reply to this Message

Peter Jamieson - 21 Apr 2005 16:28 GMT

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

> If I calculate a date in excel I can format it as dddd d mmmm yyyy (for
> example)
[quoted text clipped - 8 lines]
>
> Any ideas

Reply to this Message

MervynW1954 - 21 Apr 2005 18:51 GMT

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)

> 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?)
[quoted text clipped - 16 lines]
> >
> > Any ideas

Reply to this Message

Peter Jamieson - 22 Apr 2005 10:58 GMT

> 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

> The date format displays exactly as I want it in excel, (d mmmm yyyy)
>
[quoted text clipped - 34 lines]
>> >
>> > Any ideas

Reply to this Message