Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Need formula for conditional formatting

Posted on 2011-02-23
Medium Priority
301 Views
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
0
• 2
• 2

LVL 10

Expert Comment

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

LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 34966147
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

LVL 10

Expert Comment

ID: 34966157
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 Comment

ID: 34980926
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 Comment

ID: 34983759
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
###### Suggested Courses
Course of the Month11 days, 22 hours left to enroll