Using IF with RYG formula

styless · 2019-08-28T14:11:16+00:00

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…

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!

Find more posts tagged with

Formulas

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

How to split contents from a cell from a smartsheet into another smartsheet
I need to extract and split the contents of a cell in a Smartsheet into three separate columns to a new smartsheet. The cell entries are originally submitted through a form.
Information in a form populates specific cell.
Hello, Just investigating an idea. I'm building a staff register sheet. I'm curious to know if its possible to populate specific cells by the answers given in a form? So if, in a form, a staff member gives their name, the date and that they will be on leave, sick, training ect. Once the form is submitted, it will populate…
A multiple SUMIF function
My current formula is below. I would like to update this so that I only get a total of those that have been Quoted and have a timeframe data of 2025. How do I add this extra filter into the formula? =SUMIF(Status:Status, "Quoted", [Est. GP]:[Est. GP])