Conditional Formatting excluding weekends

[Yoohoo567]

Hi - I've searched to try and solve it myself but I'm getting more confused.

I need a date in Column A to be RAG rated to indicate deadline. It needs to be green when date entered, turn yellow on day 6 and red on day 10. I need to exclude weekends which is the part I can't get to work.

Would appreciate any help - Thank you

 

[Fluff]

Hi & welcome to MrExcel.
Do you mean the date needs to turn yellow when it's 6 working days from today?

 

[Yoohoo567]

Hi & welcome to MrExcel.
Do you mean the date needs to turn yellow when it's 6 working days from today?

Hi - thank you.

Yes, 6 working days to yellow and 10 working days to red.

 

[Fluff]

Ok, how about

+Fluff 1.xlsm
	A
1
2	09/12/2021
3	06/12/2021
4	01/12/2021
5
6
7
8
Main

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A11	Expression	=AND(A2>=TODAY(),A2<=WORKDAY(TODAY(),6))	text	NO
A2:A11	Expression	=AND(A2>=TODAY(),A2<=WORKDAY(TODAY(),10))	text	NO

 

[Yoohoo567]

Thank you - I can't seem to get that to work. I think I probably wasn't clear as you have used dates in the future and mine are all today or in the past.

For example I will enter today's date in Column A when I receive an application - I would like it then to turn green.
When 6 working days have past, then to turn yellow
When 10 working days have past to turn red.

Would that be a different formula or am I just entering this incorrectly (which wouldn't surprise me as it has now frazzled my brain)

 

[Fluff]

Ok, how about

+Fluff 1.xlsm
	A
1
2	24/11/2021
3	17/11/2021
4	16/11/2021
5	11/11/2021
6	10/11/2021
7
8
9
10
Main

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A11	Expression	=AND(A2<>"",NETWORKDAYS(A2,TODAY())>10)	text	NO
A2:A11	Expression	=AND(A2<>"",NETWORKDAYS(A2,TODAY())>6)	text	NO
A2:A11	Expression	=A2<>""	text	NO

 

[Yoohoo567]

YES! Thanks for your help - that's made my day.

 

[Fluff]

You're welcome & thanks for the feedback.