Conditional Formatting

[SaraWitch]

Hello all.

I am trying to conditional format for four outcomes:

1. Change cells to green if active
2. Change cells to orange if today's date is over deadline date and not yet approved
3. Change cells to orange if approval date is over deadline date, but green if under deadline date
4. Change cells to blue if complete

The formulas I have entered are all working except number 3 (cells stay orange if today's date is over deadline date regardless if approval date is under)!

Spreadsheet details are:

Column C: Date received
Column E: Deadline date
Column T: Approval date
Column V: Complete/Cancelled/Refused

I then have three additional columns that show:

Column AB (Status): A [Active], B [Complete], C [Blank] (=IF$C8="","C",(IF($V8>0,"B",IF($V8="","A"))))
Column AC (Overdue (no approval yet)): Yes, No (=IF($E8>TODAY(),"No","Yes")
Column AD (Approval overdue): Waiting, Yes, No (=IF($T8="","Waiting",(IF($T8>$E8,"Yes","No")))

I have conditional formatted rows as (order as seen on CF Rules Manager box):

=AND($AC8="Yes",$AD8="Yes",$AB8="A") [format fill orange]
=AND($AC8="Yes",$AB8="A") [format fill orange]
=$V8>0 [format fill blue]
=$C8>0 [format fill green]

I know it makes a difference what order the CFs are and I have played with them to no avail (have experimented with 'Stop If True' box too, which makes no difference!).

Any help would be greatly appreciated as this is driving me mad!

Ta muchly, folks!

 

[Joe4]

Can you provide an example in which it is not working correctly?
Namely, what is in columns C, E, T, and V?
And what color are you expecting?

 

[SaraWitch]

Hello Joe4,

To clarify, number 3 is not working - once an approval date is entered I want the cells to either:

a. stay green if approval date is under deadline date;
b. stay orange if approval date is over deadline date.

However, the cells are staying orange if today's date is over the deadline date, regardless of the approval date.

Data is columns as in my original post.

Hope this explains further!

Ta muchly!
Sara

 

[Joe4]

To clarify, number 3 is not working - once an approval date is entered I want the cells to either:

As I said, please give me a specific example, namely what values are in column C, E, T, and V.
I am trying to recreate the situation on my side, so need to know the EXACT values you are working with for one of these that is not working the way you want.

 

[SaraWitch]

Hello.

In Column C is the date received.
In Column E is the deadline date.
In Column T is the approval date.
In Column V is the completion date.

What I have is conditional formatting to change the row green if date received is entered, turn orange if the deadline date has passed (today's date) and blue when a completion date is entered. But when an approval date is entered before the deadline date, I want it to be green until completion date is entered, when it turns blue. The line is staying orange if today's date is past the deadline date even though the approval date was before the deadline date.

I have further columns in AB (to show if there is any active data ('A','B' or 'C')), AC (to say 'yes' or 'no' if overdue but no approval yet) and AD (to say 'waiting', 'yes' or 'no' if approval date has not been entered or is over or below deadline date). Formulas as in my initial post. I have tried IFAND formulas based on these cells, i.e. =IF(AND($AC8="A",$AC8="Yes",$AD8="Waiting",$AD8="Yes")) [fill cells orange]. But this isn't working! Maybe I have my formula wrong as I feel this is the way to go!

Any help would be appreciated!

Ta muchly!

 

[Joe4]

Despite my best efforts, I am afraid I am not making myself clear here. I am looking for actual data.

Pick a record, ANY RECORD, that is not working the way you want, and tell me those what those data values in those columns are.

For example, cell C8 is 10/25/2018, etc. (tell me the values in ALL the columns used in your formulas, C, E, T, and V).
Tell me ACTUAL DATA values for any record you have that is not working the way you want.

 

[SaraWitch]

Hello.

The actual data can be any dates, but for an example:

Column C = "01/08/2018" (date received) - line turns green [working]
Column E = "12/09/2018" (deadline date) - line turns orange if date is below today's date [working]
Column T = "15/09/2018" (approval date) - line stays orange [working]

but if

Column T = "10/08/2018" (approval date) - line still stays orange, when should be green as the approval date is below the deadline date [not working]

Column V = "Complete" - line turns blue [working]

I have re-jigged the conditional format formula to: =AND($AC8="Yes",$AD8="Waiting",$AD8="Yes",$AB8="A" [fill orange]

with this result:

Column AB = "A" / Column AC = "Yes" / Column AD = "Waiting" or "Yes" - line turns green, not orange [not working]

Hope this now makes sense for you!

 

[Joe4]

Ok, the issue has to do with how your Conditional Formatting rules are written.

=AND($AC8="Yes",$AD8="Yes",$AB8="A") [format fill orange]
=AND($AC8="Yes",$AB8="A") [format fill orange]
=$V8>0 [format fill blue]
=$C8>0 [format fill green]

Note that if your record meets multiple Conditions, the order matters. The first one it hits will win out.
So you need to be sure to either:
- make sure that they are placed in the proper order
- write your conditions so that they CANNOT possibly meet more than one condition (use AND to check more values)

So, it is turning orange because it is hitting your second condition here:

=AND($AC8="Yes",$AB8="A")

before it hits the green one.

I would recommend amending it like this:

=AND($AC8="Yes",$AB8="A",$T8>$E8)

and then it should work the way you want.

 

[SaraWitch]

You're AMAZING!!!!

Thank you so, so, so very much, Joe4! No more sleepless nights!

 

[Joe4]

You are welcome!

When writing multiple Conditional Formatting rules, I usually try to make them as narrow as possible, so that any record cannot meet more than one condition. That way, I know it won't run into the issue, and order really doesn't matter.