How do I apply conditional formatting to date based on the text in an adjacent and today's date?

0
votes

I have a list of dates in a "Deadline" column in column E.

I have a "Status" column in column F, where the cell value are text. The text values are "Needs drafting", "Revisions required","In progress", "Draft sent for review","Review pending","Approval pending","Complete".

I want to format the "deadline" cells (column E) based on if the date in the given "deadline" cell is less than or equal to today AND if the "status" in the adjacent cell is equal to any of the following: "Needs drafting", "Revisions required","In progress", "Draft sent for review","Review pending","Approval pending".

So essentially, if the deadline is today or has passed and the status isn't "complete", then I want to format that deadline cell to be red bold italicized.

In the conditional formatting rule manager, I attempted to use the following formula to no avail, since the conditional formatting doesn't allow arrays:

=AND(MATCH(F2,{"Needs drafting","Revisions required","In progress","Draft sent for review","Review pending","Approval pending"},0),E2<NOW())

Any ideas?

Thank you, Brittany

1
excelconditional-formatting

1 Answers

0
votes

How about putting the phrases together into a string instead of an array?

=AND(ISNUMBER(FIND(F2,"Needs drafting|Revisions required|In progress|Draft sent for review|Review pending|Approval pending")),E2<=TODAY())

or use Search to ignore case. I've changed the < to <= and the NOW to Today.

Please add dollar signs if you want to highlight both columns.

=AND(ISNUMBER(FIND($F2,"Needs drafting|Revisions required|In progress|Draft sent for review|Review pending|Approval pending")),$E2<=TODAY())

enter image description here

From the information provided, it also sounds as if this would be an easier option

=AND($F2<>"Complete",$E2<=TODAY())