Conditional Formatting

[tobysdhc]

Hello all,

I have two columns.

Column A (2:500) is a due date calculated by a formula
Column B (2:500) is the actual date of completion.

I have conditional formatting in Column A as the date gets closer (yellow 1 month out, orange 1 week out, red this week). The problem is, the conditional formatting is still in effect even if the date completed (column B) is prior to the due date.

Is there a way I can have excel ignore the conditional formatting, for example if B3 >= A3 without having to enter a formula for each of the 500 rows?

Thanks,

 

[Jeffrey Mahoney]

Yes. Please provide the formula you have for the conditional formatting now. If you have one.

 

[FDibbins]

You probably just need to include an AND() function in there....
=AND(B3<A3,your-formula)

(keep in mind that all CF needs to work, is for the formula to return TRUE or FALSE, so you only need to structure the formula to return 1 of those)

 

[Jeffrey Mahoney]

Check to see if I got this right

To test for the Week range: =OR(AND(A2-TODAY()<8,A2>=TODAY(),B2=""),AND(A2-TODAY()<8,A2>=TODAY(),B2<>"",A2<b2))

To test for the month range:

=OR(AND(A2<edate(today(),1)+1,a2>=TODAY(),B2=""),AND(A2<edate(today(),1)+1,a2>=TODAY(),B2<>"",A2<B2))<b2))[ code]<edate(today(),1)+1,a2=""><edate(today(),1)+1,a2><b2))
<edate(today(),1)+1,a2><edate(today(),1)+1,a2><b2))


The conditional formatting for the week will need to have "Stop If True" checked.

Jeff</b2))
</edate(today(),1)+1,a2></edate(today(),1)+1,a2></b2))
</edate(today(),1)+1,a2></b2))[></edate(today(),1)+1,a2></edate(today(),1)+1,a2></b2))

 

[FDibbins]

looks like most of my formula got left out because of the V sighn....

You probably just need to include an AND() function in there....
=AND(B3 < A3,your-formula)

(keep in mind that all CF needs to work, is for the formula to return TRUE or FALSE, so you only need to structure the formula to return 1 of those)

 

[Jeffrey Mahoney]

It truncates the formula

 

[Jeffrey Mahoney]

Week:
Add these two lines together to make a complete formula for the week CF:
=OR(AND(A2-TODAY()<8,A2>=TODAY(),B2=""),
plus the next line
AND(A2-TODAY()<8,A2>=TODAY(),B2<>"",A2<B2))

 

[FDibbins]

It truncates the formula

Yup thanks, I had forgotten that lol

 

[Jeffrey Mahoney]

Month:

Combine the next three lines together to make the complete formula
=OR(AND(A2<EDATE(TODAY(),1)+1

plus

,A2>=TODAY(),B2=""),AND(A2<EDATE(TODAY(),1)+1

plus

,A2>=TODAY(),B2<>"",A2<B2))

 

[Jeffrey Mahoney]

The month formula is all messed up

=OR(AND(A2{EDATE(TODAY(),1)+1,A2}=TODAY(),B2=""),AND(A2{EDATE(TODAY(),1)+1,A2}=TODAY(),B2{}"",A2{B2))

Replace all { with less than signs
REplace all } with greater than signs