Very strange Excel to Word date conversion

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Suzanne - 11 Feb 2008 20:19 GMT

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" }

Reply to this Message

Rich/rerat - 11 Feb 2008 21:17 GMT

Suzanne,
Try using the following switch

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

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

Signature

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

Reply to this Message

Peter Jamieson - 11 Feb 2008 23:20 GMT

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

Signature

Peter Jamieson
http://tips.pjmsn.me.uk

>I have an Excel spreadsheet with three columns of dates. All three columns
> are formatted in Excel exactly the same way (10-Feb-2008)
[quoted text clipped - 16 lines]
> This one is showing up as "2/10/2008"
> {MERGEFIELD "ACTUAL_SURVEY_DATE" \@ "dd MMM yyyy" }

Reply to this Message

Suzanne - 14 Feb 2008 12:20 GMT

(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.

> See http://tips.pjmsn.me.uk/t0003.htm and if possible, try the DDE option
> described there.
[quoted text clipped - 19 lines]
> > This one is showing up as "2/10/2008"
> > {MERGEFIELD "ACTUAL_SURVEY_DATE" \@ "dd MMM yyyy" }

Reply to this Message

Peter Jamieson - 14 Feb 2008 14:02 GMT

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.

Signature

Peter Jamieson
http://tips.pjmsn.me.uk

> (BIG SIGH...)
>
[quoted text clipped - 42 lines]
>> > This one is showing up as "2/10/2008"
>> > {MERGEFIELD "ACTUAL_SURVEY_DATE" \@ "dd MMM yyyy" }

Reply to this Message

Suzanne - 20 Feb 2008 19:53 GMT

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

> 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).
[quoted text clipped - 54 lines]
> >> > This one is showing up as "2/10/2008"
> >> > {MERGEFIELD "ACTUAL_SURVEY_DATE" \@ "dd MMM yyyy" }

Reply to this Message

Peter Jamieson - 21 Feb 2008 08:48 GMT

> I think I'm finally finished with the
> Excel workbook and Word merge forms.

That must be a relief!

Signature

Peter Jamieson
http://tips.pjmsn.me.uk

> It doesn't seem the Word or Excel MVP's can figure this one out. It
> appears
[quoted text clipped - 82 lines]
>> >> > This one is showing up as "2/10/2008"
>> >> > {MERGEFIELD "ACTUAL_SURVEY_DATE" \@ "dd MMM yyyy" }

Reply to this Message