MS Office Forum / Word / Mailmerge and Fax / March 2007
Adding days to Merge Field
|
|
Thread rating:  |
TotallyConfused - 21 Mar 2007 19:07 GMT Please can someone tell me how to revise this merge field to add 3 business working days. I have a date field and I have to add a due date field (DATE\@ "MMMM d, yyyy"\*MERGEFORMAT), 2007. the due date field needs to be greater by 3 business working than the date field. I would appreciate some helping me with this. I was referred to macropod, and I have tried everything, can't seem to make it work. Thank you in advance for any help you can provide.
macropod - 21 Mar 2007 21:37 GMT Hi TotallyConfused,
To see how to add 3 days to a mailmerge date, check out 'Date Calculations In A Mailmerge' in my Date Calc 'tutorial', at: http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=249902
Note that the example field adds 14 calendar days, not 3 business days. To add 3 business days, which presumably means skipping over weekends, you'd need to expand the field coding (select it and press Shift-F9) and: . change '{SET Delay 14}' to '{SET Delay 3}' . add a new field after the one coded as '{SET jd{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045+Delay}'. The new field should be coded as {SET jd{=jd+(MOD(jd,7)>4)*(7-MOD(jd,7))}}, with the field braces (ie '{ }') created in pairs via Ctrl-F9 (you can't just copy & paste them from here). Skipping over public holidays as well takes a lot more work. I'm currently working on an update to the document to show how to do this, but it's not quite ready for release.
Cheers
 Signature macropod [MVP - Microsoft Word] -------------------------
> Please can someone tell me how to revise this merge field to add 3 business > working days. I have a date field and I have to add a due date field (DATE\@ > "MMMM d, yyyy"\*MERGEFORMAT), 2007. the due date field needs to be greater > by 3 business working than the date field. I would appreciate some helping > me with this. I was referred to macropod, and I have tried everything, can't > seem to make it work. Thank you in advance for any help you can provide. TotallyConfused - 21 Mar 2007 23:11 GMT Thanks for responding. This is what I have : I put {{SET Delay 3}DATE \@ "MMMM d, yyyy" \*MERGEFORMAT}{SET jd{=jd+(MOD(jd,7)>4*(7-MOD(jd,7))}
This is what I have and it is not working. I am probably now reading it right. Can you please tell me what I am doing wrong. I am trying to understand this. I do not work with Word a lot but by doing this it will save us a lot of time. I assume "jd" is for Julian Day?
Thank you.
> Hi TotallyConfused, > [quoted text clipped - 17 lines] > > me with this. I was referred to macropod, and I have tried everything, can't > > seem to make it work. Thank you in advance for any help you can provide. Graham Mayor - 22 Mar 2007 07:22 GMT You might do better downloading Macropod's sample document from the link provided and edit the examples rather than attempt to enter them from the keyboard.
 Signature <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP
My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<>
> Thanks for responding. This is what I have : I put {{SET Delay > 3}DATE \@ "MMMM d, yyyy" \*MERGEFORMAT}{SET [quoted text clipped - 40 lines] >>> referred to macropod, and I have tried everything, can't seem to >>> make it work. Thank you in advance for any help you can provide. macropod - 22 Mar 2007 08:22 GMT Hi TotallyConfused,
Your field coding won't work. You need to feed Word something it can digest.
The field I referred you to in my Date Calc 'tutorial' document looks like:
{QUOTE {SET Delay 14} {SET a{=INT((14-{ MergeDate \@ M})/12)}} {SET b{={ MergeDate \@ yyyy}+4800-a}} {SET c{={ MergeDate \@ M}+12*a-3}} {SET d{ MergeDate \@ d}} {SET jd{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045+Delay}} {SET e{=INT((4*(jd+32044)+3)/146097)}} {SET f{=jd+32044-INT(146097*e/4)}} {SET g{=INT((4*f+3)/1461)}} {SET h{=f-INT(1461*g/4)}} {SET i{=INT((5*h+2)/153)}} {SET dd{=h-INT((153*i+2)/5)+1}} {SET mm{=i+3-12*INT(i/10)}} {SET yy{=100*e+g-4800+INT(i/10)}} "{dd}-{mm}-{yy}" \@ "dddd, d MMMM yyyy"}
As per my previous response, you need to edit the field so that you get:
{QUOTE {SET Delay 3} {SET a{=INT((14-{ MergeDate \@ M})/12)}} {SET b{={ MergeDate \@ yyyy}+4800-a}} {SET c{={ MergeDate \@ M}+12*a-3}} {SET d{ MergeDate \@ d}} {SET jd{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045+Delay}} {SET jd{=jd+(MOD(jd,7)>4)*(7-MOD(jd,7))}} {SET e{=INT((4*(jd+32044)+3)/146097)}} {SET f{=jd+32044-INT(146097*e/4)}} {SET g{=INT((4*f+3)/1461)}} {SET h{=f-INT(1461*g/4)}} {SET i{=INT((5*h+2)/153)}} {SET dd{=h-INT((153*i+2)/5)+1}} {SET mm{=i+3-12*INT(i/10)}} {SET yy{=100*e+g-4800+INT(i/10)}} "{dd}-{mm}-{yy}" \@ "dddd, d MMMM yyyy"}
Note the change to the 2nd line and the new 8th line that I've inserted.
Take note too of the tutorial's 'Introductory Notes'.
Cheers
 Signature macropod [MVP - Microsoft Word] -------------------------
> Thanks for responding. This is what I have : I put {{SET Delay 3}DATE \@ > "MMMM d, yyyy" \*MERGEFORMAT}{SET jd{=jd+(MOD(jd,7)>4*(7-MOD(jd,7))} [quoted text clipped - 29 lines] >> > me with this. I was referred to macropod, and I have tried everything, can't >> > seem to make it work. Thank you in advance for any help you can provide. TotallyConfused - 22 Mar 2007 14:38 GMT Okay in the document I am working on the due date is not a merge field. It is a date that I have to enter manually. But would have to be +3 days from my other Date field that is entered when the document is opened. This date I put in from the menu View - Field - Date. All my other fields are merge fields. Can this be done without merging a date?
> Hi TotallyConfused, > [quoted text clipped - 77 lines] > >> > me with this. I was referred to macropod, and I have tried everything, can't > >> > seem to make it work. Thank you in advance for any help you can provide. Graham Mayor - 22 Mar 2007 15:02 GMT Change Mergedate in Macropod's example to Date then the date + 3 is calculated from the system date rather than the merge date.
 Signature <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP
My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<>
> Okay in the document I am working on the due date is not a merge > field. It is a date that I have to enter manually. But would have [quoted text clipped - 106 lines] >>>>> referred to macropod, and I have tried everything, can't seem to >>>>> make it work. Thank you in advance for any help you can provide. macropod - 22 Mar 2007 22:03 GMT Hi TotallyConfused,
At the start of this thread you said you wanted help to "revise this merge field to add 3 business working days". Now you say "the due date is not a merge field".
If the date you want the calculation to be based on isn't from your mailmerge data source, then I suggest you use the example found under 'Calculate a day, date, month and year, using n days delay' and modify it to suit. As coded, the field uses the current system date and looks like:
{QUOTE {SET Delay 14} {SET a{=INT((14-{DATE \@ M})/12)}} {SET b{={DATE \@ yyyy}+4800-a}} {SET c{={DATE \@ M}+12*a-3}} {SET d{DATE \@ d}} {SET jd{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045+Delay}} {SET e{=INT((4*(jd+32044)+3)/146097)}} {SET f{=jd+32044-INT(146097*e/4)}} {SET g{=INT((4*f+3)/1461)}} {SET h{=f-INT(1461*g/4)}} {SET i{=INT((5*h+2)/153)}} {SET dd{=h-INT((153*i+2)/5)+1}} {SET mm{=i+3-12*INT(i/10)}} {SET yy{=100*e+g-4800+INT(i/10)}} "{dd}-{mm}-{yy}" \@ "dddd, d MMMM yyyy"}
First off, make the same changes that I outlined in my previous post. This will give you:
{QUOTE {SET Delay 3} {SET a{=INT((14-{DATE \@ M})/12)}} {SET b{={DATE \@ yyyy}+4800-a}} {SET c{={DATE \@ M}+12*a-3}} {SET d{DATE \@ d}} {SET jd{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045+Delay}} {SET jd{=jd+(MOD(jd,7)>4)*(7-MOD(jd,7))}} {SET e{=INT((4*(jd+32044)+3)/146097)}} {SET f{=jd+32044-INT(146097*e/4)}} {SET g{=INT((4*f+3)/1461)}} {SET h{=f-INT(1461*g/4)}} {SET i{=INT((5*h+2)/153)}} {SET dd{=h-INT((153*i+2)/5)+1}} {SET mm{=i+3-12*INT(i/10)}} {SET yy{=100*e+g-4800+INT(i/10)}} "{dd}-{mm}-{yy}" \@ "dddd, d MMMM yyyy"}
As per your latest post, this should give you what you say you want, but anything based on a plain DATE field is liable to change the calculated date every time the document is opened. I suggest you think carefully whether that it appropriate - including for the DATE field you're already inserting. If you don't want the field changing the results every time you open the document, change all 'DATE' instances to one of 'CREATEDATE', 'SAVEDATE' or 'PRINTDATE', depending on what you want to achieve.
If, instead of using the system date with a 'DATE', 'CREATEDATE', 'SAVEDATE' or 'PRINTDATE' field, you want to be prompted for the starting date: . change all 'DATE' instances to 'StartDate' . add a new second line coded as: {ASK StartDate "Please input the start date"} This will give you a field coded as:
{QUOTE {ASK StartDate "Please input the start date"} {SET Delay 3} {SET a{=INT((14-{StartDate \@ M})/12)}} {SET b{={StartDate \@ yyyy}+4800-a}} {SET c{={StartDate \@ M}+12*a-3}} {SET d{StartDate \@ d}} {SET jd{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045+Delay}} {SET jd{=jd+(MOD(jd,7)>4)*(7-MOD(jd,7))}} {SET e{=INT((4*(jd+32044)+3)/146097)}} {SET f{=jd+32044-INT(146097*e/4)}} {SET g{=INT((4*f+3)/1461)}} {SET h{=f-INT(1461*g/4)}} {SET i{=INT((5*h+2)/153)}} {SET dd{=h-INT((153*i+2)/5)+1}} {SET mm{=i+3-12*INT(i/10)}} {SET yy{=100*e+g-4800+INT(i/10)}} "{dd}-{mm}-{yy}" \@ "dddd, d MMMM yyyy"}
The example under 'Interactively Calculate A Past Or Future Date' in the tutorial document has a more elaborate version of this.
Finally, if you want to be prompted as per the last field, but only once per mailmerge, add a '\o' switch to the ASK field (ie {ASK StartDate "Please input the start date" \o}).
Cheers
 Signature macropod [MVP - Microsoft Word] -------------------------
> Okay in the document I am working on the due date is not a merge field. It > is a date that I have to enter manually. But would have to be +3 days from [quoted text clipped - 86 lines] >> >> > me with this. I was referred to macropod, and I have tried everything, can't >> >> > seem to make it work. Thank you in advance for any help you can provide.
|
|
|