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.

 

[ranman256]

make formula to set D to the color name (hide it if you need):
YELLOW, if Date() <14 days
RED , if Date() > B

then set B color condition based on the word in D.

 

[DTbox]

make formula to set D to the color name (hide it if you need):
YELLOW, if Date() <14 days
RED , if Date() > B

then set B color condition based on the word in D.

If I do that using Date(), won't it pull the current date and not the date from the cell?

 

[kevin9999]

Does this give you what you want?

Book1
	A	B	C
1		Planned End Date	Status
2		10/29/2023	OPEN
3		11/5/2023	OPEN
4		11/16/2023	OPEN
5		10/29/2023	CLOSED
6		11/5/2023	CLOSED
7		11/16/2023	CLOSED
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B7	Expression	=AND(C2<>"CLOSED",B2<TODAY())	text	YES
B2:B7	Expression	=AND(C2<>"CLOSED",(B2-TODAY())<=14)	text	YES

 

[DTbox]

Does this give you what you want?

Book1
	A	B	C
1		Planned End Date	Status
2		10/29/2023	OPEN
3		11/5/2023	OPEN
4		11/16/2023	OPEN
5		10/29/2023	CLOSED
6		11/5/2023	CLOSED
7		11/16/2023	CLOSED
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B7	Expression	=AND(C2<>"CLOSED",B2<TODAY())	text	YES
B2:B7	Expression	=AND(C2<>"CLOSED",(B2-TODAY())<=14)	text	YES

How do I apply that to the entire column?

 

[DTbox]

Does this give you what you want?

Book1
	A	B	C
1		Planned End Date	Status
2		10/29/2023	OPEN
3		11/5/2023	OPEN
4		11/16/2023	OPEN
5		10/29/2023	CLOSED
6		11/5/2023	CLOSED
7		11/16/2023	CLOSED
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B7	Expression	=AND(C2<>"CLOSED",B2<TODAY())	text	YES
B2:B7	Expression	=AND(C2<>"CLOSED",(B2-TODAY())<=14)	text	YES

The problem I'm running into is it looks like I'll have to apply conditioning to every row separately. When I use your formula, it only applies to that one row. I can't copy that formula to the rest of the column because it'll apply what ever is in C2 to apply to all the cells in column B.

 

[kevin9999]

When setting up the conditional formatting, in the "Applies to" box, set the range to whatever you think you will need (not the entire column). The formula is not absolute referenced to C2 - it will automatically adjust down the column, as you can see from the example I gave.

 

[DTbox]

When setting up the conditional formatting, in the "Applies to" box, set the range to whatever you think you will need (not the entire column). The formula is not absolute referenced to C2 - it will automatically adjust down the column, as you can see from the example I gave.

This is what happens when I do that.

 

[kevin9999]

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

 

[DTbox]

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

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.