Conditional Formatting - Red/Yellow/Green based on values in other cells

[john_luczkowski]

So here's what I'm trying to do....

[TABLE="width: 539"]
<colgroup><col span="2"><col span="2"><col span="2"><col></colgroup><tbody>[TR]
[TD]Task[/TD]
[TD]Status[/TD]
[TD]Begin Date[/TD]
[TD]Red Date[/TD]
[TD]Yellow Date[/TD]
[TD]Target Completion Date[/TD]
[TD]Actual Completion Date[/TD]
[/TR]
[TR]
[TD]task 1[/TD]
[TD][/TD]
[TD="align: right"]12/13/2016[/TD]
[TD="align: right"]12/20/2016[/TD]
[TD="align: right"]12/13/2016[/TD]
[TD="align: right"]2/17/2016[/TD]
[TD="align: right"]12/30/2016[/TD]
[/TR]
[TR]
[TD]task 2[/TD]
[TD][/TD]
[TD="align: right"]12/23/2017[/TD]
[TD="align: right"]1/30/2017[/TD]
[TD="align: right"]1/23/2017[/TD]
[TD="align: right"]2/17/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]task 3[/TD]
[TD][/TD]
[TD="align: right"]12/20/2016[/TD]
[TD="align: right"]1/3/2017[/TD]
[TD="align: right"]12/27/2016[/TD]
[TD="align: right"]2/17/2016[/TD]
[TD="align: right"]12/30/2016[/TD]
[/TR]
</tbody>[/TABLE]



I want to fill in Column B in each row (Status) with a color - Green, Yellow, or Red

Green - either the Task has been completed (so there's a value in Column G - Actual Completion Date, or there's no data there but today's date is before the date in column E (Yellow date)

Yellow Date - No info in G, but today is after Yellow date (column E), but before Red date,

Red Date - No info in G, and today is after red date


I'm pretty good with using nested formulas, but I'm not sure if I should write 3 separate rules and how that would work to check each row on it's own conditions or if I should use the drop-down using a 3 color scale.

Haven't been able to get either option to work properly. Was trying for green value - (OR($G2="*",AND($G2<>"*",$E2<Today())))

And that's based on me looking up that "*" could represent "any text" - and today needing a () after it

Any direction or support would be appreciated

 

[etaf]

hasn't this already been answered ??? i'm sure i have answered
must be an error in the database update just recently

Green - either the Task has been completed (so there's a value in Column G - Actual Completion Date, or there's no data there but today's date is before the date in column E (Yellow date)

=OR( G2 < > "", today() < E2)

Yellow Date - No info in G, but today is after Yellow date (column E), but before Red date,

=AND( G2 = "" , today() > E2 )

Red Date - No info in G, and today is after red date

=AND ( G2 = "" , today() > D2 )