Using IF with RYG formula

styless

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!

James McCombs

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

Thank you so much. It worked like a charm!