Link to home
Start Free TrialLog in
Avatar of isaacr25
isaacr25

asked on

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!
Avatar of Saurabh Singh Teotia
Saurabh Singh Teotia
Flag of India image

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

Saurabh...
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
Use the conditional formatting manager:

User generated image
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
Avatar of isaacr25
isaacr25

ASKER

I've attached a sample file. Thanks.
test2.xlsx
ASKER CERTIFIED SOLUTION
Avatar of rspahitz
rspahitz
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
If you use the "Stop if true", as shown in the last file, checking for "Complete" is redundant in the conditions after the first ...
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
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.
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.
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
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")
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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial