merged dates from excel lose format

[Janice Dowdeswell]

HI there all

I have office 2003, win xp. The date format in the Control Panel
Regional and Language options is set correctly (for me) to dd/mm/yy as
is the formatting on the Excel spreadsheet. However when the dates are
merged to Word the format is mm/dd/yy.

How do I preserve the formatting during importing it from Excel to
Word?

or is there a workaround? I tried changing the Excel spreadsheet data
from 'dates' to just text so that it would be imported as plain text
but couldn't find a way to do that either.

thanks for any help
Janice

 

[Peter Jamieson]

Does specifying a date format switch in Word do the trick, e.g. use Alt-F9
to reveal the field codes and change

{ MERGEFIELD mydate }

to

{ MERGEFIELD mydate \@"DD/MM/YY" }

? Be careful, because what you may dfind is that the day/month are the wrong
way around in some dates and not others. If so, this is down to a fault in
the OLEDB provider used to get the data and I don't know what the
recommended fix is for your combination of Word/Windows. If it is still
wrong, another workaround is to check Word Tools|Options|General|"Confirm
conversions at open", reselect your data source, and choose the Excel via
DDE option when offered. You may still need the date format switches in that
case.

 

[Janice Dowdeswell]

thanks Peter - using the mergefield format switch didn't work, it
converted it to dates but not the correct ones... I finally found how
to convert the excel spreadsheet to text by using =Text(a2,dd/mm/yy)
on the whole data table and then copying and pasting the result as
values only - a tedious workaround but it got it done.

Janice