Skip to main content

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!

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

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


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

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


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’?

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

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


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

Thanks JB!

Terms & ConditionsAccessibility statement