Problems with Mail Merge and Dates

[DerbyDad03]

Greetings!

I know Excel pretty well, but Word is a mystery to me.

I'm trying to bring dates from Excel into a Word doc via Mail Merge.

I have column of dates that might read as follows:

Date Received
1/28/2010
2/15/2010
2/18/2010

This works fine via Mail Merge and the dates show up as dates.

However, if I enter text anywhere in this list, Mail Merge converts
the dates to the serial numbers that Excel uses to store dates. For
example, if I have this:

Date Received
1/28/2010
Pending
2/18/2010

I'll get 40206 for 1/28/2010, Pending for Pending, 40227 for
2/18/2010, etc.

If I remove Pending (or "n/a" or any other text) the dates revert back
to the date format shown in Excel.

Is there a way around this - other than forcing the dates in Excel to
be text, which creates a different set of issues in the spreadsheet?

Thanks!

 

[PJY]

You could use Access for your data source

 

[DerbyDad03]

Thank you for the Access suggestion but the purpose of the spreadsheet
is not just to be the Data Source for the merge. The dates are used
for other purposes in the spreadsheet (i.e. referred to in formulae).

For example, I have a cell that contains:

=MONTH(A1) to return 2 from 2/19/2010

If I force the date to be text in Excel so that it works with the
merge, I would then have to change that formula to be:

=MONTH(DATEVALUE(A1))

I know how to "fix" the problem on the Excel side, but I would prefer
not to have to alter every cell that contains a date and every formula
that refer to those cells because of what appears to be a weird
glitch(?) between Excel and Word.

I don't know enough about Word to know if the problem can be resolved
with some kind of MERGEFIELD code to convert the number (40228) back
to it's equivalent date (2/19/2010).

Just as an FYI...

1/1/1900 is day 1 in Excel. 40227 days later, you come to 2/19/2010.
Excel stores 2/19/2010 internally as 40228 and displays that as a date
based on the formatting in the cell. For some reason, Mail Merge
ignores the date formatting as soon as any text is place in the list
and instead shows Excel's "internal" value.

 

[macropod]

Hi DerbyDad03,

Try changing the data connection method to DDE.

Alternatively, you could use a field code solution based on the example found under the heading 'Importing Date and Time Values From
Excel and Access' in my Word Date Calculation Tutorial, which you can download from:
http://lounge.windowssecrets.com/index.php?showtopic=249902
or
http://www.gmayor.com/downloads.htm#Third_party
Do read the tutorial's introductory material.