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!).
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 then rejigged the conditional format formula to: =AND($AC8="Yes",$AD8="Waiting",$AD8="Yes",$AB8="A" [format fill orange]
with this result:
Column AB = "A" / Column AC = "Yes" / Column AD = "Waiting" or "Yes" - line turns green, not orange [not working]
Maybe I have my formula wrong, but instinct is telling me this is the way to go!
Any help would be greatly appreciated as this is driving me mad!
Ta muchly, folks!
Bookmarks