Help with conditional formatiing?
 

2 columns with dates, if date in column A is more than 1 year after column B,
then make the date in column A red. I can't get this to work. Conditional
formatting will only look at one row but not the rest of the rows. I need
something that compares relatively, eg. A2,B2; A3,B3, etc. Right now the
best I can do with conditional formatting is A2,B2; A3,B2; A4,B2; etc. Would
appreciate an email reply in addition to newsgroup reply. Thanks.

It seems as if you have to remove dollar signs.
I think the following condition should work:
=OR(AND(YEAR(A1)=YEAR(B1)+1,MONTH(A1)=MONTH(B1),DA Y(A1)DAY(B1)),
AND(YEAR(A1)=YEAR(B1)+1,MONTH(A1)MONTH(B1)), AND(YEAR(A1)YEAR(B1)+1))
"abqhusker" wrote:

2 columns with dates, if date in column A is more than 1 year after column B,
then make the date in column A red. I can't get this to work. Conditional
formatting will only look at one row but not the rest of the rows. I need
something that compares relatively, eg. A2,B2; A3,B3, etc. Right now the
best I can do with conditional formatting is A2,B2; A3,B2; A4,B2; etc. Would
appreciate an email reply in addition to newsgroup reply. Thanks.

Martin P wrote:
It seems as if you have to remove dollar signs.
I think the following condition should work:
=OR(AND(YEAR(A1)=YEAR(B1)+1,MONTH(A1)=MONTH(B1),DA Y(A1)DAY(B1)),
AND(YEAR(A1)=YEAR(B1)+1,MONTH(A1)MONTH(B1)), AND(YEAR(A1)YEAR(B1)+1))
"abqhusker" wrote:


2 columns with dates, if date in column A is more than 1 year after column B,
then make the date in column A red. I can't get this to work. Conditional
formatting will only look at one row but not the rest of the rows. I need
something that compares relatively, eg. A2,B2; A3,B3, etc. Right now the
best I can do with conditional formatting is A2,B2; A3,B2; A4,B2; etc. Would
appreciate an email reply in addition to newsgroup reply. Thanks.


Martin, thanks for replying. You know what, the dollar sign were the
problem initially but I removed them and was still having problems. I
believe that the problem was in my formula for adding a year on to a
date, lol. My formula was A2B2+12 (B2+12 is supposed to represent
date+12 months). But what just dawned on me is that I need to change it
to B2+365. B2+12 was only adding 12 days!!!!!!!! LOL! DUH!!!! I am
keeping your formula above for future reference though. Thanks alot.

ABQHUSKER SAYS: THESE GUYS ARE GOOD!!!! YOU CAN LEARN A LOT FROM THESE
GUYS!!!