Solved

Date problem in Word mail merge from Excel

Posted on 2007-11-20
15
563 Views
Last Modified: 2010-04-21
My colleague has created an Excel workbook containing customer information with a start- and end date for contracts.
These columns are formatted as date cells in the Dutch format dd-MM-yyyy.
She's now trying to create a Word mail merge letter with these fields. The start date shows up fine, but the end date is in a US format (MM-dd-yyyy). We tried to alter the merge field with the switch @\ "dd-MM-yyyy", but it doesn't work.

The only end dates that are correctly displayed are the ones after the 12th day of the month (e.g. 20-11-2007), so it looks like Word is applying the correct date format, but identifies the dates from the Excel workbook incorrectly.
0
Comment
Question by:RubenvdLinden
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 2
15 Comments
 
LVL 59

Accepted Solution

by:
Saurabh Singh Teotia earned 125 total points
ID: 20319509
Use this formula...it will give u date in ur desired manner...

=TEXT(H2,"mm-dd-yyyy")

H2 is the cell reference which has date entered in US format...

0
 
LVL 76

Assisted Solution

by:GrahamSkan
GrahamSkan earned 125 total points
ID: 20319659
Most Office date interpretation will try to make a date legal if swapping the month and day will do that, so you could be right about the American dates.

Which version of Office is being used and do know what the connection method is (e.g. OLE, DDE)?

It might help to look at the values in terms of different data types, in the column of dates in the Excel Workbook, so here is a macro to do that

It will print to the immediate window and has constants for the column number(c), start row(r1) and end row(r2). You will need to set these.


Sub ShowValues()
    Dim r As Integer
    Const c = 2
    Const r1 = 2
    Const r2 = 10
 
    Debug.Print "Column: " & c, "as is", "double", "date"
    For r = r1 To r2
        Debug.Print "Row: " & r, Cells(r, c),
        On Error Resume Next
            Debug.Print CDbl(Cells(r, c)),
        If Err = 13 Then
            Debug.Print "Text",
        End If
        On Error GoTo 0
        If IsDate(Cells(r, c)) Then
            Debug.Print CDate(Cells(r, c))
        Else
            Debug.Print "No date"
        End If
    Next r
End Sub

Open in new window

0
 
LVL 12

Author Comment

by:RubenvdLinden
ID: 20319731
saurabh726,
We don't want the US date format. All dates in the Excel sheet are in the Dutch date format (dd-mm-yyyy).
The start date is correctly printed by Word (dd-mm-yyyy), the end date is printed as a US date (mm-dd-yyyy).

Grahamskan,
The computer of my colleague is running Office 2003 standard edition (Dutch) with servicepack 2.
Is there anyway we can tell which connection method is being used?

My colleague is currently in a meeting, but I will try your macro as soon as she's back.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 20319782
Yeah convert the dates...by the formula that i told u..and its gonna print in that format only....
0
 
LVL 12

Author Comment

by:RubenvdLinden
ID: 20319884
I'm sure the text conversion will work, but I want to find out why the program is behaving this way ... the other date column DOES work, this one doesn't ...
0
 
LVL 12

Author Comment

by:RubenvdLinden
ID: 20319897
Grahamskan,

Do you need the exact output of the macro?
As far as I can tell all dates are correct, except for the rows where the end date contains the word 'unknown'.
Double conversion also works fine.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 20320633
If no special steps have been taken, the connection is probably OLE. You have to set the option:
Tools|Options|General|Check Conversion at Open. The user is then asked when assigning the datasource.

In older versions the default was  DDE. In DDE, the format as displayed in Excel is copied. On OLE, the underlying value is used. Either way the date format switch *should* correct it.

I had hoped that the output would tell you something, but we would be interested in seeing a enough of it to include some cells that work OK and others that don't. Just paste in here.
0
 
LVL 12

Author Comment

by:RubenvdLinden
ID: 20320725
Graham,

Row: 391      07-09-2012     41159        07-09-2012
Row: 392      16-10-2017     43024        16-10-2017
Row: 393                     0            No date
Row: 394                     0            No date
Row: 395      onbep.        Text          No date
Row: 396      10-08-2017     42957        10-08-2017
Row: 397      03-10-2007     39358        03-10-2007
Row: 398                     0            No date


Row 391 fails (displays as 09-07-2012), row 392 is ok (16 cannot be converted as a month).
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 20320972
Presumably rows 391, 396 and 397 show the wrong dates in the merge.

I notice that it is one user. Can the problem be reproduced on other machines?

If so, or you aren't sure, could you make sure that the Excel file has no confidential data, zip it and upload it here for us to look at?

www.ee-stuff.com

You use the same ID and password as this site. You have to identify the question with its numeric part or the URL. If the upload is successful, you will see the URL to the file. We would be grateful if you paste that back into this question. Thanks
0
 
LVL 12

Author Comment

by:RubenvdLinden
ID: 20326582
Graham,

I can try the mail merge on another machine tomorrow (running Office 2003 with servicepack 3 instead of servicepack 2). I'm not sure if I'm allowed to upload this file to EE-stuff, so I'll have to ask that tomorrow.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 20326980
OK. You may be able to edit confidential data out. We probably don't need the full file, anyway. Just enough so we can actually reproduce the problem.
I say "we". I'm not sure how many others will try, but I know that I will.
0
 
LVL 12

Author Comment

by:RubenvdLinden
ID: 20357530
My colleague has added another column in Excel and copied the values of the 'broken' column to this new column.
Now it works.

I don't get it; the new column is not a date column, but a 'standard' column so that's a difference. However, if I change the old column to a 'standard' column, the dates still show up corrupted in Word.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 20363946
I know that Word documents can get corrupted. Perhaps spreadsheets can, too. We can still look at the file if you post it to ee-stuff.
0
 
LVL 12

Author Comment

by:RubenvdLinden
ID: 20364043
I'm very sorry for the inconvenience, but I'm not allowed to post this file.

One other thing my colleague found out is that if she adds the date switch to the mergefield again, the date no longer works (so I guess it's probably a text field now).
0
 
LVL 12

Author Closing Comment

by:RubenvdLinden
ID: 31410097
saurabh726, your solution helped us to get the correct information in the document. I have awarded you a B because I think your comment is more a workaround for the problem than an actual solution. Nevertheless, I'm very happy that the document is working again.

GrahamSkan, I'm very sorry I was not allowed to post the file. You did some great work for me troubleshooting this file and I think you might have even solved it if you had the file. To show you my gratitude, I reward you half the points.

Thank you both!
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
In a previous video Micro Tutorial here at Experts Exchange (http://www.experts-exchange.com/videos/1358/How-to-get-a-free-trial-of-Office-365-with-the-Office-2016-desktop-applications.html), I explained how to get a free, one-month trial of Office …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question