Conditional Formatting based on Date and Data within Cells

[pawcoyote]

Hi all,

I am working on a workbook and having a bit of an issue. It seems that these two Conditional Formatting Formulas do not work nicely together.

The first code is to fill in the cell red if Today's date is past the date in D13 as long as G13 Has Open or Hold in it
The second code is to fill in the cell yellow if Today's date is within the next 7 days of the date in D13 as long as G13 has Open or Hold in it.

Color Fill is Red - Past Today's Date

=AND(AND(NOT(ISBLANK($D13)),TODAY()>$D13),OR($G13="Open",$G13="Hold",ISBLANK($G13)))

Color Fill is Yellow - Within Next 7 days of Today's Date

=AND(AND(NOT(ISBLANK($D13)),TODAY()>$D13-7),OR($G13="Open",$G13="Hold",ISBLANK($G13)))

What is happening is The yellow code is making the cells all yellow even when D13 is past the due date of today. It isn't filling for dates that are within 7 days... Those remain without a fill in color.

 

[Fluff]

Try changing the order of the rules, so that the Red fill is before the yellow fill

 

[pawcoyote]

Try changing the order of the rules, so that the Red fill is before the yellow fill

Thank you once again. Also I had +7 not the -7 in my yellow saw that once I did the change in order. You are the best...

 

[Fluff]

You're welcome & thanks for the feedback.