Using IF with RYG formula

styless
styless ✭✭✭✭
edited 12/09/19 in Formulas and Functions

Hello I am trying to build a formula where:

  • If the "Date Entered" column is within 60 days, the "Req Health" stays Green
  • If it is 90 days or more, Req Health turns Yellow (I got this formula figured out)
  • 125 days or more, Req health turns Red.

Sheet sample: https://app.smartsheet.com/b/publish?EQBCT=a7cfbf9d93ba41c7bccefd8366c8553c

I am just not sure how to combine all the ranges and formulas as I have tried different way and came up with Errors.

Pls help. Thanks in advance!

Tags:

Comments

  • Hey, styless!

     

    I think I've mostly sorted out your issue, but I tweaked the formula a bit because it looks like you had a gap between <60 being Green, but >90 being Yellow. I changed the Yellow value to kick off at 61 days Let me know if this works!

    =IF(TODAY() - [Date Entered]1 <= 60, "Green", IF(AND(TODAY() - [Date Entered]1 <= 124, TODAY() - [Date Entered]1 >= 61), "Yellow", IF(TODAY() - [Date Entered]1 >= 125, "Red", "Null")))

    P.S. I added a "Null" text value, just in case there were any hiccups. If you'd like to get rid of it, just change it to: 

    =IF(TODAY() - [Date Entered]1 <= 60, "Green", IF(AND(TODAY() - [Date Entered]1 <= 124, TODAY() - [Date Entered]1 >= 61), "Yellow", "Red"))

  • styless
    styless ✭✭✭✭

    Thank you so much. It worked like a charm!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!