#1
|
|||
|
|||
Mail Merge date calculation to add 2 weeks but must be Tues or Fri only
I am trying to use a merge field calculation with two possible dates depending on when the document was issued. I can only get one to work but not the other due to both criteria having different amount of days in between.
I'm wondering if I'm able to use an IF statement but I have no idea how to write this. The criteria is shown below: If Document Issue Date falls on a Monday, then display 2 weeks later but on a Friday. If Document Issue Date falls on a Thursday, then display 2 weeks later but on a Tuesday. Example: Document Issue Date = Mon 17 Jan 2021 then display Fri 4 Feb 2021 (18 days) or Document Issue Date = Thu 20 Jan 2021 then display Tue 8 Feb 2021 (19 days) My current code that works correctly for the Monday issued date is below: Code:
{QUOTE "{SET Delay "18"} {SET a "{ =INT((14-{ MERGEFIELD Document_lssue_Date \@ M })/12)}”} {SET b "{ = { MERGEFIELD Document_lssue_Date \@ yyyy}+4800-a}"} {SET c "{= { MERGEFIELD Document_lssue_Date\@ M }+12*a-3}"} {SET d "{ = { MERGEFIELD Document_lssue_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)}"} {=yy*10A4+mm*10A2+dd \# "OOOO'-'OO'-'OO"}" \@ "ddd, d MMMM yyyy"} Thanks, FurryMuff |
#2
|
||||
|
||||
You have a lot of strange content in your field code - extraneous double-quotes and a final line that contains some weird character strings.
Assuming the Document Issues only occur on Mondays and Thursdays, you could use a field coded as: Code:
{QUOTE {SET a{=INT((14-{MERGEFIELD Document_lssue_Date \@ M})/12)}} {SET b{={MERGEFIELD Document_lssue_Date \@ yyyy}+4800-a}} {SET c{={MERGEFIELD Document_lssue_Date \@ M}+12*a-3}} {SET d{MERGEFIELD Document_lssue_Date \@ d}} {SET jd{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045+18+{=MOD(jd,7)=3}}} {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"}
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Field for date that I run the mail merge; not Date nor CreateDate | GregL | Mail Merge | 13 | 12-20-2018 12:08 AM |
Mail Merge Date Error | KDP | Mail Merge | 2 | 12-11-2017 01:38 PM |
Date field - future date calculation + only business days | neon4 | Word | 7 | 01-21-2016 02:21 PM |
Using WEEKNUM to calculate how many weeks from date to today. | stephen_ | Excel | 1 | 07-02-2013 06:41 AM |
Mail Merge Calculation Error | Lsbutler2000 | Mail Merge | 1 | 06-22-2007 06:33 AM |