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

I use Stonefield Querry to pull the data from iChannel by Conarc.

Answer 2

So let me get this right. You have a date (say) 01/31/13 which resolves to 01/31/2013 and you want the first and last days of the previous year i.e 1/1/2012 and 12/31/2012? That being the case you need

{ SET PrevYr {=({MERGEFIELD "IC_C_UEBSPLANYE" @ "yyyy"} - 1 ) } } and for the 1st January

1/1/{ PrevYr }

and for the 31st Dec

12/31/{ PrevYr }

Answer 3

I would expect that it would be much easier to add a couple of additional fields to the data source and use some method of populating them with the required data.

What is the data source?

Answer 4

Mr. Mayor,

I am generating correspondence relative to the year end that has just past.  At various points in the document, I need to insert the first or last day of the prior plan year. (January 1, 2012 and December 31, 2012, currently.)

In the raw, {MERGEFIELD "IC_C_UEBSPLANYE"} returns "12/31/2013"  The values in this field are . . . well, I went out to verify that they are January 31, February 28, etc. as they appear in the CRM software and the reportwriter but I now see they are coming into Excel as 01/31/13, 02/28/13.  Drat.

So now I understand why I am having this problem!  I will have to do some backtracking tomorrow and see what I can do about the formatting of the source data.

Thank you.

Answer 5

I don't know if Paul (Macropod) is around this weekend, but I have alerted him to the thread.

This 'language' is a not so simple use of mathematical calculations with Word fields and Paul's document is a tour de force on the subject.

However, looking briefly at your problem, if you are working only with the day and month, Word cannot determine the year and that would be essential to making a calculation. You need to produce a means of identifying the year before you can hope to calculate a revised date.

What changes do you need to make to the 'dates' that you derive from your data?

What EXACTLY appears in the document when you merge the date field in question without recourse to calculations or formatting switches?