Conditional format using workday function

[gully176]

I’m looking for a very specific formula. My data would be laid out as such.

A5 would contain a date that a document was forwarded
B5 is the slot for the documents due date

I want multiple things to happen

I want B5 to auto populate a due date using the workday function that part is simple enough. BUT I want to set up 3 conditional format rules for this cell.

1. If cell B5 is populated with a date and cell C5 is empty then I want it to highlight B5 yellow to indicate it is the most current date
2. If cell B5 is populated with a date and cell C5 is empty and cell B5’s date is within 5 business days (excluding holidays so workday will be a must) then I want it to highlight B5 orange
3. If cell B5 is populated with a date and cell C5 is empty and Cell B5’s date is the current date or past the current date I want it to highlight Red

Can anyone help me out?

 

[yky]

What if B5 is not a date or C5 is not empty?

 

[gully176]

The only thing that would be placed in B5 is a date, if it is empty, then the cell should be left unformatted. If Cell C5 contains data, then it can remove all formatting already done by the rule.

 

[Fluff]

How about
=AND(B5<=TODAY(),C5="") for red
=B5<=WORKDAY(B5,5,Sheet1!A2:A10) for orange, change the part in red to look at your holiday list
=AND(B5<>"",C5="") for yellow

 

[gully176]

How about
=AND(B5<=TODAY(),C5="") for red
=B5<=WORKDAY(B5,5,Sheet1!A2:A10) for orange, change the part in red to look at your holiday list
=AND(B5<>"",C5="") for yellow

This is leaving the cell formatted Red until an entry is placed, which defeats the purpose as the red is a visual indicator for me.

 

[Fluff]

Sorry that should be
=AND(B5<=TODAY(),B5<>"",C5="")
=AND(B5<=WORKDAY(B5,5,Sheet1!A2:A10),B5<>"")