?
Solved

Conditional Formatting in excel using multiple validations

Posted on 2012-04-12
6
Medium Priority
?
341 Views
Last Modified: 2012-04-15
Hi,
I am trying to get a conditional format where the row is highlighted in red if the end date is < today and the status != to complete. Seems fairly simple but unable to do it. I attached the test spreadsheet and copied and pasted the text below. Thanks in advance


Assignment      Owner      Start               End                   Status      
assignment 1       Jacob      4/9/2012              4/10/2012      Not Started      
assignment 2      Jacob      4/9/2012              4/10/2012      Complete      
assignment 3      Jacob      4/10/2012      4/19/2012      Not Started      
assignment 4      Jacob      4/13/2012      4/16/2012      Not Started      
assignment 5      Jacob      4/16/2012      4/17/2012      Not Started
test.xlsx
0
Comment
Question by:jacobJL
[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
  • 4
  • 2
6 Comments
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 2000 total points
ID: 37838161
=AND($D2<TODAY(),$E2<>"Complete")
0
 

Author Comment

by:jacobJL
ID: 37838318
Wow that was fast, and worked like a charm. Thank you! If you don't mind I have 2 more conditions I would like to add.

If start date is this week i would like the row green.
If end date is this week but !=today and not complete then row yellow.
0
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 2000 total points
ID: 37838597
=WEEKNUM($C2)=WEEKNUM(TODAY())

=AND(WEEKNUM($D2)=WEEKNUM(TODAY()),$D2<>TODAY())
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jacobJL
ID: 37838877
Great thank you so much. Would like to keep open for a little bit. I am just trying to make all 3 work together without conflicting with one another. Greatly appreciated.
0
 

Author Comment

by:jacobJL
ID: 37839981
Ok last one.
If  cell E2 the status = Ready for QA or Complete I want no filter applied or just white background.
I tried a series of things and this is the last thing I came up with but doesn't work.

=AND(ISNUMBER(SEARCH("Ready for QA",$E2)),$E2="Complete")


EDIT:
actually I just created 2 rules like this:
=IF($E2="Ready for QA", TRUE, FALSE)
and
=IF($E2="Complete", TRUE, FALSE)

This works but if you have a better way or can combine them into one that would be great. Thank you.
0
 

Author Closing Comment

by:jacobJL
ID: 37849538
Thank you for your help
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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…

764 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