Calculate next renewal date

Get Help
1

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?

2

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. :wink:

I’m sure @Chr1sG can confirm if there is a way to use modulo in the formula. :slight_smile:

1 Like
3

Have you seen this?

I think it could be adapted to achieve what you need

4

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

image

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?

5

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

6

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.

7

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.

8

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. :wink:

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. :slightly_smiling_face:
(And yes, that will have implications on stability/security/etc.)

1 Like
9

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.