Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Apr 2016
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mail Merge Date Calculations

    Hi there,

    I am using the following formula to create a date calculation from a mail merge data source.

    My Problem is that the date field in my data source (and I can't change it) is in the format 25th June 2015, and the 'th' seems to cause an issue in the formula and the day calculation seems to default to calculate from the 1st of the month
    Capture.PNG

    the months and years calc OK.

    Any help appreciated

    Brad Palmer
    Attached Images Attached Images

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Is the date stored as an actual date in the data source? The ordinals suggest not, in which case you should consider removing the ordinals from the data source or creating another field that stores the dates without them and use that for the merge. Word can't easily parse the day part of the field with the ordinal attached - the field coding required for that is:
    {QUOTE{SET Day {MERGEFIELD MergeDate}}{IF{REF Day}= "1*" 1 {IF{REF Day}= "2*" 2 {IF{REF Day}= "3*" 3 {IF{REF Day}= "4*" 4 {IF{REF Day}= "5*" 5 {IF{REF Day}= "6*" 6 {IF{REF Day}= "7*" 7 {IF{REF Day}= "8*" 8 {IF{REF Day}= "9*" 9}}}}}}}}}{IF{REF Day}= "?1*" 1 {IF{REF Day}= "?2*" 2 {IF{REF Day}= "?3*" 3 {IF{REF Day}= "?4*" 4 {IF{REF Day}= "?5*" 5 {IF{REF Day}= "?6*" 6 {IF{REF Day}= "?7*" 7 {IF{REF Day}= "?8*" 8 {IF{REF Day}= "?9*" 9 {IF{REF Day}= "?0*" 0}}}}}}}}}}}
    You would need to insert that where you now have {MERGEFIELD MergeDate \@ d}. There's a macro for converting the above to a working field code in the Mailmerge Tips & Tricks 'Sticky' thread at the top of this forum.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    New Lounger
    Join Date
    Apr 2016
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the prompt response
    The date in the data source is formatted as 4th January 2010, unfortunately we cannot change this format as this would affect hundreds of client sites.
    Because this is on a client system we are also not able to run vba macros either.

    I have changed the code as you suggested but now get a field calculation error.
    I have attached the data source (data.doc) and my merge to document (AnimalBirthdate_merge_fields_for_Date_Calcs).

    Once again any help is much appreciated

    Brad Palmer
    Attached Files Attached Files

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    As indicated in my previous post, you need to convert the text representation of the field I posted into a working field code - copying and pasting from the post on its own won't suffice.

    Furthermore, you have replaced more of the original field coding than I indicated you should.
    Last edited by macropod; 2016-05-04 at 05:12.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    New Lounger
    Join Date
    Apr 2016
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry Paul, I am no Microsoft word expert
    I have decided to try what you said for just 1 day of the month - 4th and now have the following but it still does not work.
    Capture.JPG

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    As I said, you were supposed to replace the field coded as {MERGEFIELD MergeDate \@ d} with a field based on the representation I posted. But you still have the {MERGEFIELD MergeDate \@ d} field! If you paid attention to what you were told you would now have a working field coded as:
    {SET d{QUOTE{SET Day {MERGEFIELD MergeDate}}{IF{REF Day}= "1*" 1 {IF{REF Day}= "2*" 2 {IF{REF Day}= "3*" 3 {IF{REF Day}= "4*" 4 {IF{REF Day}= "5*" 5 {IF{REF Day}= "6*" 6 {IF{REF Day}= "7*" 7 {IF{REF Day}= "8*" 8 {IF{REF Day}= "9*" 9}}}}}}}}}{IF{REF Day}= "?1*" 1 {IF{REF Day}= "?2*" 2 {IF{REF Day}= "?3*" 3 {IF{REF Day}= "?4*" 4 {IF{REF Day}= "?5*" 5 {IF{REF Day}= "?6*" 6 {IF{REF Day}= "?7*" 7 {IF{REF Day}= "?8*" 8 {IF{REF Day}= "?9*" 9 {IF{REF Day}= "?0*" 0}}}}}}}}}}}}

    Changing your field names doesn't help either. If 'AnimalBirthDate' is the real name, you'll need to change 'MergeDate' to reflect that.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    New Lounger
    Join Date
    Apr 2016
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Paul, sorry to have obviously annoyed you, iy seemed that I misinterpreted the original instructions to leave the {Set d in place.

    I do appreciate your assistance and I am nearly there. The results I get for the 1st - 9th of the month and the 11th and 22nd of the Month are now correct, however for the 10th and 12th onwards I get incorrect results.
    Animal Birthdate Merge field = 10th May 2016

    Results I get
    10th = 02/05/2016
    12th = 22/05/2016
    13th = }32-5-2016
    14th = }42-5-2016
    15th =
    16th =
    17th =
    18th =
    19th =
    20th =
    21st =
    23rd = }33-5-2016
    24th = }43-5-2016
    25th = }53-5-2016
    26th = }63-5-2016
    27th = }73-5-2016
    28th = }83-5-2016
    29th = }93-5-2016
    30th = 04/05/2016
    31st = 14/05/2016

    I know the formatting of the results should give me a clue, but I am still out of ideas.

    The code I have now is
    Capture.JPG

  8. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Did you use the macro from the Mailmerge Tips & Tricks 'Sticky' thread at the top of this forum, as suggested in post #2 to convert the text to a working field code? The fact you're getting a } in the output suggests some fields from my post(s) haven't been converted correctly.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  9. #9
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    I had a play with this and it seemed like the ordinal was causing these dates to always be set to the first of the month.

    To test this, I added the date formatting switch to compare {MERGEFIELD AnimalBirthdate} with {MERGEFIELD AnimalBirthdate \@ "d MMM yyyy"}. The first one shows the date as it is stored in the source data, the second one shows it formatted as a date. If you can't get these to align you are not going to solve the formulas for correctly adding days.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  10. #10
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Andrew: The field code I provided addresses the problem with the ordinal formatting in the data source. All the back & forth since then has been to do with whether those changes have been implemented correctly. Had the OP followed the advice given, he'd have fields like the attached.
    Attached Files Attached Files
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  11. #11
    New Lounger
    Join Date
    Apr 2016
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Andrew and Paul, I'm guessing the instruction I have not followed was to run a macro from the Mailmerge Tips & Tricks 'Sticky' thread, I have absolutely no experience at running Macros in word, other than I know where to find them but unsure what to do after that.
    Sorry for being such a dummy.

  12. #12
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    I have now done that and included a document with the result as an attachment to my previous post. Did you look at that?
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  13. #13
    New Lounger
    Join Date
    Apr 2016
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ok great thanks for that, I did download the attachment but hadn't realised that you had run the macro, I will test out and let you know.
    Thanks this is really much appreciated.

Posting Permissions

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