Use a numerical merge field to subtract that number of months

MichaelSpedding · #1 11-10-2015, 09:40 AM

Hi,

I am trying to enter a 'notice date' into a merge field however the software we use holds this as a numerical value in months (e.g. '6' months notice, as opposed to 'notice date 01/01/16').

What I have is:

{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+{NoticeDate \@ M} - {MERGEFIELD Unit_Lease_Dates_Notice6}/12}}
"{NoticeDate \@ D}{mm}{yy}" \@ MMMM D, YYYY}

Unit_Lease_Dates8 = 1 Mar 2019
Unit_Lease_Dates_Notice6 = 6

The result that is being returned is January 3, 2015

Obviously this is incorrect (it should be 'September 1, 2018' - pretty close I think...

), however, this is my first venture into Word merge field codes (or whatever the technically correct name for them are) so please forgive my lag of ability. I have cobbled this together from various sources but would like some assistance in pulling it all together.

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}.

macropod · #3 11-10-2015, 05:22 PM

Cross-posted at: http://answers.microsoft.com/en-us/o...6-07579e264cf2
For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184

MichaelSpedding · #4 11-11-2015, 03:35 AM

Hi,

thanks for your response.

Code:

{  QUOTE
{ SET Delay -{MERGEFIELD Unit_Lease_Dates_Notice6}}  
{ SET mm =MOD(ABS( {MERGEFIELD Unit_Lease_Dates8 \@ M} +Delay+11),12)+1}} 
{ SET yy {=INT( {MERGEFIELD Unit_Lease_Dates8 \@ yyyy}+(Delay+ {MERGEFIELD Unit_Lease_Dates8 \@ M}-1/12)}}  
{ SET dd {=IF(( {MERGEFIELD Unit_Lease_Dates8 \@ 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)+( {MERGEFIELD Unit_Lease_Dates8 \@ d}>30)>1,30, {MERGEFIELD Unit_Lease_Dates8 \@ d}))  
" dd - mm - yy " \@ "d MMMM, yyyy”  }

This is what I have tried with no joy... it is bringing back 1 March, 2015 :(

What am I doing wrong?

also, apologies about cross posting, I'll play the ignorance card this time, won't happen again!

macropod · #5 11-11-2015, 04:22 AM

1. What is your regional date format?
2. What is returned by {MERGEFIELD Unit_Lease_Dates8}
3. What is returned by {MERGEFIELD Unit_Lease_Dates_Notice6}

MichaelSpedding · #6 11-11-2015, 04:27 AM

1. I'm in the UK so it is dd/mm/yyyy
2. Unit_Lease_Dates8 = 01/03/2019
3. Unit_Lease_Dates_Notice6 = 6

gmayor · #7 11-11-2015, 05:44 AM

If you want 01/09/2019 from 01/03/2019 then use Paul's example -

{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"}

Change the final switch \@ "dddd, d MMMM yyyy" to \@ "dd/MM/yyyy"

and then so you don't screw it up, use www.gmayor.com/export_field.htm to convert the text listing to fields.

MichaelSpedding · #8 11-11-2015, 09:00 AM

I have managed to get this to work now, thanks for the assistance!

I was trying to take away the number of months but it wasn't, for some reason, working when I 'SET Delay' to be negative but it did work when I subtracted 'Delay' within the other formulae.

thanks again and sorry for the cross posting!

macropod · #9 11-11-2015, 01:11 PM

The '1 March, 2015' result suggests you were only previewing the merge, not finishing it.

As for the Delay issue, that could be addressed by coding the SET field as:
{SET Delay {=-{MERGEFIELD Unit_Lease_Dates_Notice6}}}
or:
{SET Delay {MERGEFIELD Unit_Lease_Dates_Notice6 \# "-0"}}

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to 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