Conditional Formatting contradicting formula

[gravanoc]

Gee, my favorite topic! Conditional formatting is kicking my *** again.

Problem Statement: My conditional formatting formula is applying formatting to the correct & incorrect cells, all except cells with vanilla text in them in column E. What is disquieting about this situation is that I have the near-equivalent formula in a regular cell which is what I copied and pasted into the cond. formatting formula. The only difference between the two is that the normal cell formula contains an IF statement, which was castrated on the move to the cond. format formula, since IF statements don't seem to work there. Here's the two, side-by-side:

=IF(OR(NOT('Job Planning'!E3>$S$3),$P$3>'Job Planning'!E3,N3=FALSE, NOT(YEAR('Job Planning'!E3)=YEAR(NOW())), NOT('Job Planning'!E3>$T$3)),"RED","GREEN")
=OR(NOT(E1>Internals!$S$3),Internals!$P$3>E1,Internals!N3=FALSE, NOT(YEAR(E1)=YEAR(NOW())), NOT(E1>Internals!$T$3))

To clarify the conditions I've set, the first is checking whether the scheduled date in E is greater than date at the beginning of the week, currently 29-Sep. Second, is today's date greater than the date in E. Third, is the checkbox's linked cell set to FALSE? Fourth, it's checking to make sure the year is the current year. Finally, if the the scheduled date in E exceeds Thursday's date.

The truly bizarre part is that if I check boxes below a certain, it will unformat the cell two rows above it. I've tried adjusting the formula to E1, E3, etc. I figure some part of that is the problem.

Pictures:

 

[gravanoc]

Update: It's working now. The way I fixed the earlier problem was by re-arranging the cells in the reference sheet to all be in the 1st row.

 

[jasonb75]

Gee, my favorite topic! Conditional formatting is kicking my *** again.
The only difference between the two is that the normal cell formula contains an IF statement, which was castrated on the move to the cond. format formula, since IF statements don't seem to work there.

It depends on the output of the formula, but more often than not they will fail, in an attempt to help you understand the logic behind cf a little better.

Conditional formatting is only applied where the formula returns a logical TRUE, this is regardless of the functions used.

If your formula results in TRUE or any number (including dates, decimals, fractions, times, currency) other than zero then the format will be applied.

If your formula returns FALSE, zero, an error, or any text (including numbers formatted as text) then the format wil not be applied.

When you apply conditional formatting to a range, it is advisable to select the range from top left to bottom right, then base the formula on the top left cell when considering relative ranges, etc. Doing it any other way can mess things up (the relative ranges are based on the position of the activecell within the applied range when you set the rule).

Finally, remember the 'stop if true' option if you have multiple rules for any given range, especially if conditions overlap so that 2 or more rules could result in true. In this scenario, the rule that should be applied last should be top of the list with the box ticked. Getting this part wrong is probably the most common cause of incorrect formatting being applied.

Hope this helps.