Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Date problem in Word mail merge from Excel

Posted on 2007-11-20
Medium Priority
606 Views
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
Question by:RubenvdLinden
• 8
• 5
• 2

LVL 59

Accepted Solution

Saurabh Singh Teotia earned 375 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 77

Assisted Solution

GrahamSkan earned 375 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
0

LVL 12

Author Comment

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

LVL 59

Expert Comment

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

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

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 77

Expert Comment

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

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 77

Expert Comment

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

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 77

Expert Comment

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

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 77

Expert Comment

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

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

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

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to usâ€¦
###### Suggested Courses
Course of the Month12 days, 3 hours left to enroll