How to add varying months to varying dates?

I am really having trouble getting a formula to work here. I've gone through a number of support articles, but I'm just missing something.

I want a formula that looks at Date Contract Executed and it adds Months Until Expiration to produce a new Date of Expiration. I've got my date columns as dates and the middle column as a number.


Any help is appreciated!

Tags:

Best Answers

  • Leibel S
    Leibel S Community Champion
    Answer ✓

    @S.Stone

    This should work:

    =DATE(YEAR([Date Contract Executed]@row) + ROUNDDOWN(([Months Until Expiration]@row + MONTH([Date Contract Executed]@row)) / 12.00001, 0), MOD([Months Until Expiration]@row, 12) + MONTH([Date Contract Executed]@row), DAY([Date Contract Executed]@row))

  • Paul Newcome
    Paul Newcome Community Champion
    Answer ✓

    This should get the job done for you:

    =IFERROR(IFERROR(DATE(YEAR([Date Contract Executed]@row) + ROUNDDOWN((MONTH([Date Contract Executed]@row) + [Months Until Expiration]@row) / 12, 0) + IF(IF(MOD(MONTH([Date Contract Executed]@row) + [Months Until Expiration]@row, 12) = 0, 12, MOD(MONTH([Date Contract Executed]@row) + [Months Until Expiration]@row, 12)) = 12, -1) - IF(AND(ABS([Months Until Expiration]@row) - MONTH([Date Contract Executed]@row) <> 12, [Months Until Expiration]@row < 0, ABS([Months Until Expiration]@row) > MONTH([Date Contract Executed]@row)), 1, 0), IF(MOD(MONTH([Date Contract Executed]@row) + [Months Until Expiration]@row, 12) = 0, 12, MOD(MONTH([Date Contract Executed]@row) + [Months Until Expiration]@row, 12)), DAY([Date Contract Executed]@row)), DATE(IF(MONTH([Date Contract Executed]@row) - ABS([Months Until Expiration]@row) < 1, YEAR([Date Contract Executed]@row) - 1, YEAR([Date Contract Executed]@row)), IF(MONTH([Date Contract Executed]@row) - ABS([Months Until Expiration]@row) < 1, MONTH([Date Contract Executed]@row) + (12 - ABS([Months Until Expiration]@row)), MONTH([Date Contract Executed]@row) - ABS([Months Until Expiration]@row)), DAY([Date Contract Executed]@row))), "")

  • Leibel S
    Leibel S Community Champion
    Answer ✓

    @S.Stone

    I see what happened here:

    the below should fix it

    =DATE(YEAR([Date Contract Executed]@row) + ROUNDDOWN(([Months Until Expiration]@row + MONTH([Date Contract Executed]@row)) / 12.00001, 0), ROUNDUP(MOD([Months Until Expiration]@row + MONTH([Date Contract Executed]@row), 12.00001), 0), DAY([Date Contract Executed]@row))

Answers

  • Leibel S
    Leibel S Community Champion
    Answer ✓

    @S.Stone

    This should work:

    =DATE(YEAR([Date Contract Executed]@row) + ROUNDDOWN(([Months Until Expiration]@row + MONTH([Date Contract Executed]@row)) / 12.00001, 0), MOD([Months Until Expiration]@row, 12) + MONTH([Date Contract Executed]@row), DAY([Date Contract Executed]@row))

  • I hadn't seen "Rounddown" before — thank you!!

  • Paul Newcome
    Paul Newcome Community Champion
    Answer ✓

    This should get the job done for you:

    =IFERROR(IFERROR(DATE(YEAR([Date Contract Executed]@row) + ROUNDDOWN((MONTH([Date Contract Executed]@row) + [Months Until Expiration]@row) / 12, 0) + IF(IF(MOD(MONTH([Date Contract Executed]@row) + [Months Until Expiration]@row, 12) = 0, 12, MOD(MONTH([Date Contract Executed]@row) + [Months Until Expiration]@row, 12)) = 12, -1) - IF(AND(ABS([Months Until Expiration]@row) - MONTH([Date Contract Executed]@row) <> 12, [Months Until Expiration]@row < 0, ABS([Months Until Expiration]@row) > MONTH([Date Contract Executed]@row)), 1, 0), IF(MOD(MONTH([Date Contract Executed]@row) + [Months Until Expiration]@row, 12) = 0, 12, MOD(MONTH([Date Contract Executed]@row) + [Months Until Expiration]@row, 12)), DAY([Date Contract Executed]@row)), DATE(IF(MONTH([Date Contract Executed]@row) - ABS([Months Until Expiration]@row) < 1, YEAR([Date Contract Executed]@row) - 1, YEAR([Date Contract Executed]@row)), IF(MONTH([Date Contract Executed]@row) - ABS([Months Until Expiration]@row) < 1, MONTH([Date Contract Executed]@row) + (12 - ABS([Months Until Expiration]@row)), MONTH([Date Contract Executed]@row) - ABS([Months Until Expiration]@row)), DAY([Date Contract Executed]@row))), "")

  • So, circling back here, but the first formula had a problem that I couldn't resolve.

    I used this formula in the first four rows, but something happened with the Aug 15, '23, date.

    =DATE(YEAR([Date Contract Executed]@row) + ROUNDDOWN(([Months Until Expiration]@row + MONTH([Date Contract Executed]@row)) / 12.00001, 0), MOD([Months Until Expiration]@row, 12) + MONTH([Date Contract Executed]@row), DAY([Date Contract Executed]@row))

    I used the other formula (below) in the fifth row, and it's resolved!

    =IFERROR(IFERROR(DATE(YEAR([Date Contract Executed]@row) + ROUNDDOWN((MONTH([Date Contract Executed]@row) + [Months Until Expiration]@row) / 12, 0) + IF(IF(MOD(MONTH([Date Contract Executed]@row) + [Months Until Expiration]@row, 12) = 0, 12, MOD(MONTH([Date Contract Executed]@row) + [Months Until Expiration]@row, 12)) = 12, -1) - IF(AND(ABS([Months Until Expiration]@row) - MONTH([Date Contract Executed]@row) <> 12, [Months Until Expiration]@row < 0, ABS([Months Until Expiration]@row) > MONTH([Date Contract Executed]@row)), 1, 0), IF(MOD(MONTH([Date Contract Executed]@row) + [Months Until Expiration]@row, 12) = 0, 12, MOD(MONTH([Date Contract Executed]@row) + [Months Until Expiration]@row, 12)), DAY([Date Contract Executed]@row)), DATE(IF(MONTH([Date Contract Executed]@row) - ABS([Months Until Expiration]@row) < 1, YEAR([Date Contract Executed]@row) - 1, YEAR([Date Contract Executed]@row)), IF(MONTH([Date Contract Executed]@row) - ABS([Months Until Expiration]@row) < 1, MONTH([Date Contract Executed]@row) + (12 - ABS([Months Until Expiration]@row)), MONTH([Date Contract Executed]@row) - ABS([Months Until Expiration]@row)), DAY([Date Contract Executed]@row))), "")

    Sharing in case anyone else gets this error and is looking for an answer.

  • Leibel S
    Leibel S Community Champion
    Answer ✓

    @S.Stone

    I see what happened here:

    the below should fix it

    =DATE(YEAR([Date Contract Executed]@row) + ROUNDDOWN(([Months Until Expiration]@row + MONTH([Date Contract Executed]@row)) / 12.00001, 0), ROUNDUP(MOD([Months Until Expiration]@row + MONTH([Date Contract Executed]@row), 12.00001), 0), DAY([Date Contract Executed]@row))

  • @Leibel S

    Ah, that works great! Thank you!

    Revised formula to the highlighted cell below.

  • Paul Newcome
    Paul Newcome Community Champion

    @Leibel S I like how you used the 12.0001 in the MOD function. I hadn't thought to do that, and it would save me from having to use the IF statements to say that IF the MOD is zero, output 12, otherwise output the MOD. I am going to have to fiddle with mine a bit and see how much that cuts it down.

    Mine is also a little bit overkill for this particular post because it allows for a negative number of months, but copy/paste from something I know works is easier than trying to slim it down. Haha.

  • Leibel S
    Leibel S Community Champion

    @Paul Newcome Thanks. I always try and make it as neat as possible

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!