# Adding Months to a Date

Options
✭✭✭
edited 12/09/19

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.

Tags:

## Comments

• ✭✭✭✭✭✭
Options

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))

• Options

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.

• ✭✭✭✭✭✭
Options

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!