Highlight cell if date has passed

[DTbox]

I know this seems simple but I've been researching this for a few hours and I'm come up blank. I'm looking to do the following two things with conditional formatting.

1. I need a cell in Row B to go RED if the planned end date has passed and YELLOW if the planned end date is within 14 days of the date in the cell.
2. Regardless of the date in Row B, if the status shows CLOSED in Row C, the cell in Row B goes back to normal, not showing a color.

Everything I've read online shows me how to do this based on todays date only. I don't want that. I need it based off the date in the Row B. Any help would be much appreciated.

 

[kevin9999]

Your "applies to " is out of alignment. Instead of $B$3:$B$11 it should read $B$2:$B$11. That's why you've getting incorrect results. I'll get back to you tomorrow if you still can't get it to work, after 2 am here...

 

[dreid1011]

Weird, don't know why it was showing that. I corrected it and still have incorrect colors/conditions.

B1 should be red.
B4 should not be colored because the status is CLOSED.
B5 should be yellow.
B7 should not be colored because the status is CLOSED.
B8 should be red.
B9 should be yellow.

View attachment 101194

It's because your "Applies to" range is offset 1 row down. Shouldn't it be $B$2:$B$11, not $B$3:$B$11?

 

[DTbox]

Compare your formula to what I have in post #4. It's different.

I have it figured out as follows. Now the only part left is a formula that tells the conditional format in F to be ignored completely if H shows anything other than OPEN. Meaning I only want F to have a conditional format if the status of the task is in an OPEN status.

 

[kevin9999]

=AND(F3="OPEN",F3<TODAY())
Also, I recommend you don't apply CF to an entire column.

 

[DTbox]

=AND(F3="OPEN",F3<TODAY())
Also, I recommend you don't apply CF to an entire column.

Did as suggested and it's not turning the cell with 9/22/2023 to show no conditional formatting.

 

[DTbox]

=AND(F3="OPEN",F3<TODAY())
Also, I recommend you don't apply CF to an entire column.

Here's an image showing the formulas entered.

 

[kevin9999]

And again the formula you put in the CF isn't the formula I posted. You put in F5 in the top rule not F3...

 

[kevin9999]

Here's another mini sheet with your new data layout (i.e. different from your original post) and your new formatting rules. Copy it to an empty worksheet, then you can copy the format to your actual file.

Book1
	F	G	H
1
2	Planned End Date	Task End Date	Status
3	10/29/2023		OPEN
4	11/5/2023		OPEN
5	11/16/2023		OPEN
6	10/29/2023		CLOSED
7	11/5/2023		CLOSED
8	11/16/2023		CLOSED
9
10
11
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H3:H10	Cell Value	contains "OPEN"	text	YES
F3:F11	Expression	=AND(H3="OPEN",F3<TODAY())	text	YES
F3:F11	Expression	=AND(H3="OPEN",(F3-TODAY())<=14)	text	YES

 

[DTbox]

And again the formula you put in the CF isn't the formula I posted. You put in F5 in the top rule not F3...

I apologize, not trying to waste your time. I'm clicking on "3" not sure why 5 keeps appearing.

 

[kevin9999]

I understand the CF doesn't copy from the XL2BB so you won't be able to copy the format. In any event, look at post #18. They are the formulas to use. "OPEN" is never in column F, it's in column H.