Very strange Excel to Word date conversion

[Suzanne]

I have an Excel spreadsheet with three columns of dates. All three columns
are formatted in Excel exactly the same way (10-Feb-2008)

The first and third column are showing up in the Word merge document AND in
the Mail Merge Recipients window as a serial date.

The second column is showing up in the Recipients window as a date, but not
in the right format (2/10/2008).

I set a switch code in the date fields to force the date format I need, but
(obviously) am having no luck. Help????

This one is showing up as "39488"
{MERGEFIELD "PROCESS_START_DATE" \@ "dd MMM yyyy" }

This one is showing up as "2/10/2008"
{MERGEFIELD "ACTUAL_SURVEY_DATE" \@ "dd MMM yyyy" }

 

[Rich/rerat]

Suzanne,
Try using the following switch

\@ "dd'-'MMM'-'yyyy"

Make sure you use single quotes (') around each dash (-).

--
Add MS to your News Reader: news://msnews.microsoft.com
Rich/rerat
(RRR News) <message rule>
<<Previous Text Snipped to Save Bandwidth When Appropriate>>


I have an Excel spreadsheet with three columns of dates. All three columns
are formatted in Excel exactly the same way (10-Feb-2008)

The first and third column are showing up in the Word merge document AND in
the Mail Merge Recipients window as a serial date.

The second column is showing up in the Recipients window as a date, but not
in the right format (2/10/2008).

I set a switch code in the date fields to force the date format I need, but
(obviously) am having no luck. Help????

This one is showing up as "39488"
{MERGEFIELD "PROCESS_START_DATE" \@ "dd MMM yyyy" }

This one is showing up as "2/10/2008"
{MERGEFIELD "ACTUAL_SURVEY_DATE" \@ "dd MMM yyyy" }

 

[Peter Jamieson]

See http://tips.pjmsn.me.uk/t0003.htm and if possible, try the DDE option
described there.

 

[Suzanne]

(BIG SIGH...)

The alternate switch didn't work (that was the first thing I tried).

Peter... I am still working on that monster Excel ==> Word merge document
that you were apparently the only one on the planet who knew it could be
worked out (again THANK YOU!!!)

The DDE:
1) It will only get data from the first worksheet (okay... I setup a
'dummy' worksheet in this workbook that now has 16 worksheets, which are
ordered specific to the level/type of data required for our reports)

2) Word stops looking for data when it encounters an empty space; I have a
column about 2/3 of the way in for comments/notes that will probably rarely
get used, but must be there.

So, I'm still stuck.

 

[Peter Jamieson]

Using ODBC or OLE DB, there is a problem if the data types in a column are
mixed (this is described in that t0003.htm page I mentioned).

If you have control over the content of the Excel sheet, can I suggest that
you try the Data|Text to columns approach for the offending date columns (or
all of them), or at the very least, /type/the very first date in each date
column using some non-numeric text (e.g. 14 Feb 2008). I am not sure that
will actually help in this case but right now I'm a bit strapped for time,
and it might just be enough to sort this out. Otherwise, let me know as I
have made a bit of progress on that t0003.htm article since I last updated
it.

 

[Suzanne]

It doesn't seem the Word or Excel MVP's can figure this one out. It appears
its just another goofy thing Word does.

The work around was to change the Excel date columns to 'Custom: ##-##-####'
(to get 02-20-2008). I obviously am not querying off any of the date fields,
otherwise this might still be a problem.

Thanks for your help (esp Peter). I think I'm finally finished with the
Excel workbook and Word merge forms.

Suz

 

[Peter Jamieson]

I think I'm finally finished with the

Excel workbook and Word merge forms.

That must be a relief!