RENEWAL DATE FORMULA
Hello community!
I am an insurance agent and i would like a formula that shows the renewal date of my clients' contracts.
For example, when a contract's duration is 1 year (e.g. date of first contract 31/01/2024) i would like the renewal date to be automatically calculated +1 year (e.g. 31/01/2025).
I have used this formula that works fine for 1 year duration contracts.
=DATE(YEAR([FIRST CONTRACT]@row) + 1; MONTH([FIRST CONTRACT]@row); DAY([FIRST CONTRACT]@row))
a. Is there a way when the date of first contract is for example 31/8/2024 and the duration is 6 months to calculate it correctly (31/2/2025 is obviously a mistake)?
b. Is there a better way to calculate with an 'IF' formula since the duration options are 1, 3, 6 ,12 months (or 30, 90, 180 and 360 days)?
Could you please help me out with this?
Thank u very much!
Best Answers

After a long struggle with the formulas, here is the result 😅. Hope it works for you this time.
It is a long and complicated formula that is combined from some helper columns. I don't think you will understand it.
=IF((MONTH([Begin Date]@row) + MOD([Duration (months)]@row; 12)) <= 12; IF(DAY([Begin Date]@row) > DAY(IF(MONTH([Begin Date]@row) + MOD([Duration (months)]@row; 12) + 1 > 12; DATE(YEAR([Begin Date]@row) + ROUNDDOWN([Duration (months)]@row / 12); 1; 1)  1; DATE(YEAR([Begin Date]@row) + ROUNDDOWN([Duration (months)]@row / 12); MONTH([Begin Date]@row) + MOD([Duration (months)]@row; 12) + 1; 1)  1)); IF(MONTH([Begin Date]@row) + MOD([Duration (months)]@row; 12) + 1 > 12; DATE(YEAR([Begin Date]@row) + ROUNDDOWN([Duration (months)]@row / 12); 1; 1)  1; DATE(YEAR([Begin Date]@row) + ROUNDDOWN([Duration (months)]@row / 12); MONTH([Begin Date]@row) + MOD([Duration (months)]@row; 12) + 1; 1)  1); DATE(YEAR([Begin Date]@row) + ROUNDDOWN([Duration (months)]@row / 12); MONTH([Begin Date]@row) + MOD([Duration (months)]@row; 12); DAY([Begin Date]@row))); IF(DAY([Begin Date]@row) > DAY(DATE(YEAR([Begin Date]@row) + ROUNDUP([Duration (months)]@row / 12); MONTH([Begin Date]@row) + MOD([Duration (months)]@row; 12)  11; 1)  1); DATE(YEAR([Begin Date]@row) + ROUNDUP([Duration (months)]@row / 12); MONTH([Begin Date]@row) + MOD([Duration (months)]@row; 12)  11; 1)  1; DATE(YEAR([Begin Date]@row) + ROUNDUP([Duration (months)]@row / 12); MONTH([Begin Date]@row) + MOD([Duration (months)]@row; 12)  12; DAY([Begin Date]@row))))
Gia Thinh Technology  Smartsheet Solution Partner.

I am happy to help!!
Gia Thinh Technology  Smartsheet Solution Partner.
Answers

Hi John,
You can use the formula below to calculate the End Date based on the Begin Date and Duration in months for a general use case. I hope it helps!
=IF((MONTH([Begin Date]@row) + MOD([Duration (months)]@row, 12)) <= 12, DATE(YEAR([Begin Date]@row) + ROUNDDOWN([Duration (months)]@row / 12), MONTH([Begin Date]@row) + MOD([Duration (months)]@row, 12), DAY([Begin Date]@row)), DATE(YEAR([Begin Date]@row) + ROUNDUP([Duration (months)]@row / 12), MONTH([Begin Date]@row) + MOD([Duration (months)]@row, 12)  12, DAY([Begin Date]@row)))
Gia Thinh Technology  Smartsheet Solution Partner.

First of all, thank u very much for your response!
It doesn't seem to work in my case.
I have selected DATE in all cells..don't really know if i should!
Maybe it is because i have the 31/08/24 format instead of the 08/31/24 you are proposing.
Could it be the problem?
And also, in your example, when the begin date is 12/30/24 and the duration is 2 months, then the end date is 03/02/25. Actually, it isn't the right duration for my case! The right one in this example is 02/28/25 (or 02/29/25 if it was a leap year)!!
Any chance of getting it right?
Thanks again!

First of all, thank u very much for your response!
This doesn't seem to work in my case.
Maybe because i am using the 31/08/24 format, instead of the 08/31/24 you are using..could it be the problem?
I have chosen the DATE in all three columns.
Also, in your example, when the begin date is 12/30/24 and the duration is 2 months, then the end date is 03/02/25. This isn't right in my case, since the right one would be 02/28/25 (or 02/29/25 if it was a leap year).
Any chance of getting it right?
Thanks again!

I changed my personal setting to get the date format (dd/mm/yyyy) as yours and the formula still works well.
Can you provide a screenshot of your sheet that shows 3 columns and the error (if any).
Begin Date: 30/12/2024 + 2 months = 30/02/2025.
Since February 2025 ends on 28/02/2025, the extra 2 days (30/02  28/02) will automatically be added to 28/02/2025, resulting in an End Date of 02/03/2025.
The same logic applies for Begin Date: 31/08/2024 + 6 months = 31/02/2025, which results in 03/03/2025.
I modified the formula a litte bit (change the "," to ";") to apply for your regional as below. Try it again :
=IF((MONTH([Begin Date]@row) + MOD([Duration (months)]@row; 12)) <= 12; DATE(YEAR([Begin Date]@row) + ROUNDDOWN([Duration (months)]@row / 12); MONTH([Begin Date]@row) + MOD([Duration (months)]@row; 12); DAY([Begin Date]@row)); DATE(YEAR([Begin Date]@row) + ROUNDUP([Duration (months)]@row / 12); MONTH([Begin Date]@row) + MOD([Duration (months)]@row; 12)  12; DAY([Begin Date]@row)))
Gia Thinh Technology  Smartsheet Solution Partner.

Thanks!
It now works just fine!
The problem remains of course, since an insurance contract with e.g. begin date 31/08/2024 + 6 months should end on 28/02/2025 and not on 03/03/2025..if it ended on 03/03/2025 then the next renewal should be on 03/09/2025 and this isn't right..don't really know if this can be fixed somehow!
Thank u very much again!


This issue isn't limited to February, it can also occur with other months (1,3,5,7,8,10,12), as shown below.
For example:
Begin Date: 31/01/2024 + 3 months cannot be 31/04/2024, but will be 01/05/2024. I assume you’d prefer it to be 30/04/2024, correct?
Gia Thinh Technology  Smartsheet Solution Partner.

Yes, exactly!
It can occur in many other months.
In your example (Begin Date: 31/01/2024 + 3 months cannot be 31/04/2024, but will be 01/05/2024. I assume you’d prefer it to be 30/04/2024, correct?) you are perfectly right!
30/04/2024 is the perfect answer!

This will take time to modify the formula. Let's see.
Gia Thinh Technology  Smartsheet Solution Partner.

Thank u very much for your time!
Thought it would be a little easier..sorry about that!

After a long struggle with the formulas, here is the result 😅. Hope it works for you this time.
It is a long and complicated formula that is combined from some helper columns. I don't think you will understand it.
=IF((MONTH([Begin Date]@row) + MOD([Duration (months)]@row; 12)) <= 12; IF(DAY([Begin Date]@row) > DAY(IF(MONTH([Begin Date]@row) + MOD([Duration (months)]@row; 12) + 1 > 12; DATE(YEAR([Begin Date]@row) + ROUNDDOWN([Duration (months)]@row / 12); 1; 1)  1; DATE(YEAR([Begin Date]@row) + ROUNDDOWN([Duration (months)]@row / 12); MONTH([Begin Date]@row) + MOD([Duration (months)]@row; 12) + 1; 1)  1)); IF(MONTH([Begin Date]@row) + MOD([Duration (months)]@row; 12) + 1 > 12; DATE(YEAR([Begin Date]@row) + ROUNDDOWN([Duration (months)]@row / 12); 1; 1)  1; DATE(YEAR([Begin Date]@row) + ROUNDDOWN([Duration (months)]@row / 12); MONTH([Begin Date]@row) + MOD([Duration (months)]@row; 12) + 1; 1)  1); DATE(YEAR([Begin Date]@row) + ROUNDDOWN([Duration (months)]@row / 12); MONTH([Begin Date]@row) + MOD([Duration (months)]@row; 12); DAY([Begin Date]@row))); IF(DAY([Begin Date]@row) > DAY(DATE(YEAR([Begin Date]@row) + ROUNDUP([Duration (months)]@row / 12); MONTH([Begin Date]@row) + MOD([Duration (months)]@row; 12)  11; 1)  1); DATE(YEAR([Begin Date]@row) + ROUNDUP([Duration (months)]@row / 12); MONTH([Begin Date]@row) + MOD([Duration (months)]@row; 12)  11; 1)  1; DATE(YEAR([Begin Date]@row) + ROUNDUP([Duration (months)]@row / 12); MONTH([Begin Date]@row) + MOD([Duration (months)]@row; 12)  12; DAY([Begin Date]@row))))
Gia Thinh Technology  Smartsheet Solution Partner.


I am happy to help!!
Gia Thinh Technology  Smartsheet Solution Partner.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 348 Global Discussions
 199 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 455 Show & Tell
 28 Member Spotlight
 1 SmartStories
 282 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!