or Explore Discussions

# Adding months to a date from separate cell.

04/22/21 Edited 04/22/21

Current been working on a few formulas posted previously to add anywhere from 4-18 month to a date. But can not get anything to work.

So Start Date 10/01/20 + 18 months.

Is there a simple formula that can undertake this?

• "Simple"? - no. "Formula that can undertake this"? Yes. Here's what I came up with recently - I think this is what I did:

=DATE(YEAR([Start d]@row),MONTH([Start d]@row)+[Months to compl]@row,DAY([Start D]@row))

Let me know if it works!

Best,

Heather

• Thanks Heather works for =/> 12months. when I introduce a month parameter greater than 12 months I get #invalid Value

• Rats! I forgot that part. Try this:

=if((month([start d]@row)+[months to compl]@row)<13,DATE(YEAR([Start d]@row),MONTH([Start d]@row)+[Months to compl]@row,DAY([Start D]@row)),DATE(YEAR([Start d]@row+1),MONTH([Start d]@row)+[Months to compl]@row-12,DAY([Start D]@row))

If you'll have something that spans more up to 2 years, try this:

=if((month([start d]@row)+[months to compl]@row)<13,DATE(YEAR([Start d]@row),MONTH([Start d]@row)+[Months to compl]@row,DAY([Start D]@row)),if((month([start d]@row)+[months to compl]@row)<25,DATE(YEAR([Start d]@row+1),MONTH([Start d]@row)+[Months to compl]@row-24,DAY([Start D]@row)),"")

I put the ,"" part in at the end in case you go past the 2-year mark, a blank will indicate that you'll need to add to the formula.

Alternately, you could create Years and Months columns. Then it would be a little cleaner:

=if((month([start d]@row)+[months to compl]@row)<13,DATE(YEAR([Start d]@row)+[years to compl]@row,MONTH([Start d]@row)+[Months to compl]@row,DAY([Start D]@row)),DATE(YEAR([Start d]@row+[years to compl]@row+1),MONTH([Start d]@row)+[Months to compl]@row-12,DAY([Start D]@row))

In this scenario, for a task that is 18 months to completion, you'd put 1 year in the years column and 6 months in the months column. It makes the formula more versatile for longer projects. Just a thought. :)