Array formula to treat range of Holiday Days as Weekends when adding data to Calendar

Hello,

I need help with this formula. Please see attached workbook.

The current array formula below looks at a table of data and displays information from the table on a calendar on either WEEKDAYS, WEEKENDS or EVERY DAY dependent on several variables such as whether the STATUS column is either PENCILLED, CONFIRMED, INVOICED or PAID

The formula looks like this (in cell BP28):

Please Login or Register  to view this content.

I now need to adapt this formula so that it also takes into consideration a list of HOLIDAY DAYS from the range $B$52:$B$70

I need days listed in this range to be treated exactly the same as a WEEKEND days are in the current formula.

If you look at the booking for Company A (in the row of I23) between dates 20/12/2017 and 31/12/2017 and compare to the Calendar view on the right, you'll see that the dates 25/12/2017 & 26/12/2017 need to be displaying the booking (as M23 is set to weekends) and 25/12/2017 & 26/12/2017 are listed in my Holiday Range in $B$52:$B$70.

(Currently the booking only displays on the weekends and not on Christmas Day & Boxing Day as desired)


I've been adapting other bits of code I have using NETWORKDAYS.INTL to specify the holiday range, but I can't figure out how to do it for this code.


Massive thanks in advance for any assistance on this.

Amazing. Than you so much. That works perfectly.