Merge field code for Date Calculation

Hi,

Kindly help me with the Mergfield code. I want to enter any date the code should convert it to the last date of the month, add 45 days in it and display the date. Help me if someone have any idea.

Thanks

Answer
Answer

You can combine two of Paul's examples from his excellent tutorial to give you the required result. The first part determines the end of the month from the mergefield date and the second adds 45 days to that.

{SET StartDate {QUOTE {SET Subtract 0} {SET yy{MERGEFIELD DATE \@ yyyy}} {SET mm{MERGEFIELD DATE \@ M}} {SET dd{=MAX(1,IF((mm=2),28+(MOD(yy,4)=0)+(MOD(yy,400)=0)-(MOD(yy,100)=0),IF((mm=4)+(mm=6)+(mm=9)+(mm=11)+({ MERGEFIELD DATE \@ d}>30)=1,30,31))-Subtract)}} "{dd}-{mm}-{yy}" \@ "dd/MM/yyyy"} } {QUOTE {SET Delay 45} {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 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"}

Graham Mayor (Microsoft Word MVP 2002-2019)
For more Word tips and downloads visit my web site
https://www.gmayor.com/Word_pages.htm

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Answer
Answer

I am not sure what you mean in your final post as Delay-d does not appear in the listing?

It's in the last field code I posted:

{QUOTE{SET Delay 45}
 ...
{SET d{=({DATE \@ d}=29)*(m=2)+(({DATE \@ d}=28)*(m=2)*(1-((MOD(y,4)=0)+(MOD(y,400)=0)-(MOD(y,100)=0))))+((m=4)+(m=6)+(m=9)+(m=11))*({DATE \@ d}=30)+({DATE \@ d}=31)}}
 ...
{SET jd{=INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045+Delay-d}}
{...
"{dd}-{mm}-{yy}" \@ "dddd, d MMMM yyyy"}

Try:

{QUOTE{SET Delay 45}
{SET m{MERGEFIELD BeginDate \@ M}}
{SET y{MERGEFIELD BeginDate \@ yyyy}}
{SET a{=INT((14-m)/12)}}
{SET b{=y+4800-a}}
{SET c{=m+12*a-3}}

{SET d{=(28+((MOD(y,4)=0)+(MOD(y,400)=0)-(MOD(y,100)=0)))*(m=2)+((m=4)+(m=6)+(m=9)+(m=11))*30+((m=1)+(m=3)+(m=5)+(m=7)+(m=8)+(m=10)+(m=12))*31}}

{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}" \@ "d MMMM yyyy"}

Cheers
Paul Edstein
(Fmr MS MVP - Word)

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated July 27, 2023 Views 1,607 Applies to: