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.