# RYG and Gray!

edited 04/20/20

Hey,

I cant figure out how to add to this formula. I have this formula that someone shared from a seminar:

=IF(AND([End Date]@row + 1 < TODAY(), [% Complete]5 <> 1), "Red", IF(AND([End Date]@row -2 < TODAY(), [% Complete]@row <> 1), "Yellow", "Green"))

.

This give me my r,y,g health in the right time frame i want. What I need is to add:

If the "start date" is in the future and the "% complete" is 0, then the health is Gray. ( if the start date is in the future and the "% complete" is more that 0, then it should use the formula above.

.

Thanks in advance for the help

• ✭✭✭✭✭✭

Try something like this...

=IF(AND([Start Date]@row> TODAY(), [% Complete]@row = 0), "Gray", IF(AND([End Date]@row + 1 < TODAY(), [% Complete]5 <> 1), "Red", IF(AND([End Date]@row -2 < TODAY(), [% Complete]@row <> 1), "Yellow", "Green")))

• Hey @Paul Newcome thanks for this. It's really close. The only issue I see is when the "% complete" is 100%, if the "end date" is in the past, I get the red health (but since it's 100% it should be green)

• ✭✭✭✭✭✭

My apologies. I assumed your posted formula was already working and that you just wanted to add in that other little bit. Lets try a little rearranging...

=IF([% Complete]@row = 1, "Green", IF(AND([Start Date]@row> TODAY(), [% Complete]@row = 0), "Gray", IF(AND([End Date]@row + 1 < TODAY(), [% Complete]5 <> 1), "Red", IF(AND([End Date]@row -2 < TODAY(), [% Complete]@row <> 1), "Yellow"))))

• Hey @Paul Newcome I'm sorry. My Bad. I hadn't tested that scenario until I tried your formula. You are so right, it wasn't working lol.

.

So this one is close as well. The yellow and red health seems to work but now i'm not getting any health when the start date and/or end date is +2 days past today (unless the % complete is 100) Ex. SD= 4/13, ED= 4/23, Any % complete (except 100%) yields nothing for health

the only time I get a "green" health is when the % complete =100. Ex. SD=4/13, ED=4/27, %C= 5% yields no health like above

.

FYI - I really appreciate your (anyones) help, would it be easier if I said what I want the end result to be? or can it be figured out from the initial formula I posted?

• Hello everyone!

Thanks @Paul Newcome! does anyone have any help for me? we are so close to fixing this formula.

Thanks!

• ✭✭✭✭✭✭

What is the exact criteria list for the formula? If you are able to spell it out in a written manner, that would make it much easier to build out a formula. An example for what I mean is...

Green = [% Complete] is 100%

Red = [End Date] is in the past

Yellow = [End Date] is in the next three days

etc

etc

• @Paul Newcome Ok here's a go!

Green:

-      [% Complete] is 100% or

-      [Start Date] > Today and [% Complete] is > 0 (i.e start is in the future and there is some work completed) or

-      [Start Date] < Today and [End Date] > Today +2 (i.e start is in the past and end date is more than 2 days in the future)

Yellow:

-      [End Date] < Today -2 and [% Complete] < 100% (i.e 2 days before the end date through to the end date and completion is less than 100%, its yellow)

Red:

-      [End Date] < today +1 and [% Complete] < 100% (i.e. the end date is in the past and less than 100% is complete)

Gray:

-      [Start Date] > today and [% complete] = 0 (i.e. start date is in the future and no work had been done)

• ✭✭✭✭✭✭

Ok. Let's see what we can do...

=IF(OR([% Complete]@row = 1, AND([Start Date]@row > TODAY(), [% Complete]@row > 0), [End Date]@row > TODAY(2)), "Green", IF([End Date]@row < TODAY(), "Red", IF([End Date]@row <= TODAY(2), "Yellow", "Grey")))

• Hey @Paul Newcome ,

Thank you again. Like 98% perfect! The one detail that doesn't seem to be working is the "gray" functionality. I cant get the light to go gray in any scenario.

Dyon

• ✭✭✭✭✭✭

Hi Dyon,

At a glance, I think it’s the ”Grey” part. Try changing it to ”Gray” instead.

Did that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭

@Paul Newcome Easy to get those mixed up!

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• @Paul Newcome @Andrée Starå thanks! that is 100% working so far. I did try the gray yesterday and that still didnt work but the new tweak does work. thanks so much! I'll try and not bug you for more formulas for forseeable future :-)

• ✭✭✭✭✭✭

Excellent!

Happy to help!

Haha! Feel free to bug me if you need to!

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭

@Dyon Martin What He said... ☝️

Happy to help, and bug away!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!