# Need formula for conditional formatting

Please write a conditional formatting formula to do this:

If today's date is greater than due date (Column A value) then Fill the cell Red but not if
the value in column L is 'P' (check Mark - Windings 2)

This can easily be done by =A1<Today()  ...then color A1 red

What I need added is that the cell should only be colored if there is no check mark in Column F . I guess an IF statement would do this.

So

Due Date (ColA)     Completed(Col F)

1/15/2010                        ..A1 should be red

1/15/2010           P            ...A2 should not be red cause there is a check mark/P in column F
###### Who is Participating?

x

Commented:
You don't really need an IF statement in conditional formatting just a condition that evaluates to either TRUE or FALSE. I'd also add another condition because if applied to a blank row the formatting will be triggered because a blank cell is deemed to be zero and hence < today....and of course a blank in F1 will also <>"P", so try

=AND(A1<TODAY(),F1<>"P",A1<>"")

regards, barry
0

Commented:
=if(and(A1<Today() ,F1<>"P"),1,0)
0

Commented:
Barry's point as per usual is on the money.

I am in the habit of using the if because I tend to use a few nested if statements and I understand it easier that way
0

Author Commented:
Please see my conditional formatting formula/rules for Due Date

When I use barry's formula and due date is 1/25/2011 - The cell gets colored yellow. It should be red.

I am sure its because of my other two rules. Can you help me fix this?

I need green if due date falls within the next 25 days from today; Yellow if it falls within the next 5 days and Red if due date is before Today

Rule 1 - Color Due Date Red when Due date is less than today and col F does not have a checkmark

=AND(A2<TODAY(),F2<>"P",A2<>"")

Rule 2

Color Due Date yellow when due date falls withing the next 5 days
Formula: (A2-TODAY())<5

Rule 3

Color Due Date "Green" if due date is falls within the next 25 days
Formula: =(A2-TODAY())<25

0

Author Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.