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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

Conditional Formatting in excel using multiple validations

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
jacobJL
Asked:
jacobJL
  • 4
  • 2
2 Solutions
 
Saqib Husain, SyedEngineerCommented:
=AND($D2<TODAY(),$E2<>"Complete")
0
 
jacobJLAuthor Commented:
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
 
Saqib Husain, SyedEngineerCommented:
=WEEKNUM($C2)=WEEKNUM(TODAY())

=AND(WEEKNUM($D2)=WEEKNUM(TODAY()),$D2<>TODAY())
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
jacobJLAuthor Commented:
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
 
jacobJLAuthor Commented:
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
 
jacobJLAuthor Commented:
Thank you for your help
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now