Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CONDITIONAL FORMATING FORMULA | Excel Worksheet Functions | |||
formula for conditional formating | Excel Discussion (Misc queries) | |||
Conditional formating formula | Excel Worksheet Functions | |||
if, then formula with conditional formating | Excel Worksheet Functions | |||
Formula for Conditional Formating - Help! | Excel Worksheet Functions |