Conditional Format to Highlight Row Where Date in Column Within 5 Business Days of Range

[mac_squared]

Hello All,

I have a table that lists items extracted in sharepoint. In one column is that created date of an item and a named range that contains our quarterly release dates. I need a conditional format, that will highlight the row if the Created Date is within 5 BUSINESS DAYS of the Target Release Date, any idea on how to accomplish this?

Capture.PNG

[TMS]

Use the same CF formula that you have used for the date created column. Easy way is to edit the range that the CF is applied to.

[Root_]

See if the following CF rule works for you:

=ABS(NETWORKDAYS(F22,B22,Holidays))<=5

where Holidays is a named range that contains holiday dates.

[mac_squared]

Use the same CF formula that you have used for the date created column. Easy way is to edit the range that the CF is applied to.

There is no CF formula, I just highlighted the cells to produce the desired outcome.

[mac_squared]

See if the following CF rule works for you:

=ABS(NETWORKDAYS(F22,B22,Holidays))<=5

where Holidays is a named range that contains holiday dates.

This formula does not highlight anything .

[mac_squared]

See if the following CF rule works for you:

=ABS(NETWORKDAYS(F22,B22,Holidays))<=5

where Holidays is a named range that contains holiday dates.

I found the issue. So I need B22 to refer to a named range, as opposed to a value in an adjacent column, is that possible?

[Root_]

Assuming range B22:B30 in your example is named Release_Dates, try the following CF rule:

=OR(ABS(NETWORKDAYS(F22,+Release_Dates,Holidays))<=5)