How do I calculate an expiration date from a specific date column

I want to use the date in Column A plus "X" months so that Column B populates a new future date. What's the syntax I can copy/paste into Column B?

Example -

Column A (Contract Effective Date) - 5/29/2020

Column B (Contract Expires on Date) in 4 month. Should = 9/29/2020

***"X" months can change as it may not be the same every time. I think I will be updating the formula every time.

Tags:

Answers

  • Hi Debra. If you create a column for the contract length in days, the formula in B would be =[Effective Date]1 + [Contract Length]1. Viv

  • Werner Gerstacker
    Werner Gerstacker ✭✭✭✭✭

    Hi @Debra Humes


    this will work for contract durations that do NOT go beyond the end of next year:

    =DATE(IF((MONTH(Created@row) + Duration@row) < 13, YEAR(Created@row), YEAR(Created@row) + 1), IF((MONTH(Created@row) + Duration@row) < 13, MONTH(Created@row) + Duration@row, MONTH(Created@row) + Duration@row - 12), DAY(Created@row))


    The start date is in the 'Created Column' and the contract lengths (in FULL months) is in the column called 'Duration'.

    Keep in mind that X months from now might be a weekend or holiday.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!