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:
Your assistance is greatly appreciated!
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!
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
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:
You need Three separate formatting Rules (Apply these rules where Column B is your Date and column C is your Status.):
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<>"Co mplete")
=AND($B2+5>TODAY(),$C2<>"C omplete")
=$C2="Complete"
ConditionalFormatting.PNG
=AND($B2+5>TODAY(),$C2<>"C
=$C2="Complete"
ASKER
I've attached a sample file. Thanks.
test2.xlsx
test2.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you use the "Stop if true", as shown in the last file, checking for "Complete" is redundant in the conditions after the first ...
ASKER
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
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.
Try that and apply it.
Let me know if there are other issues...the rest seems correct to me.
ASKER
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.
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.
ASKER
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
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(),$J 10<>"Compl ete")
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")
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<
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")
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Saurabh...