Conditional Formatting depending on date determined via formula

[m27mitchell]

Hello, I'm trying to apply conditional formatting to cells that contain a formula that returns a date (deducting days from another cell that contains a date). I want to do the following, but it's not working. I wasn't sure if the reason it's not working may be because the cell that it's formatting contains a formula to return a date, in lieu of an actual date being typed directly in the cell.

- If date in the past - cell is red formula I am using is: =($N6<TODAY()-30)
- If date is within the next 2 weeks - cell is yellow formula I am using is: =AND($N6<TODAY()-14,$N6>TODAY()-31)
- If date is further than 2 weeks out - cell has no color (fill) formula I am using is: =($N6>TODAY()-15)
- If cell is blank - cell has no color (fill) using the "format only cells that contain blanks" option

Any idea on what I'm doing wrong? It's making some of the past dates yellow instead of red, and some red, etc.

 

[Fluff]

What is the first cell in the applies to range?

 

[m27mitchell]

What is the first cell in the applies to range?

N6

 

[FracinDean]

The answer to one of your questions is that it doesn’t matter if the cell contains a constant or a formula. I can help with the formulas, but some of your definitions are confusing to me. What does “two weeks out” mean. If that is in the future, then you have a sign error, should be

=($N6>today() +15

Remember to use the code tags around your code when posting. Just select your code and then click on the button that says <v b a / > on the toolbar.

 

[Fluff]

Remember to use the code tags around your code when posting.

The OP is posting a formula, not code & therefore there is no need to use code tags.

 

[Fluff]

@m27mitchell
For red use
=AND($N6<>"",$N6<TODAY()-30)
for Yellow use
=AND($N6<>"",$N6<TODAY()+14)
No need to set a condition for blank cells, make sure the the red rule is above the yellow rule in the order of priority