Skip to main content

Account Renewal Date Function

  • March 27, 2019
  • 5 replies
  • 55 views
M

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!

5 replies

JonathanBowen
Forum|alt.badge.img+18

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

M
  • Max_Murphy
  • Author
  • New Participant
  • March 27, 2019

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

JonathanBowen
M
  • Max_Murphy
  • Author
  • New Participant
  • March 27, 2019

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

JonathanBowen
Forum|alt.badge.img+18

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

M
  • Max_Murphy
  • Author
  • New Participant
  • March 27, 2019

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!

JonathanBowen
Terms & ConditionsAccessibility statement