Calculate expiration date

Options

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 ✭✭✭✭✭✭
    Answer ✓
    Options

    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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Try this:


    =IFERROR(DATE(YEAR([Original Date]@row) + ROUNDDOWN((MONTH([Original Date]@row) + VALUE([Number Of Months]@row)) / 12, 0) + IF(IF(MOD(MONTH([Original Date]@row) + VALUE([Number Of Months]@row), 12) = 0, 12, MOD(MONTH([Original Date]@row) + VALUE([Number Of Months]@row), 12)) = 12, -1) - IF(AND(ABS(VALUE([Number Of Months]@row)) - MONTH([Original Date]@row) <> 12, VALUE([Number Of Months]@row) < 0, ABS(VALUE([Number Of Months]@row)) > MONTH([Original Date]@row)), 1, 0), IF(MOD(MONTH([Original Date]@row) + VALUE([Number Of Months]@row), 12) = 0, 12, MOD(MONTH([Original Date]@row) + VALUE([Number Of Months]@row), 12)), 1), DATE(IF(MONTH([Original Date]@row) - ABS(VALUE([Number Of Months]@row)) < 1, YEAR([Original Date]@row) - 1, YEAR([Original Date]@row)), IF(MONTH([Original Date]@row) - ABS(VALUE([Number Of Months]@row)) < 1, MONTH([Original Date]@row) + (12 - ABS(VALUE([Number Of Months]@row))), MONTH([Original Date]@row) - ABS(VALUE([Number Of Months]@row))), 1))

  • Deanna Copello
    Options
  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @Deanna Croach

    I hope you're well and safe!

    Did you change the Column names to match yours?

    I hope that helps!

    Be safe, and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Deanna Copello
    Options

    duh. no. Id10T error on my side.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    @Deanna Croach

    Easy to miss!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Yes. My suggestion for changing column names is to copy/paste the formula into a notepad doc the using CTRL+H to find/replace the sample column names with your sheet column names.

  • Deanna Copello
    Options

    Hi again! The formula ALMOST worked. it calculated the month and year correctly, but it defaulted to 01 for the day. how do i change 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)), 1), 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))), 1))

    Disregard the question of the one off months (for internal company discussion)



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    We just need to include the DAY portion in the DATE functions. Sorry about that.


    =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))

  • Deanna Copello
    Options

    hmm, #incorrect argument set......

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

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

  • Deanna Copello
    Options

    works perfectly! thank you!!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!