Formula to indicate 21day prior to renewal

[Tiaan_Terblanch]

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).

[TheTimeSavingCo]

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"
  )
)

[ScottWorld]

@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"
))

[Tiaan_Terblanch]

Adam_TheTimeSavingCo:

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"
  )
)

Hi Adam,

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!

[ScottWorld]

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

[Tiaan_Terblanch]

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.

(IF({Last Renewed}=BLANK(),BLANK(), DATEADD({Last Renewed},12,“months”))

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

[Justin_Barrett]

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"))

Tiaan_Terblanche:

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

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}),
  DATEADD(
    {Last Renewed},
    SWITCH({Renewal Period}, "Yearly", 12, "Quarterly", 3),
    "months"
  )
)

Will that work?