Adding Months to a Date

Wilbsguy

The Date formula allows addition but I get errors when I cross months by adding days or when I cross years by adding months. (Haven't tested years with adding days but that also crosses a month so I would get an error anyway.

I need it to act like Excel and actually add a year for each multiple of 12 months in particular. Days is annoying but I can work with that in other ways.

 

So I created a formula to force this on the frontend. 

=DATE(

YEAR([Start Date]@row) + INT((MONTH([Start Date]@row) + Duration@row) / 12),

(((MONTH([Start Date]@row) + Duration@row) / 12) - INT((MONTH([Start Date]@row) + Duration@row) / 12)) * 12,

DAY([Start Date]@row))

But it is acting weird. There are times it works, I was testing a Start Date of 11/1/2019 and a duration of 4 months. It worked. Make it 5 months, it says "#Invalid Data Type" - but the column is set as a date type. If I break out the parts, I get a variety of errors but sometimes it works. I even get all three to work and when I put it together, it breaks. So I can get the year to say 2020, the month to 4, and day to 1. But paste all those into a date formula in a date column and it gives me the error. A little frustrated.

 

I suppose I could use Netdays * 30 but I am stuck on this should be so simple and should work. It is the principle of the matter.

L_123

Give this a try

 

=DATE(YEAR([Start Date]@row + INT((MONTH([Start Date]@row) + Duration@row) / 12)), 12 * ((MONTH([Start Date]@row) + Duration@row) / 12 - INT((MONTH([Start Date]@row) + Duration@row) / 12)), DAY([Start Date]@row))

Troy Filson

This is so complex for such a simple calculation.

Is there not another solution?

I'm trying to calculate 3 weeks prior to a go-live date, which is common in my work.

Andrée Starå

Hi Troy,

Fortunately, there is a much simpler solution.

Try something like this.

=[Proposed GoLive Date]@row- 21

Did it work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting