RENEWAL DATE FORMULA

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

I am happy to help!!

All comments

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

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

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?

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.

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.

Wow wow wow!!!

Thank u very much!

You are great!

You saved my day!

I wish you the best!!