Skip to main content
Question

If...then...condition

  • June 16, 2020
  • 0 replies
  • 20 views
Hi, I'm working on a formula which will give me the follow:

If the field "When does it expire" equals to or more than 90 days from the expiration date, the field "status" will change to green with text "OK"; if it equals to or less than 90 days from the expiration date, the "status" will change to yellow with text "Renew"; if it passes the expiration date, the "status" will change to red with text "Expired". The "When does it expire" field is Date and the "Status" field is a rich text formula. Any tips on how to write this formula would be greatly appreciated!!

Thank you 🙏 !!!

------------------------------
Joanne Wong
------------------------------
This topic has been closed for replies.

Try these as a starting point:

Status:

If([Expiration Date] < Today(), "Expired", ToDays([Expiration Date] - Today()) <= 90, "Renew", "Ok")

Rich Text field:
var text Danger = "<a style=\" text-decoration: none; background: #FF0000; border-radius: 5px; color: #fff; display: inline-block; width: 75px; text-align: center; padding: 8px 5px; font: normal 700 14px/1 \"Calibri\", sans-serif; text-shadow: none; \">Exipired</a>";
var text Warning = "<a style=\" text-decoration: none; background: #FFFF00; border-radius: 5px; color: #000; display: inline-block; width: 75px; text-align: center; padding: 8px 5px; font: normal 700 14px/1 \"Calibri\", sans-serif; text-shadow: none; \">Renew</a>";
var text Success = "<a style=\" text-decoration: none; background: #008000; border-radius: 5px; color: #fff; display: inline-block; width: 75px; text-align: center; padding: 8px 5px; font: normal 700 14px/1 \"Calibri\", sans-serif; text-shadow: none; \">Ok</a>";


If([Status] = "Expired", $Danger, [Status] = "Renew", $Warning, $Success)

------------------------------
Paul Peterson
------------------------------
    • JoanneWong
    • Author
    • Registered
    • June 16, 2020
    Thanks Paul! It works!

    ------------------------------
    Joanne Wong
    ------------------------------
    Terms & ConditionsAccessibility statement