Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Calculate Renewal Date Using IF

Topic Labels: Formulas
264 1
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

Hey fellow AT users,

I’m trying to set up a formula that calculates the renewal date of subscriptions based on if they are Monthly or Annual. I currently have 3 columns that I’m working with: `Frequency`, `Billing Start Date`, and `Billing Renewal Date` (which is where my formula is).

I was testing the following formula:

``````IF(
{Billing Start Date},
IF(
IS_BEFORE({Billing Start Date}, TODAY()), DATEADD({Billing Start Date}, 1,
SWITCH({Frequency}, "/mo.", 'month', "/yr.", 'year'))
)
)
``````

The issue is that while it DOES show the next date based on the `Frequency` (i.e /mo. or /yr.), it’s not current.

For example, a `Billing Start Date` of August 29, 2020, that’s set to renew monthly, is showing as September 28, 2020 instead of October 28, 2021 (since it’s currently 2021 and September is past).

Does anyone know how I can get this to work? Do I need more columns for date comparisons or can this be done with just the 3 existing columns?

18 - Pluto

To make this work, you need to figure out the existing difference between `TODAY()` and `{Billing Start Date}`, add one, and use that value instead of a literal 1 in the `DATEADD()` function.

You could also simplify the formula a bit by combining the two `IF()` tests into a single one using `AND()`. I also prefer using the less-than operator instead of the `IS_BEFORE()` function, but either method would work.

Here’s how it looks after those changes:

``````IF(
AND({Billing Start Date}, {Billing Start Date} < TODAY()),
{Billing Start Date},
DATETIME_DIFF(
TODAY(),
{Billing Start Date},
SWITCH(
{Frequency}, "/mo.", 'month', "/yr.", 'year'
)
) + 1,
SWITCH(
{Frequency}, "/mo.", 'month', "/yr.", 'year'
)
)
)
``````