Word date from mail merge displayed as number - can anyone help ?

[nottinghamdave]

Hi,

I have created a mail merge from Excel 2003 to Word 2003 and my dates are always displayed wrongly.

I have added a date using the following format :-
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o>{ DATE \* MERGEFORMAT } and the date displays 15/05/2009

I have added a date on the same document using the following format </o>
<o>{ MERGEFIELD "Order_Dt" \@ "ddd MMMM yyyy" } and the date displays 39947.539468

I have been trying everything to get Order_Dt that is merged from Excel to display the correct format and have gone bald pulling my hair out.

I am not technical so will not understand going into programming or complex methods. Is there a simple way to fix this because I can't find the answer anywhere on the web or on any forum anywhere.</o>
<o></o>
<o>Thanks</o>
<o>Dave</o>

 

[nottinghamdave]

Just a quick bump.

Just to add, when I choose the below on Word 2003 :-
Tools > Letters and Mailings > Mail Merge

Then choose "Edit Recipient List" on Step 3 of 6, all the dates are displayed numerically eg 39947.539468 for instance, which is the numerical equivalent of 14/05/2009 (1/1/1900 + 39947.539468 days). I just can't figure it out.

Dave

 

[nottinghamdave]

Just a bump to see if anyone can help.

Thanks
Dave

 

[wsjackman]

it may by a computer issue
search for date format in help
you may have to change a control panel setting

 

[nottinghamdave]

>> wsjackman
Many thanks for your reply. You gave me confidence that I wasn't doing it wrong and going crazy.

I have checked all the settings on Windows and I don't think that is the problem.

Whilst doing this I found the real problem (not the answer yet). I don't think the problem is with Word 2003, it is with Excel 2003. I added a new date column to the mail merge with virgin dates and no pre-formatting. These came through as dates. However the other dates that are coming through from Excel are coming through as numbers eg 39946.868.

I have a feeling that they show as dates on Excel but are actually text fields. I have checked all the fields on Excel and they all are a date format.

So I suppose the problem now is making Excel fields that look like date fields to behave like date fields rather than text fields.

Any help would be appreciated as I am still passing date looking fields through mail merge but are actually text fields or other.

Many thanks again for your input

 

[VoG]

One possibility...

Select the "text dates", Data > Text to Columns, click Next twice and on the third screen tick Date, select DMY and click Finish.

 

[nottinghamdave]

One possibility...

Select the "text dates", Data > Text to Columns, click Next twice and on the third screen tick Date, select DMY and click Finish.

Sorry for the late reply. Many thanks for your suggestion Peter, I tried that but it didn't work either.

I went back to Excel and cleared completely down the cells containing the dates and re-entered them as dates. They then were recognised as dates and went through to Word in the mail merge and the dates were displayed correctly.

Thanks for all your input guys.
Dave

 

[ajminogue]

Did you get the dates to display in a format like this: 9/1/2010, or like this: Septemeber 1, 2010? I'm trying for the later without success.

 

[nottinghamdave]

I got them to display as 9/1/2010. But I could have got them to display as September 1 2010 if I had changed the { MERGEFIELD "Order_Dt" \@ "ddd MMMM yyyy" } date display format.

The problem was, if I remember, that Excel cells were set as text fields somehow at some stage as it wasn't a new spreadsheet and been formatted for a while beforehand. The mail merge worked fine if I was transferring to a completely clear formatted cell (if I remember rightly). The problem was clearing the cells.

In my #5 post above I discovered my problem when I added a new date field to the mail merge and checked the formatting wasn't a number. I then had to mess around for quite a while to work out how to solve it, I think by clearing the formatting somehow in Excel. Ctrl-Edit-Clear All on the cells or something like that. If you add a new date field yourself in the mailmerge and discover the same then you might be closer to finding your answer.

Best of luck.