mcompton69
asked on
Office XP Mail Merge from Excel - Date picture changes to US format
I am sure this is a well known problem and the question has been posted before.
We have Office XP and when mail merging data from an Excel spreadsheet into a Word document, one of the fields which contains a date of birth is being displayed as the mail merge result in the US format MM/DD/YYYY. In Excel it is set correctly to the UK format DD/MM/YYYY. I have tried changing settings in both the PCs region settings, removed the US language etc. and i have tried various different formats for the column in question in Excel but we cannot get the date to display in the UK format in the mail merge.
PLEASE can anyone help!!!!
Thanks.
Mike
We have Office XP and when mail merging data from an Excel spreadsheet into a Word document, one of the fields which contains a date of birth is being displayed as the mail merge result in the US format MM/DD/YYYY. In Excel it is set correctly to the UK format DD/MM/YYYY. I have tried changing settings in both the PCs region settings, removed the US language etc. and i have tried various different formats for the column in question in Excel but we cannot get the date to display in the UK format in the mail merge.
PLEASE can anyone help!!!!
Thanks.
Mike
Matt's way is the easy way!!! If your interested in the hard way, you can actually modify the mail merge field in word so that it actually displays the date correctly. I looked into this for a co-worker back a few weeks ago.
1. Right Click on the field and select edit field.
2. Press the field codes button.
3. In the field codes textbox, type this after the text that is there.
,/@ "MMM, DD, YYYY"
or whatever format you want.
4. Click on the OK Button.
You should be good to go.
PS-This much more of a pain then the text() in Excel.
HTH
Cal
1. Right Click on the field and select edit field.
2. Press the field codes button.
3. In the field codes textbox, type this after the text that is there.
,/@ "MMM, DD, YYYY"
or whatever format you want.
4. Click on the OK Button.
You should be good to go.
PS-This much more of a pain then the text() in Excel.
HTH
Cal
ASKER
I would prefer to go with your line of thinking Cal, but your answer does not work.
I have tried typing exactly what you have written but it does not accept the switches - it just removes them when you OK the Edit Field box, or encloses the entire line as the field name:
This is the orignal field code:
MERGEFIELD Date_Of_Birth
i amend the code as you suggested
MERGEFIELD Date_Of_Birth ,/@ "MMM, DD, YYYY"
and when i click OK the code is changed to
MERGEFIELD "Date_Of_Birth ,/@ MMM, DD, YYYY"
If you look at the standard Date field though, it instructs to use the field as such: DATE [\@ "Date-Time Picture"] [Switches] which is different to what you have recommended - such as a back slash rather than a forward slash - where does the comma come from?
I have also tried entering the field as below, but Word just removes all the switches entirely - it is EXTREMELY FRUSTRATING!
MERGEFIELD Date_Of_Birth \@ "dd/mm/yyyy"
Please advise!!!!
Thanks for your help Cal
I have tried typing exactly what you have written but it does not accept the switches - it just removes them when you OK the Edit Field box, or encloses the entire line as the field name:
This is the orignal field code:
MERGEFIELD Date_Of_Birth
i amend the code as you suggested
MERGEFIELD Date_Of_Birth ,/@ "MMM, DD, YYYY"
and when i click OK the code is changed to
MERGEFIELD "Date_Of_Birth ,/@ MMM, DD, YYYY"
If you look at the standard Date field though, it instructs to use the field as such: DATE [\@ "Date-Time Picture"] [Switches] which is different to what you have recommended - such as a back slash rather than a forward slash - where does the comma come from?
I have also tried entering the field as below, but Word just removes all the switches entirely - it is EXTREMELY FRUSTRATING!
MERGEFIELD Date_Of_Birth \@ "dd/mm/yyyy"
Please advise!!!!
Thanks for your help Cal
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Cal, this is interesting - the field is apparently like this
{ MERGEFIELD Date_Of_Birth \@ "dd/mm/yyyy" } [This is on my machine which i have recreated the problem using the same xls and doc]
{ MERGEFIELD Date_Of_Birth ,\@ "dd/mm/yyyy" } [when i checked on my colleagues machine in question - it has a comma in?]
but it definately displays the date as mm/dd/yyyy
any ideas? This is clearly a bug in either Excel or Word
{ MERGEFIELD Date_Of_Birth \@ "dd/mm/yyyy" } [This is on my machine which i have recreated the problem using the same xls and doc]
{ MERGEFIELD Date_Of_Birth ,\@ "dd/mm/yyyy" } [when i checked on my colleagues machine in question - it has a comma in?]
but it definately displays the date as mm/dd/yyyy
any ideas? This is clearly a bug in either Excel or Word
Yeah,
I had trouble getting the formatting to work as well. That's why I know a couple of different ways to modify it. The online help is pretty useless as well. I would agree that it's word that is buggy. Otherwise it must be me.:-) My only suggestion would be to delete the field entirely and add it in again. It seemed to me that erasing the codes didn't work. So delete the field, add it again, and then do the F9 process and see what happens.
I was able to get this process to work on a merge field from Excel.
Cal
I had trouble getting the formatting to work as well. That's why I know a couple of different ways to modify it. The online help is pretty useless as well. I would agree that it's word that is buggy. Otherwise it must be me.:-) My only suggestion would be to delete the field entirely and add it in again. It seemed to me that erasing the codes didn't work. So delete the field, add it again, and then do the F9 process and see what happens.
I was able to get this process to work on a merge field from Excel.
Cal
ASKER
Thanks Cal - yes your suggestion of removing and re-adding the field works on my machine, but the user has only SP2 (i have SP3) - i have installed the patch and will have another look tomorrow to see if that solves it. Will keep you posted!
Thanks, Mike
Thanks, Mike
ASKER
Hi Cal
I have had some progress but i have now up against a brick wall again:
In order to specify the pitcture for a date, you must be patched to SP3 we have found out, so i have patched the relevent machines.
BUT - one of the columns in the merge contains both dates and text. So if we were to specify the picture, in would need two parameters, like "DD/MM/YYYY" OR [Text].
But if I use \@ for the switch for specifying the date, what would i use to instruct to use a text column?
Thanks again you have been very helpful so far.
Mike
I have had some progress but i have now up against a brick wall again:
In order to specify the pitcture for a date, you must be patched to SP3 we have found out, so i have patched the relevent machines.
BUT - one of the columns in the merge contains both dates and text. So if we were to specify the picture, in would need two parameters, like "DD/MM/YYYY" OR [Text].
But if I use \@ for the switch for specifying the date, what would i use to instruct to use a text column?
Thanks again you have been very helpful so far.
Mike
Mike,
So the original Excel data has both the date and text in the same column. The only way to resolve that is in the original excel data. Is the format of the field consistant?(ie. Date is always the first 10 Characters, or Last 10 Characters) Can you post a sample of one of the values? You will need to make an extra column that will be the datevalue of the particular column.
HTH
Cal
So the original Excel data has both the date and text in the same column. The only way to resolve that is in the original excel data. Is the format of the field consistant?(ie. Date is always the first 10 Characters, or Last 10 Characters) Can you post a sample of one of the values? You will need to make an extra column that will be the datevalue of the particular column.
HTH
Cal
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.
I will leave the following recommendation for this question in the Cleanup topic area:
Accept: Cbrine {http:#15061194}
Any objections should be posted here in the next 4 days. After that time, the question will be closed.
roos01
EE Cleanup Volunteer
I will leave the following recommendation for this question in the Cleanup topic area:
Accept: Cbrine {http:#15061194}
Any objections should be posted here in the next 4 days. After that time, the question will be closed.
roos01
EE Cleanup Volunteer
Try creating a new column in there, and have the dates as text, using the TEXT function.
If column A has your dates currently, you could put this in another column and fill down through your data
=TEXT(A2,"dd/mm/yyyy")
Then have the header of that column be 'UK Date' or something, should take care of your issue. Not sure why it isn't working correctly, if your regional settings are correct, but this should be a good workaround for you.
Matt