Adding months to a date from separate cell.

Mike DeLucaMike DeLuca ✭✭✭✭
edited 04/22/21 in Formulas and Functions
04/22/21 Edited 04/22/21
Answered - Pending Review

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.

Answers

  • Heather DHeather D ✭✭✭✭✭

    @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 DeLucaMike DeLuca ✭✭✭✭

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

  • Heather DHeather D ✭✭✭✭✭

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

Sign In or Register to comment.