# Formula to indicate 21day prior to renewal

Topic Labels: Formulas
1857 6
cancel
Showing results for
Did you mean:
6 - Interface Innovator

We have the following columns in our database:

Date Joined - regular date
Last renewed - regular date
Next Renewal - formula (IF({Last Renewed}=BLANK(),BLANK(), DATEADD({Last Renewed},12,“months”))

However, I also need to include my month-to-month memberships. How do I add it to this formula?

Status of Renewal - The formula should help us calculate the Status of Renewal field as follows:

• IT SHOULD SHOW: “Active ” if the Renewal Date is in the future with 22 days and longer from TODAY.
• IT SHOULD SHOW “Close To Renewal ” if the Renewal Date is 21 days from TODAY (the current date).
• IT SHOULD SHOW: “Needs Renewal ” if the Renewal Date is 22 days passed and longer from TODAY (current date).
6 Replies 6
18 - Pluto

Hi @Tiaan_Terblanche , try this:

``````IF(
DATETIME_DIFF({Next Renewal}, TODAY(), 'days') >= 22,
"Active",
IF(
AND(
DATETIME_DIFF({Next Renewal}, TODAY(), 'days') >= -22,
DATETIME_DIFF({Next Renewal}, TODAY(), 'days') <= 21
),
"Close To Renewal",
"Needs Renewal"
)
)
``````

18 - Pluto

@Tiaan_Terblanche

Adam’s formula can be simplified to this, and my guess is that you wanted a zero for the second IF statement:

``````IF(
DATETIME_DIFF({Next Renewal}, TODAY(), 'days') >= 22, "Active",
IF(
DATETIME_DIFF({Next Renewal}, TODAY(), 'days') >= 0, "Close To Renewal",
"Needs Renewal"
))
``````
6 - Interface Innovator

Thank you, I had to make a small change and now it is working as it should!!

IF(
DATETIME_DIFF({Next Renewal}, TODAY(), ‘days’) >= 22,
“ :green_circle: Active :green_circle: ”,
IF(
AND(
DATETIME_DIFF({Next Renewal}, TODAY(), ‘days’) >= -0,
DATETIME_DIFF({Next Renewal}, TODAY(), ‘days’) <= 21
),
“ :yellow_circle: Close To Renewal :yellow_circle: ”,
“ :red_circle: Needs Renewal :red_circle: ”
)
)

I was wondering if you can help me with one last thing.

I need to distinguish between a month-to-month membership and a yearly membership.

I currently have this formula to calculate a 12 month membership renewal:
Next Renewal - formula (IF({Last Renewed}=BLANK(),BLANK(), DATEADD({Last Renewed},12,“months”))

But I need to add one for 1 month as well. Can I combine it or do I need to add a column or what would be the solution?

Thanks for your help, I really appreciate it!

18 - Pluto

As I mentioned above, the 2nd IF() statement in the formula is unnecessarily long & can be simplified.

6 - Interface Innovator

Hi Scott,

I needed 3 options for the renewal status so the formula that I mentioned to Adam is working perfectly.

However, I need a formula that will calculate a 12 month and a 3 month ‘Next Renewal’ date.

This is what I have, but I need to include the monthly renewal plans as well.

Will you be able to help with that and can I combine the two?

Thanks

``````IF({Last Renewed}=BLANK(),BLANK(), DATEADD({Last Renewed},12, "months"))
``````

First off, this can be greatly simplified. In 99.9999% of cases where you might be tempted to use the `BLANK()` function, it’s not actually necessary. Here are the things to remember:

1. With very few exceptions, an empty field is equivalent to False, and a non-empty field is equivalent to True. This means no need to compare against a literal blank field.
2. The final argument in the `IF()` function is optional. If omitted, the function returns nothing, thereby leaving the field blank.

With that in mind, and knowing that your goal is to only show a new date if there’s a date in `{Last Renewed}`, the above formula can become this:

``````IF({Last Renewed}, DATEADD({Last Renewed},12, "months"))
``````

I’m guessing you have a field that specifies the renewal period for each subscriber (12 months vs 3 months). For the sake of argument, I’ll assume it’s a single-select field with “Yearly” and “Quarterly” options, with a field name of `{Renewal Period}`.

What you need to do is output a number of months based on the setting in that field. If that were all you were doing, you could start like this:

``````IF({Renewal Period} = "Yearly", 12, IF({{Renewal Period} = "Quarterly", 3))
``````

By using the `SWITCH()` function, this could be shortened a touch:

``````SWITCH({Renewal Period}, "Yearly", 12, "Quarterly", 3)
``````

Now you just insert that formula into the place in your original formula where you need that number to go; i.e. in place of the static “12”:

``````IF({Last Renewed}, DATEADD({Last Renewed}, SWITCH({Renewal Period}, "Yearly", 12, "Quarterly", 3), "months"))
``````

As a final failsafe, I recommend adding the `{Renewal Period}` field value as another condition that determines whether or not the new date is calculated; i.e. you only want a new date if the record has a previous renewal date and a renewal period. Here’s that update:

``````IF(AND({Last Renewed}, {Renewal Period}), DATEADD({Last Renewed}, SWITCH({Renewal Period}, "Yearly", 12, "Quarterly", 3), "months"))
``````

And because it’s getting tough to see all at once, here’s the same thing spread across multiple lines:

``````IF(
AND({Last Renewed}, {Renewal Period}),