Office 2003 Mailmerge Problem - Dates!

[elsiegee40]

We have a mailmerge into Word using Excel for the source data.

 

The dates stored in the spreadsheet are in UK format DD/MM/YYYY

 

After the mailmerge, they appear in the word document in US format MM/DD/YYYY. Everything I can see is in UK format - I've checked the language defaults, etc... Has anyone any idea what I've missed please?

[powdarrmonkey]

Check the windows regional properties, often they govern what happens to dates between applications.

[elsiegee40]

Unfortunatelatey Windows Regional Settings are fine

[Psymon]

Maybe its worth saving it as a text field? Then it will copy number / letter word for word instead of trying to be smart and recognise a date.

[srochford]

In the field properties, add \@ "d-MMM-yy" to get 15-Jul-08 etc

 

Your field will look something like this:

 

{datefield \@ "d-MMM-yy"}

[elsiegee40]

Trouble is that this data is in DD/MM/YYYY format in Excel to be compatible with an Outlook Database - so converting to text doesn't help.

[CESIL]

If the language is set to US for this document then Word will switch the date format to match...

[elsiegee40]

If the language is set to US for this document then Word will switch the date format to match...

 

Nice try

 

Language is English (UK)

 

Is someone else able to give this a try with Office 2003 in their setup, please?

[witch]

Word Mail Merge FAQ

 

there might be something here to help!

[steve]

Steve Rochford and witch have shown the way:

 

Right click on the merge field that related to the date.

Select Toggle field codes

You'll be shown something like:

 

{ MERGEFIELD "Date" }

 

Change this to

 

{ MERGEFIELD "Date" \@ "dd/MM/yyyy" }

 

where "dd/MM/yyyy" is the date format you want

 

 

Right click on the merge field again and select Toggle field codes to change back.

 

Check the date is in the right format with "View Merged Data" icon on toolbar.

[CESIL]

Are the dates in Excel actually entered in UK format or are they just just formatted that way?

 

If I could play with a sample of the data then I may get a better idea.

[CESIL]

As already mentioned I can now see that it appears that Excel stores dates as a serial value and then displays it in whatever format you request (the system regional format seems to be the default).

 

As it says in the FAQ that Witch posted, the format from Excel is lost when you merge into Word so the field codes already suggested are needed.

 

The funny thing is I have a vague memory of having this problem years ago but it didn't ring any bells until now...must be my age...

[jcollings]

Steve Rochford and witch have shown the way:

 

Right click on the merge field that related to the date.

Select Toggle field codes

You'll be shown something like:

 

{ MERGEFIELD "Date" }

 

Change this to

 

{ MERGEFIELD "Date" \@ "dd/MM/yyyy" }

 

where "dd/MM/yyyy" is the date format you want

 

 

Right click on the merge field again and select Toggle field codes to change back.

 

Check the date is in the right format with "View Merged Data" icon on toolbar.

 

I agree with you folks - it's a well known issue and I have fixed mine using the above method.

[elsiegee40]

Thanks guys. I will give this atry in the morning

[elsiegee40]

Just wanted to say THANK YOU all... the toggle worked a treat. saved my bacon on the last day of term! :)