conditional formatting based on different cells with more conditio

[sarajane82]

I'm trying to get conditional formatting in the cells in one column. I want
them to be blue if the cell in front of it contains the value "N/A", I want
it to be green if the cell in front of it contains a value greater than 180,
i want it to be yellow if the cell in front of it is greater than 180 AND if
the months in the two cells before THAT one are 6 months apart.

ex: A5= 12/20/2007; B5= 7/25/2008; C5= 218 (formula of B5-A5); D5 has a
conditional formula to produce the answer "No" because C5 is greater than
180. But I need that to automatically shade green.

BUT...if A6=12/19/2007 and B6= 6/25/2008, C6 will be 189. The formula in D6
produces a "No" but I need it to be yellow because even though it is greater
than 180, the month in A6 (December) is 6 months away from the month in B6
(june). Is this even possible?

 

[JBeaucaire]

You get 3 levels of conditional formatting, so this should work:

Condition1: Formula Is:
=D5="N/A"
Format: Blue background

Condition2: Formula Is:
=(IF(YEAR(A5)=YEAR(B5),MONTH(B5)-MONTH(A5),
MONTH(B5)+12-MONTH(A5)))<=6
Format: Yellow background

Condition3: Formula Is:
=(IF(YEAR(A5)=YEAR(B5),MONTH(B5)-MONTH(A5),
MONTH(B5)+12-MONTH(A5)))>6
Format: Green background