Solved

Conditional Formatting in excel using multiple validations

Posted on 2012-04-12
6
322 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
  • 4
  • 2
6 Comments
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 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 500 total points
ID: 37838597
=WEEKNUM($C2)=WEEKNUM(TODAY())

=AND(WEEKNUM($D2)=WEEKNUM(TODAY()),$D2<>TODAY())
0
Industry Leaders: 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

740 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