Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-01-2022, 02:05 AM
FurryMuff FurryMuff is offline Mail Merge date calculation to add 2 weeks but must be Tues or Fri only Windows 10 Mail Merge date calculation to add 2 weeks but must be Tues or Fri only Office 2010
Novice
Mail Merge date calculation to add 2 weeks but must be Tues or Fri only
 
Join Date: Feb 2022
Location: Australia
Posts: 1
FurryMuff is on a distinguished road
Default 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"}
Any help would be much appreciated!

Thanks, FurryMuff
Reply With Quote
  #2  
Old 02-01-2022, 06:09 PM
macropod's Avatar
macropod macropod is offline Mail Merge date calculation to add 2 weeks but must be Tues or Fri only Windows 10 Mail Merge date calculation to add 2 weeks but must be Tues or Fri only Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Mail Merge date calculation to add 2 weeks but must be Tues or Fri only 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 calculation to add 2 weeks but must be Tues or Fri only Mail Merge Date Error KDP Mail Merge 2 12-11-2017 01:38 PM
Mail Merge date calculation to add 2 weeks but must be Tues or Fri only 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 date calculation to add 2 weeks but must be Tues or Fri only Mail Merge Calculation Error Lsbutler2000 Mail Merge 1 06-22-2007 06:33 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:30 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft