# Calculate next renewal date

I am trying to create a formula to calculate the next renewal date for subscriptions. We have seen something similar in the Subscription Tracking template, but we need more flexibility to create a more sustainable solution.

What we would like:

• Such as a renewal every X-months (instead of only annual or monthly).
• And the automation used in the template is quite fixed. For example, an annual update is +365, but sometimes a year has 366 days.

So what we want to achieve is the following.

• We have a start date and we have a âContract in months.â
• Where âContract in monthsâ is a number field, where the number of months can be anything.

Based on the given information, we want to calculate (via a formula) when the next renewal date is.

Example 1:

• Start date = November 3, 2022, Contract in months = 12
• The output should be â November 3, 2022

Example 2

• Start date = August 1, 2023 and Contract in Months = 30
• The output should be â February 1, 2026 (so 2,5 years later).

Who knows the right formula?

Hi @Marloes,

What you want to do requires the modulo (`%`) math function and I donât think Fibery has that yet.

With a modulo function, this should do the trick:

``````Date(Year(StartDate) + RoundDown(DeltaMonths / 12, 0), Month(StartDate) + (Month(Date) + Months) % 12, Day(StartDate))
``````

And, if you feel fancy, youâll add a `- Days(1)` at the end to end the day before it started.

Iâm sure @Chr1sG can confirm if there is a way to use modulo in the formula.

1 Like

Have you seen this?

I think it could be adapted to achieve what you need

If I use this formula and modify this a bit, I receive the following feedback:

This is the formula, where âStartdatumâ is a date field and â[Contract in maanden]â is a number field:

``````Date(
Year(Startdatum) +
RoundDown((Month(Startdatum) + [Contract in maanden] - 1) / 12, 0),
Month(Startdatum) +
[Contract in maanden] -
12 * RoundDown((Month(Startdatum) + [Contract in maanden] - 1) / 12, 0),
Least(
Day(
Date(
Year(Startdatum) +
RoundDown((Month(Startdatum) + [Contract in maanden] - 1) / 12, 0),
Month(Startdatum) +
[Contract in maanden] +
1 -
12 * RoundDown((Month(Startdatum) + [Contract in maanden]) / 12, 0),
1
) - Days(1)
),
Day(Startdatum)
)
)
``````

What should I do to make it work?

Thanks for your help! The % function is indeed not possible (yet?) in Fibery.

As the error message indicates, you need to create a new formula (not modify an existing one).
Once a formula is created, the data type is locked, and from the error message, it looks like youâre trying to modify an existing formula that currently returns an integer.

It is not, yet.
Itâs worth pointing out that @njyo 's formula will give an error in some specific cases, eg. if you added one month to a date which was 31st January, because there is no 31st February.
This is why the lower part of my formula includes the rather complicated `Least(...` calculation.

Itâs explained a bit more in the topic linked to.

Good catch @Chr1sG!
Yes, would need to get the minimum of the days in a month and the day of the start monthâŚ

Guess that makes the case that besides `Days(x)` there should also be a `Months(x)` and a `Years(x)`function besides the `%` operator.

Which, leads to an even bigger topic: User-defined functions. I love that Google Sheets now allows me to define my own functions for reuse, this would be a perfect example of such a function. Have the admin write it once and then itâs always available. This forum can be used to exchange or eventually there could even be a marketplace.
(And yes, that will have implications on stability/security/etc.)

1 Like

Good to know!

Still the formula didnât work at once. But updating the date values triggered the formula to calculate. Thanks!! Could not have fixed this formula myself.