Conditional formatting based on date and status

[theguyIsHere]

How do I get the finish date column to be highlighted in Red based on the following criteria, please:

- If the date is in the past (AND)
- status does not equal 'Complete', 'on hold' ?

I can do conditional formatting for the date but not the second item Many thanks

Project Name	Finish Date	Project Status
Project 1	20 Oct 2022	Complete
Project 2	30 Sep 2022	Red
Project 3	15 Dec 2022	Green

 

[etaf]

=AND( C2<>"complete" , B2 < today() , B2<>"" )

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
C2:C100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:

=AND( C2<>"complete" , B2 < today() , B2<>"" )

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

The reason for the B2<>"", is so that RED is not in blank cells - a blank cell is seen as 1/1/1900 so less than today - and c2 will be blank - so it will highlight - Hence B2<>""

 

[theguyIsHere]

Thank you. That's awesome.

This syntax does not seem to cover the other conditions...what is missing please?

=AND( OR(C2<>"complete" C2<> "On Hold"), B2 < today() , B2<>"" )

 

[Fluff]

How about

=AND(C2<>"complete",C2<>"On Hold",B2<TODAY(),B2<>"")

 

[theguyIsHere]

Thank you. Magic

 

[Fluff]

Glad we could help & thanks for the feedback.