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]

For the week formula

=OR(AND(A2-TODAY(){8,A2}=TODAY(),B2=""),AND(A2-TODAY(){8,A2}=TODAY(),B2{}"",A2{B2))

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

 

[Jeffrey Mahoney]

That's Dumb!!!!!!

Almost gave up

 

[tobysdhc]

Wow! Thanks! I'll give it shot and let you know how it goes.

 

[FDibbins]

Jeff you can still use < and > you just need to include a space around them, otherwise the server thinks you are using HTML...
=OR(AND(A2-TODAY() > 8,A2 < =TODAY(),B2=""),AND(A2-TODAY() > 8,A2 < =TODAY(),B2 < > "",A2{B2))

 

[Jeffrey Mahoney]

Ok Dibbins. It was the end of my day and wanted to go home.

 

[FDibbins]

No problem, some people don't know that

 

[tobysdhc]

Do I put that in the conditional formatting "formula" section? And will I have to do that for all 500 rows?

 

[FDibbins]

1. highlight the range you want to apply the conditional formatting to
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter the suggested formula/s

 

[tobysdhc]

Hello and thank you for your help. I entered the formulas. the week range took some off, but not others. The month formula had no effect. It may have something to do with

 in front?

 

[Jeffrey Mahoney]

I'm going to post these differently. This time with spaces before and after the < and > signs. You will have to remove the spaces.

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

Month
=OR(AND(A2-TODAY() < 8,A2 > =TODAY(),B2=""),AND(A2-TODAY() < 8,A2 > =TODAY(),B2 < > "",A2 < B2))