Conditional Formatting Dates

what are we formatting - all 3 columns - just the c column

For Grey - Then B5 is NOT BLank - but C5 will be
SO one rule for that - would be
=AND( B5<>"" , C5 = "" )

RED
If no date in Col C, & the date in Col B is 3 weekdays or less than today. Format Red.
we can use NETWORKDAYS()

But in your example all the dates are in future

so just checking the example
Often mix things up with terms like less than

you would need 4 rules
1 for grey
1 for red
1 for amber
1 for green
and we can use something like

=Networkdays( B5, today() ) > 3

today is 30th march 21
Now the date in the cell B5 is 2nd April - so no colour
RED would be colored in if the date in B5 was
Friday 26/3 , Monday 29/3 , Tuesday 30/3

If thats not right - what dates should flag
is it for the future

so 3 work days in future from future - is 31, 1, 2. in B5 = red
so 4 - 7 days is 5,6,7,8 April in B5

Enter into conditional formatting in this order using New Rule>Use Formula...

Formula:

Please Login or Register  to view this content.

Check off "Stop if True"
See attached sheet. I assumed you wanted all the data in the row colored.

Thread marked as solved - remove the solved tag if you want further help.

It's not a rule - it's just advice. When threads are marked solved,people tend to stop looking at them.

you should be able to combine the rules into a nested IF

green: =NETWORKDAYS(TODAY(),$B5)-1>7

Amber: =AND(ISNUMBER($B5),NETWORKDAYS(TODAY(),$B5)-1<=7)

Red: =AND(ISNUMBER($B5),NETWORKDAYS(TODAY(),$B5)-1<=3)

Gray: =AND(ISNUMBER($B5),ISNUMBER($C5))

RED with a 3, Amber a 2, & Green a 1, & finally 0

something like - EDIT , like the conditional format - stop if true - the order is important
as < = 7 - is also < = 3 - so the 7 needs to come last so its not overriding the 3

=IF(C5 = "" , "" , IF( NETWORKDAYS(TODAY(),$B5)-1>7, 1, IF (AND(ISNUMBER($B5),NETWORKDAYS(TODAY(),$B5)-1<=3), 3, IF ( AND(ISNUMBER($B5),NETWORKDAYS(TODAY(),$B5)-1<=7) , 2 , 0 ))))

=IF(C5="","",IF(NETWORKDAYS(TODAY(),$B5)-1>7,1,IF(AND(ISNUMBER($B5),NETWORKDAYS(TODAY(),$B5)-1<=3),3,IF(AND(ISNUMBER($B5),NETWORKDAYS(TODAY(),$B5)-1<=7),2,0))))

you are welcome