Solved

Date problem in Word mail merge from Excel

Posted on 2007-11-20
15
549 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
  • 8
  • 5
  • 2
15 Comments
 
LVL 59

Accepted Solution

by:
Saurabh Singh Teotia earned 125 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 12

Author Comment

by:RubenvdLinden
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction This tutorial provides instructions on how to properly format your Word document using the inbuilt tools provided. The benefits of using these tools means your documents are more accessible and easily portable to other applications an…
Microsoft Word is a program we have all encountered at some point, but very few of us have dug deep into its full scope of features, let alone customized it to suit our needs. Luckily making the ribbon (aka toolbar, first introduced in Word 2007) wo…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now