Add month in calculated date column

Question

I'm using SharePoint 2007. I've a column with date in format DD/MM/YYYY

I've also another column where I want to add some months to the previous date and obviously I've a column with the number of months I want to add.

So, the calculated column formula is:

DATE(YEAR([Invoice Date]);MONTH([Invoice Date])+(Months_to_add);DAY([Invoice Date]))

But, in this case, if [Invoice Date] is, for example 31/12/2013 and I add two months, I obtain 01/03/2014 instead 28/02/2014. It will be also fine to set all days to "01" to avoid this problem, but using DATEVALUE (solution found googling) SP returns a formula error and forcing DAY(01) returns an error.

Any suggestion?

Thanks

Answer 1

Ok, the answer is a bit late, but still for those struggling with the same issue, as future SharePoint versions would show the same result.

If you run a couple of tests (on paper) for these last day of the month dates, you will notice that in most, if not all cases the resulted date is off the number of days of the calculated date. Example:

• 31/1/2016 + 1 month would actually result as 2/3/2016 being 2 days past 29/2
• 31/10/2015 + 6 months would actually result as 1/5/2016 being 1 day past 30/4

In order to compensate you also need to extract the calculated date's day value if it is not equal to the Start Date's day value; if they are equal, you just extract 0 days. See Formula 1:

Formula 1:
    DATE(YEAR([Start Date]),
         MONTH([Start Date])+MonthsToAdd,
         DAY([Start Date]) - IF(DAY([Start Date]) = DAY(DATE(YEAR([Start Date]),MONTH([Start Date])+MonthsToAdd,DAY([Start Date]))),
                                0,
                                DAY(DATE(YEAR([Start Date]),MONTH([Start Date])+MonthsToAdd,DAY([Start Date]))))

Formula 2:
    IF(MONTH([Start Date]+1)<>MONTH([Start Date]),
       DATE(YEAR([Start Date]),MONTH([Start Date])+MonthsToAdd+1,1)-1,
       DATE(YEAR([Start Date]),MONTH([Start Date])+MonthsToAdd,DAY([Start Date])-IF(DAY([Start Date])=DAY(DATE(YEAR([Start Date]),MONTH([Start Date])+MonthsToAdd,DAY([Start Date]))),
                                                                                         0,
                                                                                         DAY(DATE(YEAR([Start Date]),MONTH([Start Date])+MonthsToAdd,DAY([Start Date]))))))

In case you prefer to maintain the last day of the month for the new date, Formula 1 will fail to do so (April 30 + 1 month will return May 30). To accomplish this, look at Formula 2. Here I check if increasing the start date with 1 day progresses it into the next month. If so I calculate the due date by adding the extra months plus 1 and use 1 for the day. From the resulting date I subtract 1 day. Otherwise I use the calculation from Formula 1.