RYG and Gray!

Dyon Martin

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

Paul Newcome

Ugh. I always do that. Yes. "Gray" instead of "Grey". I also threw in a couple of tweaks.

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

Paul Newcome

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")))

Dyon Martin

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)

Paul Newcome

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"))))

Dyon Martin

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?

Dyon Martin

Hello everyone!

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

Thanks!

Paul Newcome

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

Dyon Martin

@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)

Paul Newcome

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")))

Dyon Martin

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

Andrée Starå

@Dyon Martin

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

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Paul Newcome

Ugh. I always do that. Yes. "Gray" instead of "Grey". I also threw in a couple of tweaks.

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

Andrée Starå

@Paul Newcome Easy to get those mixed up!

Dyon Martin

@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 :-)

Andrée Starå

@Dyon Martin

Excellent!

Happy to help!

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

Paul Newcome

@Dyon Martin What He said... ☝️

Happy to help, and bug away!