Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Oct 04, 2022 05:01 AM
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:
Oct 04, 2022 05:45 AM
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"
)
)
Oct 04, 2022 06:14 AM
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"
))
Oct 04, 2022 06:38 AM
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!
Oct 04, 2022 07:32 AM
As I mentioned above, the 2nd IF() statement in the formula is unnecessarily long & can be simplified.
Oct 04, 2022 07:52 AM
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
Oct 04, 2022 10:40 PM
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:
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}),
DATEADD(
{Last Renewed},
SWITCH({Renewal Period}, "Yearly", 12, "Quarterly", 3),
"months"
)
)
Will that work?