# Calculate next renewal date **URL:** \ **Category:** Get Help\ **Created:** [July 19, 2023, 1:38pm UTC](https://community.fibery.io/t/calculate-next-renewal-date/4810 "2023-07-19T13:38:04Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![Marloes](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/marloes/32/5926_2.png) [@Marloes](https://community.fibery.io/u/Marloes)\ **Post date:** [July 19, 2023, 1:38pm UTC](https://community.fibery.io/t/calculate-next-renewal-date/4810/1 "2023-07-19T13:38:04Z")
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? ---
**Author:** ![njyo](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/njyo/32/923_2.png) [@njyo](https://community.fibery.io/u/njyo)\ **Post date:** [July 19, 2023, 4:16pm UTC](https://community.fibery.io/t/calculate-next-renewal-date/4810/2 "2023-07-19T16:16:52Z")
Hi @Marloes, What you want to do requires the [modulo (`%`) math function](https://en.wikipedia.org/wiki/Modulo) and I don’t think Fibery has that yet. With a modulo function, this should do the trick: ```auto 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. 🙂 ---
**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\ **Post date:** [July 19, 2023, 8:05pm UTC](https://community.fibery.io/t/calculate-next-renewal-date/4810/3 "2023-07-19T20:05:11Z")
Have you seen this? > [@Add a number of months to a date](https://community.fibery.io/t/add-a-number-of-months-to-a-date/4695/5): > > Something like this should work Date( Year(CurrentDate) + RoundDown((Month(CurrentDate) + IntervalPeriod - 1) / 12, 0), Month(CurrentDate) + IntervalPeriod - 12 \* RoundDown((Month(CurrentDate) + IntervalPeriod - 1) / 12, 0), Least( Day( Date( Year(CurrentDate) + RoundDown((Month(CurrentDate) + IntervalPeriod - 1) / 12, 0), Month(CurrentDate) + IntervalPeriod + 1 - 12 \* RoundDown((Month(CurrentDate) + IntervalPer… I think it could be adapted to achieve what you need ---
**Author:** ![Marloes](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/marloes/32/5926_2.png) [@Marloes](https://community.fibery.io/u/Marloes)\ **Post date:** [July 20, 2023, 9:13am UTC](https://community.fibery.io/t/calculate-next-renewal-date/4810/4 "2023-07-20T09:13:25Z")
If I use this formula and modify this a bit, I receive the following feedback: ![image](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/c/c8e81860284bc3b6180a27a84b7ad2d24f573890.png) This is the formula, where ‘Startdatum’ is a date field and ‘[Contract in maanden]’ is a number field: ```auto 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? ---
**Author:** ![Marloes](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/marloes/32/5926_2.png) [@Marloes](https://community.fibery.io/u/Marloes)\ **Post date:** [July 20, 2023, 9:16am UTC](https://community.fibery.io/t/calculate-next-renewal-date/4810/5 "2023-07-20T09:16:00Z")
> [@njyo](#): > > `Date(Year(StartDate) + RoundDown(DeltaMonths / 12, 0), Month(StartDate) + (Month(Date) + Months) % 12, Day(StartDate))` Thanks for your help! The % function is indeed not possible (yet?) in Fibery. ---
**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\ **Post date:** [July 20, 2023, 10:17am UTC](https://community.fibery.io/t/calculate-next-renewal-date/4810/6 "2023-07-20T10:17:36Z")
> [@Marloes](#): > > ![image](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/c/c8e81860284bc3b6180a27a84b7ad2d24f573890.png) 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. ---
**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\ **Post date:** [July 20, 2023, 10:20am UTC](https://community.fibery.io/t/calculate-next-renewal-date/4810/7 "2023-07-20T10:20:45Z")
> [@Marloes](#): > > > [@njyo](#): > > > > `Date(Year(StartDate) + RoundDown(DeltaMonths / 12, 0), Month(StartDate) + (Month(Date) + Months) % 12, Day(StartDate))` > > Thanks for your help! The % function is indeed not possible (yet?) in Fibery. 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. ---
**Author:** ![njyo](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/njyo/32/923_2.png) [@njyo](https://community.fibery.io/u/njyo)\ **Post date:** [July 21, 2023, 5:57am UTC](https://community.fibery.io/t/calculate-next-renewal-date/4810/8 "2023-07-21T05:57:45Z")
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.) ---
**Author:** ![Marloes](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/marloes/32/5926_2.png) [@Marloes](https://community.fibery.io/u/Marloes)\ **Post date:** [July 21, 2023, 6:49am UTC](https://community.fibery.io/t/calculate-next-renewal-date/4810/9 "2023-07-21T06:49:44Z")
> [@Chr1sG](#): > > 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. 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.