Adding Months to a Date

Options
Wilbsguy
Wilbsguy ✭✭✭
edited 12/09/19 in Formulas and Functions

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.

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!