Solved Excel to Word merge problems with date fields

Microsoft Word 2002 (full product)
February 18, 2010 at 12:48:52
Specs: Windows XP
I'm using a excel spreadsheet to merge data into a word document. However within my date columns I can also put 'pending' or 'n/a' So whenever a column contains this the format of the date within the word document is not displayed but is rather put as a calculation.

So for e.g
28/01/2010 is shown as 40206

How can i get round this, if I remove any of the pending or n/a the format is ok. But I do need to display a pending or n/a and or the correct date format.

Even with using the following
@/ "dddd d MMMM yyyy"
It still doesn't help as the column contains a none date formatted input.

Thanks

Ian

Sanlucar rentals


See More: Excel to Word merge problems with date fields

Report •


✔ Best Answer
February 20, 2010 at 11:29:57
Hi,

Hi DerbyDad03, the solution of changing the access to the Excel data from ODBC to DDE is mentioned in several places include MS itself (once someone tells you to look for it :-)

It took me several attempts to find the right sequence of steps, so here is what I did to get this to work using DDE:

The sequence is:
Tools - Options - general Tab - check Confirm Conversion at Open
From the Mailmerge toolbar:
select Main Document Setup and 'Normal Word Document'
select Open Data Source - navigate to Excel file (don't use the 'New Source' option).
- click Open
This opens a 'Confirm Data Source' dialog box
Select the MS Excel Worksheets via DDE (*.xls) option - OK
Select a table from the list of named ranges in the dialog box (Microsoft Excel - Named or cell range:)
OK
From the Mailmerge toolbar click 'Insert Merge Fields' and select the Table name (Database fields)
Click the 'View Merged Data' button
The Merge field will now use the formatted text from Excel - so no need to add formatting to the Word field.

In Tools - Options uncheck the Confirm conversion box.

Regards



#1
February 18, 2010 at 19:50:26
Just an FYI...40206 is not the result of a calculation, but the value that Excel uses to store the date 28/01/2010.

Excel stores all dates and times as numbers, with the integer portion being the data and the decimal portion being the time.

1/1/1900 is day 1 and 40205 days later is 28/01/2010.

I'd like to duplicate your problem to see if I can come up with a solution. How are you doing the merge?


Report •

#2
February 19, 2010 at 00:11:47
I've got the data in an excel spreadsheet a word document with text and using mail merge.

So for instance column Date received has the following data:
28/1/2010
15/2/2010
pending
n/a
18/2/2010
etc


The data in the word doc will be similar to:
{MERGEFIELD "Date_Received" \@" dddd d MMMM yyyy"}

Ian

Sanlucar rentals


Report •

#3
February 19, 2010 at 07:03:32
I was able to duplicate the issue.

As far as I can tell, as soon as the Mail Merge feature sees any text in the list, it ignores the Date format and uses the actual value stored within Excel.

I'm going to play with this a little more, but for now I can offer a possible workaround.

If you place a single quote before your dates in Excel, it will force the entry to be text and Word will show the text as 28/01/2010.


Report •

Related Solutions

#4
February 19, 2010 at 10:34:40
Hi,

I appear to have a solution!

If your dates / comments column is in column A statring at cell A2.
In column B starting at cell B2 enter this formula:
=TEXT(A2,"dd/mmm/yyyy")
Drag this down alongside your existing list.

Make the column B table, the source for your mail merge.

The merged fields in the Word document use the text from column B and in the format used in column B. I did not include a format in the mergefield.

You can now have your Excel worksheet make changes to column A, and column B will follow, but purely as text.

Hope this works.

Regards


Report •

#5
February 19, 2010 at 10:38:08
Just as an FYI... I am persuing this on the Word side with some friends.

Report •

#6
February 19, 2010 at 11:20:19
Thanks for you help.

Humar I've not tested your suggestion as it will cause me more problems as i have the data also sent to another spreadsheet to issue an updated receipt/invoice/bill and i don't want to start re-writing all teh code for the diferent invoices. If worst comes to worst I'll give your idea a try, thanks a lot.

Derbydad - I've tried your suggestion with a single quote before and it works and even shows the result of the date in the full format and not xx/xx/xx. Also the changes don't affect the invoices I produce. As the dates/pending and n/a are taken from a vlookup combo box on another worksheet I'll have to play around to see if I make the changes it's causes any problems with dates I've already put in...!

Thanks a million.

Ian

Sanlucar rentals


Report •

#7
February 19, 2010 at 11:33:37
I'd still like to see a solution on the Word side of the issue.

Even if it doesn't apply to your situation right now, I see more of a chance to need to use the dates as dates in Excel than I do in the Word doc, so I don't really care for either of the "turn them in text" solutions.

I'd much rather find a way to have Word recognize them as dates even when there is text in the column.

I'll dig a little further before putting my shovel away.


Report •

#8
February 20, 2010 at 09:19:51
Here is a response I received in a Word forum. I haven't tried either of these suggestions. If they work for you, please let us know.

*** Begin Included text ***

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/in...

or

http://www.gmayor.com/downloads.htm...

Do read the tutorial's introductory material.

--
Cheers
macropod
[Microsoft MVP - Word]

*** End Included text ***


Report •

#9
February 20, 2010 at 11:29:57
✔ Best Answer
Hi,

Hi DerbyDad03, the solution of changing the access to the Excel data from ODBC to DDE is mentioned in several places include MS itself (once someone tells you to look for it :-)

It took me several attempts to find the right sequence of steps, so here is what I did to get this to work using DDE:

The sequence is:
Tools - Options - general Tab - check Confirm Conversion at Open
From the Mailmerge toolbar:
select Main Document Setup and 'Normal Word Document'
select Open Data Source - navigate to Excel file (don't use the 'New Source' option).
- click Open
This opens a 'Confirm Data Source' dialog box
Select the MS Excel Worksheets via DDE (*.xls) option - OK
Select a table from the list of named ranges in the dialog box (Microsoft Excel - Named or cell range:)
OK
From the Mailmerge toolbar click 'Insert Merge Fields' and select the Table name (Database fields)
Click the 'View Merged Data' button
The Merge field will now use the formatted text from Excel - so no need to add formatting to the Word field.

In Tools - Options uncheck the Confirm conversion box.

Regards


Report •

#10
February 22, 2010 at 05:28:15
Thanks to the both of you. I've downloaded and read through the word document regarding date calculations. I'd also done the DDE change but not as in depth as Humar has explained, so will try that as well.

Thanks to you both, all the best.

Ian

Sanlucar rentals


Report •


Ask Question