+ Reply to Thread
Results 1 to 4 of 4

Merge file from Excel date format different

  1. #1
    Forum Contributor
    Join Date
    03-08-2007
    Location
    St. Augustine, Fl
    MS-Off Ver
    2012 and 2011 MAC
    Posts
    337

    Merge file from Excel date format different

    I am trying to merge an Excel file into work using mail merge. I have it set and it works, however the date format does not merge correctly. In the Excel file I have the date format, December 19, 2012, but when I merge it is 12/19/2012. How can I get it to merge using the December 19, 2012?
    Last edited by Wskip49; 12-27-2012 at 06:13 PM. Reason: changed title

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,117

    Re: Merge file

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
    To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    Ben Van Johnson

  3. #3
    Forum Guru macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,466

    Re: Merge file from Excel date format different

    As the mods have rather unhelpfully locked the duplicate thread in the Word forum where this discussion belongs, I'll post my reply here.

    Word has a number of different methods of connecting to mail merge data sources, including DDE and OLE DB. Word 2002 and later use the OLE DB coonection by default, though you can change this (to DDE, for example). To work around a limitation in the OLE DB provider used to get data from Excel etc., when Word is connected to an OLE DB data source, it treats dates as if they are in the US mm/dd/yy format, regardless of the format in Excel, your regional settings etc. Applying a date format switch fixes that - and gives the mailmerge document the ability to format the date independently of whatever format is used in the data source.

    To get the date format you want, you can add a formatting picture switch as follows:
    • select the mergefield;
    • press Shift-F9 to expose the field coding. It should look something like {MERGEFIELD MyDate} where 'MyDate' is your mergefield's name;
    • delete anything appearing after the mergefield's name and add '\@ "MMMM d, yyyy"' to the field, as in {MERGEFIELD MyDate \@ "MMMM d, yyyy"}. With this switch your date will come out as 'December 27, 2010'.
    • position the cursor anywhere in this field and press F9 to update it;
    • run your mailmerge.

    Other possible date formatting switch arangements include:
    . \@ "dddd, d MMMM yyyy";
    . \@ "ddd, d MMMM yyyy";
    . \@ "d MMM yyyy";
    . \@ "dd/MMM/yyyy";
    . \@ "d-MM-yy".
    Note: you can swap the d, M, y expressions around, but you must use uppercase 'M's for months - lowercase 'm's are for minutes.

    One thing you'll notice if you're using ordinary date fields in the mailmerge main document is that the date in the output document will update if you re-open the document at a later date, which can be problematic. There is a simple way to prevent this - embed the Date field in the mailmerge main document in a QUOTE field. To do this, simply select the date field, press Ctrl-F9 to embed it in another field, and type 'QUOTE' inside the left field brace, so that you end up with {QUOTE 'date field'}. With the QUOTE field, the date field will automatically unlink when the mailmerge output document is created.
    Last edited by macropod; 12-27-2012 at 02:42 AM.
    Cheers,
    Paul Edstein
    [Fmr MS MVP - Word]

  4. #4
    Forum Contributor
    Join Date
    03-08-2007
    Location
    St. Augustine, Fl
    MS-Off Ver
    2012 and 2011 MAC
    Posts
    337

    Re: Merge file from Excel date format different

    I would like to thank you so much for your help. You have made my life much easier.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1