SaraWitch
Active Member
- Joined
- Sep 29, 2015
- Messages
- 322
- Office Version
- 365
- Platform
- Windows
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!
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!
Last edited: