Conditional Formating with IF and AND formula

[murkaboris]

Hello:
I'm trying to apply conditional formatting depending on where the value
falls within a date range.

If cell S2 is < 06/28/09 - color the cell in red
If cell S2 is >= 09/28/09 - leave the cell without any color
If cell S2 is >= 06/29/09 and <= 09/27/09 color the cell in green.

I've tried to put it into conditional formatting but it changes the color of
every cell to red. Here is what I did, please advise, I think it must
something to do with date...

Formula is =S2<"06/28/09" (format red)
Formula is =S2>="09/28/09" (format none)
Formula is =AND(S2>="06/29/09",S2<="09/27/09") (format green)

Please help.
Monika

 

[Luke M]

Your structure is good, is just the way you refer to dates. As is, XL thinks
6/28/09 is text, and thus can't compare to it.


Formula is =S2<=DATEVALUE("06/28/09") (format red)
Formula is =AND(S2>=DATEVALUE("06/29/09"),S2<=DATEVALUE("09/27/09"))
(format green)

Note that there is no need for 2nd formula of "no format".

 

[murkaboris]

Awesome, awesome, awesome!!!
Thank you, worked like a charm.
Excel is so picky abou the dates .
Thanks again.
Monika