Decrease MailMerge Date Field

Question

Good morning,

I am merging a "Year End" date field into a Word Document that is stored as January 31, February 28, etc.  I  need to make changes to the date in the document rather than in the source file.

I was having trouble that the year was defaulting to the current year 2013 and was thought I was able to correct that using SET DELAY -365.  

There is another field that needs to be the first day of the prior plan year.  So, January 1, 2012 for December 31, 2012 year ends.  Using SET DELAY -730 works well for the December year ends.   For other plan year ends, it goes all wonky:

Jan 31 year ends returns Jan 2 as the first (2011) and last (2012) day of the plan year.  Both should be 2012.

Feb 29 YE returns Jan 3

Mar 31 returns Jan 4

Oct 31 YE returns Jan 11

I believe the code I am using is from the "Calculate a day, date, month and year, using n days delay" section of DateCalc V2_85.doc by Macropod.

I think my problem is in needing to make some tweaks to the other SET fields (I did manage to remove the day).  I am not familiar with this coding and don't even know enough to be able to Google for help.  Can someone tell me what this language is called and point me in the right direction to learn more about how it works and what the commands mean?

Thank you!

Answer 1

Sorry for the delay in responding.  Doug Robbins pointed me in the right direction for most of my solution. After much back and forth with SFQ support, I was able to use a formula to add fields to my report that keep my source data formatting, January 31, Feb 28, etc. 

I am still stumped on generating the first day of the prior year.  I did play around with the calculate a date and lease expiry a bit but with no luck.  It is something I will revisit after busy season.

Thank you all for your help!

Answer 2

Hi Meghan,

As Graham has noted: "if you are working only with the day and month, Word cannot determine the year".

If you know the year is always last year, that part can be derived from a DATE field, using code like:

{={DATE @ YYYY}-1). Alternatively, given that the output already defaults to the current year, you can easily get the required date using the tutorial's "Calculate a day, date, month and year, using n years delay" example as the basis and setting the 'Delay' value to -1. Then, to calculate the end date, you can use the tutorial's "Calculate a lease expiry date, using n months delay" example to calculate the end of whatever year starts on the designated start date.

Answer 3

I suggest that you ask at http://www.stonefieldquery.com/Support/

Answer 4

It is a rolling plan year end.  So the Jan 31 year end starts Feb 1 of the prior year.

I will do some exploring on the PrevYr command.

Thank you.