# 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!

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

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

• Options

didn't work...

• ✭✭✭✭✭✭
Options

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.

• Options

duh. no. Id10T error on my side.

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
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.

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

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

• Options

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

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

• Options

works perfectly! thank you!!!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!