Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Account Renewal Date Function

Topic Labels: Formulas
693 5
cancel
Showing results for 
Search instead for 
Did you mean: 

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.
AT-Renewals-Formula

Thanks so much!

5 Replies 5

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

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

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

Thanks JB!