Help with Conditional Formatting

[bobbyd98682]

Could someone please help me with conditional formatting? I’ve been trying to figure this out with the help of videos and web pages but I could really use an expert.

We send samples out for micro and heavy metals testing. A sample can be tested for micro, heavy metals, or both. An X is placed in the MIC or HM cell to indicate which test(s) the sample will receive. An IF statement places an “N/A” into the cell where no MIC Report Date or HM Report Date is required.

What is needed: To have all the cells in the ‘Report Due Date’ column fill with a color if today’s date is greater than the report due date and a cell (same row, column B or C) is empty.

I guess what I need is if TODAY()>$A$2 and either $B$2 or $C$2 are “”, then fill $A$2 with a color; and to have this for the entire column A.

I've attached a copy of what I am working on.

Thank you

Bob

 

[Atlas Johnson]

Hi Bob,
You have two criteria

C1. Date is greater than Today
C2. B or C is empty

So you have DATE>TODAY AND (B IS EMPTY OR C IS EMPTY)

Use this formula in the conditional formatting:
=AND($A2>TODAY(), OR($B2="",$C2=""))

Best,
AJ

 

[NARAYANK991]

Use this formula in the conditional formatting:
=AND($A2>TODAY(), OR($B2="",$C2=""))

Best,
AJ

Hi ,

I think you meant :

=AND($A2<TODAY(), OR($B2="",$C2=""))

which would mean that the due date for testing is passed , and at least one of the tests has not been done.

If the tests were sometime in the future ( $A2 > TODAY() ) that would not be cause for alarm.

Narayan

 

[bobbyd98682]

Thank you both for your replies.

What I failed to mention is that I would like the fill color, in column A, to stop when both cells B and C, beside it, are filled.

 

[NARAYANK991]

Hi Bob ,

That is already taken care of by the given formula.

See the uploaded file , where I have used the date entered in $N$1 ; you can manually enter any date in it , or you can have a formula such as :

=TODAY()

in it , so that today's date will be used for the CF.

At present , the Applies To range for the CF is the range $A$2:$A$8 ; if you want the CF to apply to an extended range , just change the above ; thus , if you want the CF to apply to all cells in column A till row 100 , change the above to :

$A$2:$A$100

Narayan

 

[bobbyd98682]

Thank you Atlas Johnson and NARAYANK991!
You both are fantastic!