Lookup and Count Array Formula Query

Morning all. Struggling to figure out a formula that does everything that I need it to. I'm almost certain this is possible, but as there's several parts to this formula, I'm struggling to wrap my head around it.

So, I have a 3x tables. In one table (the table name is Tbl_Att), I have 4x columns. Let's call this the Attendance Table. One is headed "Employee", and is a list of unique Employee names. Then the next two columns are headed "From Date" and "To Date". These contain a date range. The final column, is headed "HR Absences". It is this column that I would like help creating a formula for. Let's call this the Formula Column.

In another table, which is called Tbl_PHs, there is just 1x column headed "Date", and this contains a list of dates. These are dates for UK Public Holidays. Let's call this table the Public Holidays Table.

The third table is titled Tbl_Abs. Let's call this the Absences Table. This contains 4x columns. The first is headed "Full Name". The next is headed "Duration". The final two are headed "Request Start Date" and "Request End Date". These contain a date range.

What I would like, is a formula to put in the Formula Column, which does the following:

• Looks for the Employee name (from the Attendance Table) in the Full Name column of the Absences Table.
• Checks whether any of the dates falling within the date range on the Attendances Table are found within the data range (for that Employee) on the Absences Table. This then needs to somehow return an array of all of those dates that overlap between the ranges (i.e. if in the Attendance Table for a particular Employee, there was a date range of 1st Jan to 31st Jan, and then in the Absences Table there were two entries for that particular Employee, and the date ranges were the 7th to 8th Jan, and 29th Jan to 5th Feb, then the dates to return in that array would be 7th Jan, 8th Jan, 29th Jan, 30th Jan and 31st Jan).
• This then needs to look at this array, and omit from the array any Saturdays and Sundays (I imagine using NETWORKDAYS or similar).
• This then needs to look at the dates in the Public Holidays Table, and omit these dates from the array where these are found.

The formula then needs to return a sum / count of the dates that remain in the array. This should assume that each date has the value 1.

However, if a particular date in the Absences Table has a value in the Duration column of less than 1, then that date is assigned that value when summing the total of the dates, instead of 1.

So if the array of dates was just the 16th and the 25th Jan, but for the 16th Jan the duration stated 0.5, the figure returned in the Formula Column for this Employee would be 1.5.

Hopefully this makes sense. I've attached a simplified version of this workbook, which should contain all of the relevant fields.

Thanks in advance.