Help!!! - logical function for someone not very logical

[ECH123]

sing Excel 2007 - Trying to write a conditional formula for cells that have
either text and dates i.e

A B C D E
1. DATE MAY 09 JUN 09 JUL09 AUG09
2. 16 Feb 09
3. Complete

For B2, need formula to look at A2, if A2 = complete, then Blank, if A2 =
date then difference between B2 and A2, in this case May 09 and Feb 09, if
less than 3 months, then fill RED, if greater than 3 months but less than 6
months, then fill AMBER, IF greater than 6 months then fill GREEN.
Is this possible?

 

[Ron@Buy]

The answer to your question is yes - but some additional info and a change in
your criteria.
First: Less than 3 months > red. Greater than 3 months & less than 6 > amber
- what if the difference IS 3 months, what colour?
Second: You can't enter a date into B2 and have a formula in it at the same
time!
Do you intend the difference in B2 to be between B1 & A2 ?

 

[Ron@Buy]

The answer to your question is yes - but some additional info and a change in
your criteria.
First: Less than 3 months > red. Greater than 3 months & less than 6 > amber
- what if the difference IS 3 months, what colour?
Second: You can't enter a date into B2 and have a formula in it at the same
time!
Do you intend the difference in B2 to be between B1 & A2 ?

 

[ECH123]

Thanks - Less than or equal to 3 months is red
For the second - meant for the difference to be between B1 and A2 for cell
B2, B1 and A3 for cell B3 and so on

 

[ECH123]

Thanks - Less than or equal to 3 months is red
For the second - meant for the difference to be between B1 and A2 for cell
B2, B1 and A3 for cell B3 and so on

 

[Ron@Buy]

OK, clear now, so:
in B2 enter =IF(OR(A2="complete",A2=""),"",B$1-A2). Copy across and down as
necessary after entering the conditional formatting.
The in the Conditional Formatting window use "Use a formula to determine
which cells to format" and enter as the first condition:
=IF(YEAR(B$1)=YEAR(A2),MONTH(B$1)-MONTH(A2),MONTH(B$1)-MONTH(A2)+12)<=3 and
use colour RED
For the second condition:
=IF(YEAR(B$1)=YEAR(A2),MONTH(B$1)-MONTH(A2),MONTH(B$1)-MONTH(A2)+12)<6 and
use colour AMBER
and the third condition:
=IF(YEAR(B$1)=YEAR(A2),MONTH(B$1)-MONTH(A2),MONTH(B$1)-MONTH(A2)+12)>=6 use
colour GREEN
Important you enter in the above order.
Trust this helps

 

[Ron@Buy]

OK, clear now, so:
in B2 enter =IF(OR(A2="complete",A2=""),"",B$1-A2). Copy across and down as
necessary after entering the conditional formatting.
The in the Conditional Formatting window use "Use a formula to determine
which cells to format" and enter as the first condition:
=IF(YEAR(B$1)=YEAR(A2),MONTH(B$1)-MONTH(A2),MONTH(B$1)-MONTH(A2)+12)<=3 and
use colour RED
For the second condition:
=IF(YEAR(B$1)=YEAR(A2),MONTH(B$1)-MONTH(A2),MONTH(B$1)-MONTH(A2)+12)<6 and
use colour AMBER
and the third condition:
=IF(YEAR(B$1)=YEAR(A2),MONTH(B$1)-MONTH(A2),MONTH(B$1)-MONTH(A2)+12)>=6 use
colour GREEN
Important you enter in the above order.
Trust this helps