Conditional Formatting

[Picnic Basket Ninja]

Hello all,
I am in need of some help. I am trying to create a fromula in conditional formatting that will allow me to Highlight a status block (D3) either Red, Yellow, or Green according to the corresponding date in the Next Due Date block (E3). Red being overdue (ie Todays date), Yellow being comming due soon (ie 14 days out from todays date), and Green being not either of the above, or Good (ie greater than 14 days out to whatever date is in the block). Any help would be appriciated.

 

[jeffreybrown]

Hello & Welcome to the Board,

See if this helps cf

 

[Picnic Basket Ninja]

There is a 3 color CF in there, but I tried them and it won't work.

 

[jeffreybrown]

Try these conditions and in order

=E3=TODAY() >> Red

=AND(E3<=TODAY()+14,E3<>"") >> Yellow

=E3>TODAY()+14 >> Green

 

[Picnic Basket Ninja]

The Yellow and Green work, however the Red doesn't. Not sure why.

 

[Tigs81]

It might have something to do with a date and time issue. Just a guess.
Does the date cell have a time attached to it.

 

[jeffreybrown]

Maybe you can post some data showing what works and what doestn't.

What is in the cells?

 

[AlphaFrog]

You could use the INT function to strip the time component from the cell values.

=INT(E3)=TODAY() >> Red

=AND(INT(E3)<=TODAY()+14,E3<>"") >> Yellow

=INT(E3)>TODAY()+14 >> Green