Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Mailmerge and Fax / March 2007

Tip: Looking for answers? Try searching our database.

Adding days to Merge Field

Thread view: 
Enable EMail Alerts  Start New Thread
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.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.