Merge Date Error with change in calendar year

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Frank Santoro - 04 Jan 2006 17:11 GMT

I have a word 2003 merge document that pulls data from an excel 2003
sheet. The specific field I am pulling from (AFRMonth) has the date
12/01/2005. In word, I have the field formatted as follows:

{MERGEFIELD "AFRMonth" \@ "MMMM yyyy"}

so that it should show as "December 2005" in the document. However,
the formatted field is showing as "December 2006" instead. If I change
the system date to 2005, the field shows properly as "December 2005".
If I remove the formatting switches, the field shows "Dec-05"
regardless of the system date.

Any suggestions?

Reply to this Message

Graham Mayor - 06 Jan 2006 12:19 GMT

I can't reproduce this error using the information supplied.

In a US based system, if the Excel field contains 12/01/2005 then the merge
without the switch should show the same (or depending on how you connect to
the data, it could show
12/01/2005 00:00:00. )

With the switch, it would show
December 2005.

How are you connecting the merge document to Excel?

How is the field formatted in Excel?

Is the field a derived field in Excel, if so, what's the calculation?

It sounds as though the Excel date is derived from some calculation which
uses the system date.

Signature

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

> I have a word 2003 merge document that pulls data from an excel 2003
> sheet. The specific field I am pulling from (AFRMonth) has the date
[quoted text clipped - 9 lines]
>
> Any suggestions?

Reply to this Message

Frank Santoro - 09 Jan 2006 22:57 GMT

Well, I played around some more, and the error seems to reproduce only
when certain date formats are used in Excel. I've not done an
exhaustive test but under the conditions I posted in my first message:

Word shows "December 2006" if
- system date is set to 2006
- date format in excel is "mmm-yy" or "mmmm-yy" (the excel format
throws this [$-409] bit in front of some of the date formats under the
custom format list, including these two formats, but this seems to be
only a coincidence

I changed the date format in the spreadsheet to m/d/yyyy and Word shows
"December 2005", as it should.

In answer to some of your other questions:
- The connection is being made through the word merge SQL command
SELECT * FROM...
- The field is not derived in Excel; it is entered just as a date

I'd be curious whether or not anyone can reproduce the error given this
information. If not, I'd be happy to upload the word and excel files
somewhere for folks to test.

Frank

Reply to this Message

Cindy M -WordMVP- - 10 Jan 2006 09:56 GMT

Hi Frank,

I think what's happening here is that any combination of just month and
year, with a year number of 12 or less, is ambiguous. Try typing Dec-05
into an EXCEL cell, with this number format. I get Dec-06, the same thing
you're describing with Word.

This is one reason why Graham asked which connection protocol you're
using for the mail merge. An SQL string is not a connection, just a
filter :-) Possible connection protocols would be: OLE DB, ODBC, DDE or
the spreadsheet converter.

> Well, I played around some more, and the error seems to reproduce only
> when certain date formats are used in Excel. I've not done an
[quoted text clipped - 18 lines]
> information. If not, I'd be happy to upload the word and excel files
> somewhere for folks to test.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail :-)

Reply to this Message

Frank Santoro - 10 Jan 2006 17:05 GMT

Well, the data as typed in excel was "12/01/2005". When I had the
excel cell formatted as mmm-yy (i.e., displaying Dec-05), I got the
error in Word (i.e., the merge field with switches displayed "December
2006"). When I changed the format in the excel cell to mm/dd/yyyy,
there merged data in Word displayed "December 2005".

Now as to connection protocol, I have no idea what you mean. How do I
find out? All I know is I am using the word merge function.

Reply to this Message

Cindy M -WordMVP- - 11 Jan 2006 10:22 GMT

Hi Frank,

> Well, the data as typed in excel was "12/01/2005". When I had the
> excel cell formatted as mmm-yy (i.e., displaying Dec-05), I got the
[quoted text clipped - 4 lines]
> Now as to connection protocol, I have no idea what you mean. How do I
> find out? All I know is I am using the word merge function.

OK, if you didn't choose anything, and this is Word 2003, then the
connection method is OLE DB (unless this document + the connection to
the data source originated in an earlier version of Word?). Can you see
this if you start with a new document and
1. Tools/Options/General, activate "Confirm conversions on open"

2. Connect to the worksheet.

3. You'll get a dialog box with a list of connection methods. Which one
is selected by default? Go through and test with each, keeping a record
of the result you get in each case. (For ODBC you'll probably need to
click "Options" and activate "System tables" in order to get a list of
worksheet names) What kinds of results do you get?

Do you see this only with Dec. 1? Or does it also happen with any other
day (up to and including 12)?

You see, Excel saves date information as long integers (number of days
since Dec. 31, 1901 or something like that). Most Office applications
know how to take these integers and display them as dates, using the
formatting pictures. But the applications can get confused if you start
with an ambiguous date (12-05, both numbers could be day, month or
year). In such a case, they'll tend to say OK, this is the day and
month, I'll just hang the current year on it. This appears to be what
you're seeing, but I have to know exactly what circumstances in order to
test on my installation.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question
or reply in the newsgroup and not by e-mail :-)

Reply to this Message