Account Renewal Date Function

[Max_Murphy]

Hello!

What I need:

For a field to update automatically depending on the number of days remaining in a clients account. For example, if the account ends at the end of the month 4/30 - I would want a “Renewals” field to say NEEDS RENEWAL in Red if the date is less than 14 days away.

What I have done:

I cannot figure out how to write this formula within one field. So what I have done is set up another field that puts the function - DATETIME_DIFF({End},TODAY(),‘days’) which displays the number of days until the account needs to be renewed, and then in the Renewal field, I put the statement IF({Renewal Date Parse}>14, “”, “NEEDS RENEWAL”).

I would like to know if I have over complicated this and if it could be put within one field to decrease redundancy.
Here is an image for concept.

Thanks so much!

[JonathanBowen]

Hi @Max_Murphy - you’re practically there, just need to combine the two formulas you already have:

IF(DATETIME_DIFF({End}, TODAY(), ‘days’) > 14, “”, “Needs Renewal”)

JB

[Max_Murphy]

This did it! Thank you! I didnt know I could combine them like that… I’m pretty new at conditional logic. Thanks for your time.
Max

[Max_Murphy]

Would I be able to add on to this by making the “Needs Renewal” notification disappear in the field after a certain time frame? E.x.10 days past due - and “archived” or just blank?

For example, if I had a field labeled “Status” which contained single selections for Active, Pending, Hold, & Archive, could I either set this Renewals formula to only be true for those in the ‘Active’ category, or rather, could I make the ‘Needs Approval’ record disappear for those who are ‘Archived’?

[JonathanBowen]

To answer the first part, you can just use a nested IF formula:

IF(
DATETIME_DIFF({End}, TODAY(), “days”) < -10,
“Archived”,
IF(DATETIME_DIFF({End}, TODAY(), “days”) < 0, “Needs Renewal”, “”)
)

So… if end date is more than 10 days ago, show “archived”, otherwise, if end date is prior to today show “needs renewal”, otherwise show “”

JB

[Max_Murphy]

Perfect. I just applied more nested conditions based on different time frames to correlate which status the account was in.

Thanks JB!