Conditional Formatting with 2 Fields

Hi everyone,
I need some assistance please! I have an Excel spreadsheet to which I need to apply conditional formatting, based on 2 fields (let's call them Date and Status). Based on the values of the fields, I need to change the background of the entire row (if possible) to a specific color.

So:
Date within 5 days of current date + Status not equal Complete = yellow background
Date is same as current date or later + Status not equal Complete = red background
Any row with Complete status = green background

Your assistance is greatly appreciated!
isaacr25Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Can you post your sample workbook as it will become easy to do the conditional formatting for you which you can copy over..

Saurabh...
0
rspahitzCommented:
What I think you want is the conditional formatting option to "Use a formula to determine which cells to format"

Using that option, you can apply a boolean expression such as =AND(mydate >= today()-5, mydate <= today(), status <> "complete")

mydate would typically be a cell such as A1, unless you have a named cell; likewise with status, which might be B1
0
DougCommented:
Use the conditional formatting manager:

conditional formatting manager
You need Three separate formatting Rules (Apply these rules where Column B is your Date and column C is your Status.):

=AND($B2<=TODAY(),$C2<>"Complete")

=AND($B2+5>TODAY(),$C2<>"Complete")

=$C2="Complete"
ConditionalFormatting.PNG
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

isaacr25Author Commented:
I've attached a sample file. Thanks.
test2.xlsx
0
rspahitzCommented:
Try the attached. I added a few more sample entries.
change the status or start date and the colors of the rows should change.
Also note that if it's not complete and more than 5 days old, there is no effect so it remains whatever the background color is.
test2.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
If you use the "Stop if true", as shown in the last file, checking for "Complete" is redundant in the conditions after the first ...
0
isaacr25Author Commented:
rspahitz,
I'm trying the rules in the sample file you attached. See my attempt attached. It seems to be partly working. Something weird is happening, where it's skipping some lines that should be caught with the formatting. What am I missing?
test3-exp.xls
0
rspahitzCommented:
It appears that the conditional format formula for green is $J19 rather than $J10.
Try that and apply it.
Let me know if there are other issues...the rest seems correct to me.
0
isaacr25Author Commented:
Works great!

Just for my knowledge, can you explain why I needed to use 19 as opposed to 10? The data starts in row 10, that's why I used $J10.
0
isaacr25Author Commented:
Argh! Also... it seems like the Yellow rule isn't working. See attached. The rows with 11/14/2015 dates should be yellow but they're not. Would you mind taking a look?
test4-exp.xls
0
rspahitzCommented:
1. It seems you realized that $J10 was correct.

2. Today is 11/11, and maybe I misinterpreted the requirement of "Date within 5 days of current date"
I thought this was 5 days ago through today and came up with this:

=AND($B10>=TODAY()-5,$B10<TODAY(),$J10<>"Complete")

If you wanted 5 days before through 5 days after, change the second part to add 5:

=AND($B10 >= TODAY() - 5, $B10 < TODAY() + 5, $J10 <> "Complete")
0
isaacr25Author Commented:
Actually I just need 5 days after (not before). I'm trying to yellow highlight anything that is coming in the next 5 days that's not complete yet.
0
rspahitzCommented:
Ok so remove the "- 5" and you should be good. Oh and you may want to change the >= to just > if you don't wsnt today to appear
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.