Calculate expiration date

Dependencies are off.

How do I calculate an expiration date from two cells below:

Contract term in months (drop down menu 12, 24, 36, 60)

Contract begin date

Need calculation is Contract Expire Date

Thank you!

Best Answer

  • Paul Newcome
    Paul Newcome Community Champion
    Answer βœ“

    Sorry about that. Leave it to me to miss a closing parenthesis. Try this...


    =IFERROR(DATE(YEAR([Contract Start Date]@row) + ROUNDDOWN((MONTH([Contract Start Date]@row) + VALUE([Contract Term in Months]@row)) / 12, 0) + IF(IF(MOD(MONTH([Contract Start Date]@row) + VALUE([Contract Term in Months]@row), 12) = 0, 12, MOD(MONTH([Contract Start Date]@row) + VALUE([Contract Term in Months]@row), 12)) = 12, -1) - IF(AND(ABS(VALUE([Contract Term in Months]@row)) - MONTH([Contract Start Date]@row) <> 12, VALUE([Contract Term in Months]@row) < 0, ABS(VALUE([Contract Term in Months]@row)) > MONTH([Contract Start Date]@row)), 1, 0), IF(MOD(MONTH([Contract Start Date]@row) + VALUE([Contract Term in Months]@row), 12) = 0, 12, MOD(MONTH([Contract Start Date]@row) + VALUE([Contract Term in Months]@row), 12)), DAY([Contract Start Date]@row)), DATE(IF(MONTH([Contract Start Date]@row) - ABS(VALUE([Contract Term in Months]@row)) < 1, YEAR([Contract Start Date]@row) - 1, YEAR([Contract Start Date]@row)), IF(MONTH([Contract Start Date]@row) - ABS(VALUE([Contract Term in Months]@row)) < 1, MONTH([Contract Start Date]@row) + (12 - ABS(VALUE([Contract Term in Months]@row))), MONTH([Contract Start Date]@row) - ABS(VALUE([Contract Term in Months]@row))), DAY([Contract Start Date]@row)))

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!