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
-
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
-
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))
-
didn't work...
-
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.
-
duh. no. Id10T error on my side.
-
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.
-
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.
-
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)
-
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))
-
hmm, #incorrect argument set......
-
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)))
-
works perfectly! thank you!!!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!