RENEWAL DATE FORMULA

JOHN ATHANASIOU

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!

Gia Thinh

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

I am happy to help!!

Gia Thinh

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

JOHN ATHANASIOU

JOHN ATHANASIOU

@Gia Thinh

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!

JOHN ATHANASIOU

@Gia Thinh

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!

Gia Thinh

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

JOHN ATHANASIOU

@Gia Thinh

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!

Gia Thinh

Gia Thinh

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?

JOHN ATHANASIOU

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!

Gia Thinh

This will take time to modify the formula. Let's see.

JOHN ATHANASIOU

Thank u very much for your time!

Thought it would be a little easier..sorry about that!

Gia Thinh

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

JOHN ATHANASIOU

@Gia Thinh

Wow wow wow!!!

Thank u very much!

You are great!

You saved my day!

I wish you the best!!

Gia Thinh

I am happy to help!!