A possible solution is in attached workbook.



Conditional formatting is defined for column B. I think you can apply the same logic for other columns too. The colors for conditional formatting are selected as near of yours as was possible from default ones in my color palette. Holidays list is a fake one - I added the one you had in your example.

Edit: Weekday numbers in column E are calculated as ISO weekday numbers (European ones). With US weekday numbers the formulas in other columns will be much more complex, and this will affect workbooks performance when data amount increases.

Attached Files

092318_Excel_Forum_Sample_Spreadsheet.xlsx (17.7 KB, 20 views)