Mail Merge from Excel (Office 2003)

Topic

New Reply

I use Word to Mail Merge with information contained within an Excel spreadsheet. Specifically dates. In the Excel spreadsheet they appear in English format. When merged into Word they would appear in the same format (English). About about 6 weeks ago this changed. Now the dates appear in American format in Word. This is not just confined to my home PC but on other systems that I use. All Regional setting have been checked, Server etc – all ok.
I wondered how is this problem to be resolved? (Is it one of those MS-Patches that have had this affect)?

Reply | Quote

Replies

Hi there

Welcome to the Lounge.

I am unsure what version of Word you are using but there are various version problems that could be the cause:

I am going to assume you have set the Windows Date and/Time/Format correctly in the Control Panel as you have stated.

Changing the Language of a Word 97/2000/XP2002 Document

1 Open Word
2 Click on the ‘Tools’ menu then the ‘Language’ option, then ‘Set Language’ option.
3 Locate the ‘English(U.K.)’ option and click on it to select.
4 Click on the ‘Default’ button – see dialog saying ‘Do you want to change the default language to English(U.K.)?’
5 Click ‘Yes’ then click OK.
6 Close Word down (this can be done any time – but if Word crashes, normal.dot will not be saved and your language change will be lost).

If all three settings are English UK – all dates should be in the UK form in Word and documents will be spell checked in UK English.

Excel 97 and Excel 2000

Excel takes its date format from Windows and so, if you have done the above steps and type 1/4/03 it will understand it to mean 1st April 2003….
BUT Excel 97 and Excel 2000 have to use a CUSTOM FORMAT to display the date as a ‘number’ i.e. 01/04/03

So if you want to change the number format of the an Excel 97 or Excel 2000, you have to use the ‘Custom’ method.
1 Select ‘Custom’ type.
2 Type: a form for the date e.g. dd/mm/yyyy – two day digits, two month digits and four year digits.
3 Click OK

Reply | Quote

Thanks for the information.

I have already gone thro you suggested process, plus a few others.

The main point is that all was working fine until about 6 weeks ago – no settings have changed – except for the MS Patches, which made me wonder if this was the root cause.

Reply | Quote

I have had this issue not with dates, but with currency. I found the instructions in Microsoft’s Knowledge Base article 320473 to be extremely helpful to get it all to work properly. I’ve attached a PDF file of the article Give it a try.

Reply | Quote

Thanks – that was extremely useful and solved the problem. Now to investigate why it changed on the server, but more importantly fix the Tools/Options part.

Thanks again

Reply | Quote

You could use a format switch at the end of your merge fields, to tell Word exactly how you want the data formatted. Something like
@ “dddd, MMMM d, yyyy” }
at the end of your field should format the result as a valid date – change the dd, mm, yy characters for the layout you actually want.

StuartR

Reply | Quote

Thanks for the information.

I could use your suggestion, but the users I support would not be able to cope with this.

The main point is that all was working fine until about 6 weeks ago – no settings have changed – except for the MS Patches, which made me wonder if this was the root cause.

Reply | Quote