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.
Start date = November 3, 2022, Contract in months = 12
The output should be → November 3, 2022
Start date = August 1, 2023 and Contract in Months = 30
The output should be → February 1, 2026 (so 2,5 years later).
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.
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.)