Conditional Formating with IF and AND formula
 

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

Conditional Formating with IF and AND formula
 

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

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"murkaboris" wrote:

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

Conditional Formating with IF and AND formula
 

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

"Luke M" wrote:

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

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"murkaboris" wrote:

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