conditional formatting question

[RDOREMUS]

I have a simple spreadsheet of expiring dates.


columns I N O Q

10-JUL-09 11-APR-10 10-JUL-10 08-OCT-10


I - LAST ANNUAL EXAM
N- 90 DAYS BEFORE NEXT ANNUAL EXAM
O- NEXT ANNUAL EXAM DATE
Q- 90 DAYS AFTER NEXT ANNUAL EXAM

WHAT IM TRYING TO DO IS HAVE THE DATES CHANGE COLORS FROM RED, GREEN, YELLOW AND NO FORMAT COLOR

FOR COLUMN N:
GREEN: IF TODAY IS 31-90 DAYS BEFORE (O)
YELLOW: IF TODAY IS 1-30 DAYS BEFORE (O)
RED: IF TODAY IS 90 OR MORE DAYS OVERDUE (O)

COLUMN 0:

GREEN: 31-90 DAYS BEFORE (I)
YELLOW: 1-30 DAYS BEFORE (I)
RED: IF TODAY IS PAST/OVERDUE FROM (I)

COLUMN Q:

GREEN: SAME AS ABOVE JUST ADJUSTED OFF OF COLUMN (O)
YELLOW: IF TODAY IS 1-120 DAYS BEFORE COLUMN (O)
RED: TODAY IS 90 DAYS OR MORE PAST THE DATE IN COLUMN (Q)

THESE ARE THE FORMULAS IVE USED:

GREEN: =AND(($O3-TODAY())>30,($O3-TODAY())<=90)

YELLOW: =AND(($I3-TODAY())>0,($I3-TODAY())<=30),
=AND(($Q3-TODAY())>0,($Q3-TODAY())<=120)

RED: =($I3-TODAY())>=90; =TODAY()>$O3

IDK WHAT IM DOING WRONG BUT IT SEEMS THAT THE REDS ARENT WORKING THE WAY I WANT THEM TOO. ANY HELP IS APPRECIATED.

 

[stefankemp]

Hi, please don't post in capitals.

 

[RDOREMUS]

umm. . why not, ive done it in other posts and had no complaints or issues?

 

[stefankemp]

It is difficult to read and the board rules state that you shouldn't SHOUT.

But it's up to you - I think you are more likely to get replies if you type normally.

 

[Norie]

Because it makes it hard to read and understand your post.

 

[RDOREMUS]

oh ok. . i see i thought shouting came with punctuation not just capitalization but i guess im wrong. Well ill type in lowercase from now on.