Condtional formatting for dates.

[jayjay2022]

Hi everyone,

I have added another word to my dropdown menu and condiational formatting so I can clear expired dates when they are complete.

Currenlty when I choose Completed from the drop down menu it removes the pink highlight from the date so I know I don't need to chase them up. I am wanting to add an extra word "Cancelled" so this can also remove the highlited format from the date column

Example below

My current code - this works.

I have tried to duplicate the formula but when I do it then highlights everything, making the formula void. I have tried adding to the same formula but I think my syntax is incorrect.

Can someone help please?
Thanks

 

[jayjay2022]

I tried adding this code " =OR(AND(H2<>"Completed","Cancelled",A2<TODAY()-1)) but still no change. Need help.
Thanks

 

[Peter_SSs]

Can you clarify: Is your conditional formatting adding the pink to certain cells or is it removing pink that was previously applied manually?

Can you confirm that it is column A that you have shown us?

Could you show us the data again but include column H as well to help us understand? You can hide the intervening columns if data is sensitive.

Better still, can you give us the sample data (hiding intervening columns again) with XL2BB so that we can better see what you have and where and we can also copy that data for testing if required?

 

[jayjay2022]

Can you clarify: Is your conditional formatting adding the pink to certain cells or is it removing pink that was previously applied manually?

Can you confirm that it is column A that you have shown us?

Could you show us the data again but include column H as well to help us understand? You can hide the intervening columns if data is sensitive.

Better still, can you give us the sample data (hiding intervening columns again) with XL2BB so that we can better see what you have and where and we can also copy that data for testing if required?

Below is a screenshot of what happens when I apply completed (dropdown menu)

Column A is already highlighted when no data is present in the cell - just a reminder there formatting on column A

When data is present and has a current date, the pink highlight will go disappear. After two days Column A will re-highlight pink again unless "Completed" is used in column H

I am now wanting column A to the same when I use "Cancelled". I hope this make sense.

Date formatting.xlsx
	A	B
1	29/03/2022	Completed
2	30/03/2022	WIP
3	30/03/2022	Parts
4	1/04/2022	1st approval
5	11/04/2022	Cancelled
6	11/04/2022	Completed
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B:B	Cell Value	contains "Cancelled"	text	NO
B1:B6	Cell Value	contains "Parts"	text	NO
A1:A6	Expression	=AND(B1<>"Completed",A1<TODAY()-1)	text	NO
B1:B6	Cell Value	contains "3rd approval "	text	NO
B1:B6	Cell Value	contains "2nd approval "	text	NO
B1:B6	Cell Value	contains "1st approval "	text	NO
B1:B6	Cell Value	contains "WIP"	text	NO
B1:B6	Cell Value	contains "Completed"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B1:B6	List	Cancelled, Parts , 1st approval , 2nd approval , 3rd approval , WIP , Completed

 

[Peter_SSs]

Thanks for the XL2BB sample.

Is this what you want for column A then?

22 05 29.xlsm
	A	B
1	29/03/2022	Completed
2	30/03/2022	WIP
3	30/03/2022	Parts
4	1/04/2022	1st approval
5	11/04/2022	Cancelled
6	11/04/2022	Completed
7
8
9
10
CF Dates

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A10	Expression	=AND(B1<>"Completed",B1<>"Cancelled",A1<TODAY()-1)	text	NO

 

[jayjay2022]

Thanks for the XL2BB sample.

Is this what you want for column A then?

22 05 29.xlsm
	A	B
1	29/03/2022	Completed
2	30/03/2022	WIP
3	30/03/2022	Parts
4	1/04/2022	1st approval
5	11/04/2022	Cancelled
6	11/04/2022	Completed
7
8
9
10
CF Dates

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A10	Expression	=AND(B1<>"Completed",B1<>"Cancelled",A1<TODAY()-1)	text	NO

Thank you so much. This worked a treat.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.