How to highlight cell that is past due AND not complete?

[jekl1000]

Hi all,

I have a simple action item list for a project. Column E has the Due Data and Column E has the Status (Complete, In Process, Not Started, etc.). I would like the date in Column E to highlight red if the date has already past AND the Status in Column E is anything other than "Complete".

Any help would be greatly appreciated.

 

[Hurkk]

Here is how I would do it:
Please note here that the status is in Column F.
IF(AND(E2<TODAY(),F2<>"Complete"),"TRUE","FALSE")
Copy this function in the conditional formatting.
Select the range you want to set the conditional formatting --> Conditional format --> New Rule --> use a formula to determine ... --> Paste the formula and set the format color.

I hope this helps,

Hurkk

 

[Roderick_E]

Using conditional formatting, click on the entire column E something like

=or(e1>today(),<>"Complete")

 

[jekl1000]

So sorry for the errors. Column E is the Due Date and Column F is the Status. Thank you.

 

[Hurkk]

Sorry the right formula is this:
=IF(AND(E2<TODAY(),F2<>"Complete"),"TRUE","FALSE")

 

[Hurkk]

Sorry the right formula is this:
=IF(AND(E2<today(),f2<>"Complete"),"TRUE","FALSE")

=IF(AND(E2<TODAY(),F2<>"Complete"),"TRUE","FALSE")
For some reason it doesn't want to copy it.

Sorry

</today(),f2<>

 

[jekl1000]

Thank you Hurkk! This kept giving me a "The formula you typed contains an error" message though.

 

[jekl1000]

I ended up tweaking Roderick's and got it to work. I used =AND(E3<TODAY(),F3<>"Complete").

Thank you everyone!!