Calculate Renewal Date Using IF

[Amber]

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?

[Justin_Barrett]

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()),
    DATEADD(
        {Billing Start Date},
        DATETIME_DIFF(
            TODAY(),
            {Billing Start Date},
            SWITCH(
                {Frequency}, "/mo.", 'month', "/yr.", 'year'
            )
        ) + 1,
        SWITCH(
	        {Frequency}, "/mo.", 'month', "/yr.", 'year'
        )
    )
)