Beyond frustrated with mailmerge date format

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Suzanne - 07 Nov 2007 16:50 GMT

Over an hour spent on this stupid...

I've changed all my Excel date formats to a specified "Date" format:
"dd-mmm-yyyy" (vice "Custom: yyyyMMMdd") (didn't help)

I've opened my Excel spreadsheet as the data source (not working)

I've opened using "Connect to new data source.odc" as the data source (not
working)

I re-opened (refreshed) the data source after changing the Excel spreadsheet
(not working)

I've spent considerable time looking at old posts (not helping as of yet)

I'm testing dates on a plain page -- the only thing on the page are the
following mergefield codes -- which are consistently resulting as "39375"

{MERGEFIELD "STARTDATE"}
{MERGEFIELD "STARTDATE" \@ "dd-mmm-yyyy"}

What do I need to do to get my date to show up as 20-Oct-2007 (or better
yet, "20071020")?

BTW, telling me to update a service pack won't do me any good -- I'm on a
system which, for the most part, is locked down by administrators (I assume
they have already installed all the service packs anyway)

Reply to this Message

Peter Jamieson - 07 Nov 2007 17:04 GMT

> Over an hour spent on this stupid...

Yes, it's a frustrating problem.

Which version of Word/Excel? Was the sheet created in this version of Excel?

Is this just one spreadsheet?

Are all the items in the offending column dates, or are some numeric?

If you want, despam my e-mail (take the KillmapS out of
pjj@KillmapSpjjnet.demon.co.uk), send me your Excel workbook and I will try
to have a look: however, I am not around for much longer right nowand will
be out of the picture later and from tomorrow onwards

Signature

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

> Over an hour spent on this stupid...
>
[quoted text clipped - 25 lines]
> assume
> they have already installed all the service packs anyway)

Reply to this Message

Suzanne - 07 Nov 2007 17:46 GMT

Thanks Peter.

I put the spreadsheet ("IHS V2".xls) on my Office Live site:

http://suzleigh.com/MERGE.aspx

I'm running Office 2003.

BTW: This is the actual Excel data that I'm linking to the form you helped
me with last month -- which is working beautifully (except for the date).

I know that there are still some kinks in the spreadsheet (I can't get the
coding right on the drop-down validations), but am running out of time to
mess with it.

Thanks again -- Suz

> > Over an hour spent on this stupid...
>
[quoted text clipped - 40 lines]
> > assume
> > they have already installed all the service packs anyway)

Reply to this Message

Suzanne - 07 Nov 2007 18:08 GMT

Peter! Never mind... I re-saved the Excel workbook as "V3" and for some
reason, the dates are now appearing as they should be. I have no idea why.

I am, of course, still open to any suggestions or changes to the Word and/or
Excel documents (as you have the time/desire).

Suz

> > Over an hour spent on this stupid...
>
[quoted text clipped - 40 lines]
> > assume
> > they have already installed all the service packs anyway)

Reply to this Message

Peter Jamieson - 07 Nov 2007 18:22 GMT

I've had a quick look, and I think the problem is caused by the fact that
some of the fields are only present in the first data row. In this case, the
Excel driver/provider may get a bit confused as to the data type. If you
duplicate that data for the other rows instead of leaving it blank (if
that's an option) I suspect things will go much better.

(FWIW What happens here is that when I first preview the data, I see a
5-digit number instead of the date, as you do, but after that things seem
OK.)

Signature

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

> Peter! Never mind... I re-saved the Excel workbook as "V3" and for some
> reason, the dates are now appearing as they should be. I have no idea
[quoted text clipped - 58 lines]
>> > assume
>> > they have already installed all the service packs anyway)

Reply to this Message

Suzanne - 07 Nov 2007 19:38 GMT

Thanks Peter... I had already copied the format down to row 250 (I'm still
trying to work out a way to programatically have Excel copy down to the next
row). I'm thinking the problem was the date format was originally "Custom"
-- and when Word encountered it, it didn't know what to do with it.

Regardless, it seems to be working fine now.

Thanks again!

> I've had a quick look, and I think the problem is caused by the fact that
> some of the fields are only present in the first data row. In this case, the
[quoted text clipped - 68 lines]
> >> > assume
> >> > they have already installed all the service packs anyway)

Reply to this Message