Use a numerical merge field to subtract that number of months

macropod · #2 11-10-2015, 01:56 PM

Unless you can be sure your field never contains a date later than the 28th of each month, you'll need to use something like the 'Calculate a day, date, month and year, using n months delay' example from my Microsoft Word Date Calculation Tutorial, at:
http://windowssecrets.com/forums/sho...ation-Tutorial
or:
http://www.gmayor.com/downloads.htm#Third_party
Do read the document's introductory material.

As coded, that example shows:

Code:

{QUOTE
{SET Delay 9}
{SET mm{=MOD(ABS({DATE \@ M}+Delay+11),12)+1}}
{SET yy{=INT({DATE \@ yyyy}+(Delay+{DATE \@ M}-1)/12)}}
{SET dd{=IF(({DATE \@ d}>28)*(mm=2)=1,28+((MOD(yy,4)=0)+(MOD(yy,400)=0)-(MOD(yy,100)=0)),IF((mm=4)+(mm=6)+(mm=9)+(mm=11)+({DATE \@ d}>30)>1,30,{DATE \@ d}))}}
"{dd}-{mm}-{yy}" \@ "dddd, d MMMM yyyy"}

You would need to change this to:

Code:

{QUOTE
{SET NoticeDate{MERGEFIELD Unit_Lease_Dates8}}
{SET Delay {MERGEFIELD Unit_Lease_Dates_Notice6}}
{SET mm{=MOD(ABS({NoticeDate \@ M}+Delay+11),12)+1}}
{SET yy{=INT({NoticeDate \@ yyyy}+(Delay+{DATE \@ M}-1)/12)}}
{SET dd{=IF(({NoticeDate \@ d}>28)*(mm=2)=1,28+((MOD(yy,4)=0)+(MOD(yy,400)=0)-(MOD(yy,100)=0)),IF((mm=4)+(mm=6)+(mm=9)+(mm=11)+({NoticeDate \@ d}>30)>1,30,{NoticeDate \@ d}))}}
"{dd}-{mm}-{yy}" \@ "dddd, d MMMM yyyy"}

It's also not clear whether you're adding or subtracting the 'Delay' value. If subtracting, use:
{SET Delay -{MERGEFIELD Unit_Lease_Dates_Notice6}}
making sure not to leave a space between '-' and {MERGEFIELD Unit_Lease_Dates_Notice6}.

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Spread whole number across specific months in a year	rkobeyer	Excel Programming	2	10-18-2014 11:46 PM
Counting Number of Months from a static date	bremen22	Excel	4	11-25-2013 11:57 AM
Converting serial number to years, months and days	hannu	Excel	6	02-14-2013 09:21 PM
Plotting Numerical and Non-numerical Data Set	Ife	Excel	0	04-23-2012 10:35 AM
Formula to subtract one month from due date field in reminder field	ghumdinger	Outlook	1	10-01-2011 12:09 AM