# Thread: Mail Merge Date Calculations

1. ## 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

2. 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.

3. 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

4. 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.

5. 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. 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.

7. 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. 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.

9. 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.

10. 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.

11. 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. I have now done that and included a document with the result as an attachment to my previous post. Did you look at that?

13. 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
•