Help highlighting adjacent cell ten business days (exclude holidays) after the date found in the cell beside it

[bananius]

Hello,

I have been trying to work on this with conditional formatting for a couple of days now to no avail

Here is a photo of the chart I'm working with:

I have made it so that every time a "FILE NUMBER" is entered the cell to the right in grey shows the date it was entered in dd-Mmm-yy (ex. file number is C8 date is entered in D8).

Now what I need is a macro or conditional formatting that will highlight the "Motion" (B, E...) and "FILE NUMBER" (C, F...) cells in red when 10 ***business/work days have passed from the initial date entered in the corresponding grey cell (D, G, J...).

I wanted to use WORKDAY, but the cells are filled at random dates so I don't know what the start date would be. It's not a project with a duration. I want to use today, but it doesn't incorporate workdays.

Please help! Thank you!

 

[jasonb75]

Not sure if I follow correctly, try applying conditional formatting to columns B and C using =(NETWORKDAYS($D8,TODAY())>10)/$D8 as the CF rule.

 

[bananius]

Not sure if I follow correctly, try applying conditional formatting to columns B and C using =(NETWORKDAYS($D8,TODAY())>10)/$D8 as the CF rule.

That does work, but can I create a range with that? It would have to be applied to cells $D8 to $D67 and other columns as well like $G8:$G67. Or would I have to include each manually?

 

[bananius]

Not sure if I follow correctly, try applying conditional formatting to columns B and C using =(NETWORKDAYS($D8,TODAY())>10)/$D8 as the CF rule.

and thank you so much, this is very helpful!

 

[jasonb75]

I haven't tried this but think it should work.

=OR(AND(B$6=$B$6,(NETWORKDAYS(D8,TODAY())>10)/D8),AND(A$6=$B$6,(NETWORKDAYS(C8,TODAY())>10)/C8))

Apply it as the rule formula to $B$8:$O$67.