Adding months to a date from separate cell.

Mike DeLuca

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?

Look forward to your input.

Heather Duff

@Mike DeLuca ,

"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

Mike DeLuca

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

Heather Duff

@Mike DeLuca

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

Mike DeLuca

Heather D I have tried a few times. the 12 month formula works (first one) but when I try the formula for 18-14 month I just get a blank cell. Im at a loss for a fix. Any ideas?

Heather Duff

Hi @Mike DeLuca

Rats. Try taking the second formula apart. Paste formula 1 in a row that spans less than 13 months, and formula 2 in a row that spans less than 25 months. Then we'll hopefully be able to pinpoint the error.

Formula 1: =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)),"")

Formula 2: =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)),"")

If either shows blank, we will have found where the error lies. (Though I suspect, based on what you said, it'll be in formula 2.

If we still have trouble, I'd suggest that you insert two helper columns - Years and Months. Then use these formulas:

in the Years column: =INT([months to compl]@row/12)

in the Months column: =(([months to compl]@row/12)-Years@row)*12

You could hide those two columns, then in your Compl Date column use this:

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

Mike DeLuca

The formula would always error out when months were greater than 9 month or 11 months. Thank you for you assistance anyway.

The following formula seems to work ok now.

=DATE(IF(MOD(VALUE(MONTH([Start D]@row)) + [Months to compl]@row, 12) = 0, -1, 0) + YEAR([Start D]@row) + (MONTH([Start D]@row) + [Months to compl]@row - MOD(MONTH([Start D]@row) + [Months to compl]@row, 12)) / 12, IF(MOD(VALUE(MONTH([Start D]@row) + [Months to compl]@row), 12) = 0, 12, MOD(MONTH([Start D]@row) + [Months to compl]@row, 12)), DAY([Start D]@row))