#1
|
|||
|
|||
Use a numerical merge field to subtract that number of months
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. |
#2
|
||||
|
||||
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"} 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"} {SET Delay -{MERGEFIELD Unit_Lease_Dates_Notice6}} making sure not to leave a space between '-' and {MERGEFIELD Unit_Lease_Dates_Notice6}.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
||||
|
||||
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#4
|
|||
|
|||
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” } What am I doing wrong? also, apologies about cross posting, I'll play the ignorance card this time, won't happen again! |
#5
|
||||
|
||||
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}
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#6
|
|||
|
|||
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 |
#7
|
||||
|
||||
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.
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#8
|
|||
|
|||
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! |
#9
|
||||
|
||||
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"}}
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Thread Tools | |
Display Modes | |
|
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 |