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

[john_luczkowski]

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

Task	Status	Begin Date	Red Date	Yellow Date	Target Completion Date	Actual Completion Date
task 1		12/13/2016	12/20/2016	12/13/2016	2/17/2016	12/30/2016
task 2		12/23/2017	1/30/2017	1/23/2017	2/17/2016
task 3		12/20/2016	1/3/2017	12/27/2016	2/17/2016	12/30/2016

<colgroup><col span="2"><col span="2"><col span="2"><col></colgroup><tbody>
</tbody>

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 )