Solved

Need formula for conditional formatting

Posted on 2011-02-23
5
297 Views
Last Modified: 2012-05-11
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
Comment
Question by:darthvader747
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 10

Expert Comment

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

Accepted Solution

by:
barry houdini earned 500 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

by:Makrini
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

by:darthvader747
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

by:darthvader747
ID: 34983759
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question