Forum Discussion

JoanneWong's avatar
JoanneWong
Qrew Member
5 years ago

If...then...condition

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
------------------------------
Formulas and functions
  • PaulPeterson1's avatar
    PaulPeterson1
    Qrew Assistant Captain
    5 years ago
    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's avatar
    JoanneWong
    Qrew Member
    5 years ago
    Thanks Paul! It works!

    ------------------------------
    Joanne Wong
    ------------------------------