+ Post New Thread
Results 1 to 15 of 15
How do you do....it? Thread, Office 2003 Mailmerge Problem - Dates! in Technical; We have a mailmerge into Word using Excel for the source data. The dates stored in the spreadsheet are in ...
  1. #1

    elsiegee40's Avatar
    Join Date
    Jan 2007
    Location
    Kent
    Posts
    11,173
    Thank Post
    1,802
    Thanked 2,208 Times in 1,632 Posts
    Rep Power
    777

    Question Office 2003 Mailmerge Problem - Dates!

    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?

  2. #2

    powdarrmonkey's Avatar
    Join Date
    Feb 2008
    Location
    Alcester, Warwickshire
    Posts
    4,859
    Thank Post
    412
    Thanked 777 Times in 650 Posts
    Rep Power
    182
    Check the windows regional properties, often they govern what happens to dates between applications.

  3. Thanks to powdarrmonkey from:

    elsiegee40 (15th July 2008)

  4. #3

    elsiegee40's Avatar
    Join Date
    Jan 2007
    Location
    Kent
    Posts
    11,173
    Thank Post
    1,802
    Thanked 2,208 Times in 1,632 Posts
    Rep Power
    777
    Unfortunatelatey Windows Regional Settings are fine

  5. #4

    Join Date
    Aug 2007
    Location
    Deal, Kent
    Posts
    343
    Thank Post
    12
    Thanked 73 Times in 51 Posts
    Rep Power
    27
    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.

  6. #5

    Join Date
    Aug 2005
    Location
    London
    Posts
    3,156
    Thank Post
    116
    Thanked 529 Times in 452 Posts
    Blog Entries
    2
    Rep Power
    124
    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"}

  7. Thanks to srochford from:

    elsiegee40 (15th July 2008)

  8. #6

    elsiegee40's Avatar
    Join Date
    Jan 2007
    Location
    Kent
    Posts
    11,173
    Thank Post
    1,802
    Thanked 2,208 Times in 1,632 Posts
    Rep Power
    777
    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.

  9. #7

    CESIL's Avatar
    Join Date
    Nov 2006
    Location
    Hampshire
    Posts
    1,404
    Thank Post
    109
    Thanked 267 Times in 198 Posts
    Rep Power
    168
    If the language is set to US for this document then Word will switch the date format to match...

  10. #8

    elsiegee40's Avatar
    Join Date
    Jan 2007
    Location
    Kent
    Posts
    11,173
    Thank Post
    1,802
    Thanked 2,208 Times in 1,632 Posts
    Rep Power
    777
    Quote Originally Posted by cadjs View Post
    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?

  11. #9

    witch's Avatar
    Join Date
    Nov 2005
    Location
    Dorset
    Posts
    11,334
    Thank Post
    1,389
    Thanked 2,396 Times in 1,688 Posts
    Rep Power
    706
    Word Mail Merge FAQ

    there might be something here to help!

  12. Thanks to witch from:

    elsiegee40 (15th July 2008)

  13. #10
    steve's Avatar
    Join Date
    Oct 2005
    Location
    West Yorkshire
    Posts
    1,043
    Thank Post
    22
    Thanked 177 Times in 123 Posts
    Rep Power
    52
    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.

  14. Thanks to steve from:

    elsiegee40 (15th July 2008)

  15. #11

    CESIL's Avatar
    Join Date
    Nov 2006
    Location
    Hampshire
    Posts
    1,404
    Thank Post
    109
    Thanked 267 Times in 198 Posts
    Rep Power
    168
    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.

  16. #12

    CESIL's Avatar
    Join Date
    Nov 2006
    Location
    Hampshire
    Posts
    1,404
    Thank Post
    109
    Thanked 267 Times in 198 Posts
    Rep Power
    168
    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...

  17. #13

    Join Date
    Nov 2006
    Location
    Kendal
    Posts
    1,555
    Thank Post
    112
    Thanked 177 Times in 144 Posts
    Rep Power
    71
    Quote Originally Posted by steve View Post
    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.

  18. #14

    elsiegee40's Avatar
    Join Date
    Jan 2007
    Location
    Kent
    Posts
    11,173
    Thank Post
    1,802
    Thanked 2,208 Times in 1,632 Posts
    Rep Power
    777
    Thanks guys. I will give this atry in the morning

  19. #15

    elsiegee40's Avatar
    Join Date
    Jan 2007
    Location
    Kent
    Posts
    11,173
    Thank Post
    1,802
    Thanked 2,208 Times in 1,632 Posts
    Rep Power
    777
    Just wanted to say THANK YOU all... the toggle worked a treat. saved my bacon on the last day of term!

SHARE:
+ Post New Thread

Similar Threads

  1. Replies: 4
    Last Post: 14th August 2009, 01:58 AM
  2. Office 2007 at home, Office 2003 at school
    By kennysarmy in forum Windows
    Replies: 25
    Last Post: 13th January 2009, 01:31 PM
  3. sims mailmerge help wanted
    By Jamie_a in forum MIS Systems
    Replies: 4
    Last Post: 22nd January 2008, 04:47 PM
  4. Replies: 2
    Last Post: 26th April 2007, 04:07 PM
  5. Office 2003 Proxy Authentication Problem
    By Stefletch in forum Office Software
    Replies: 8
    Last Post: 10th February 2007, 10:00 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •