We have a ticket based system for assigning work out to people in our team. Each ticket is updated with an estimate of effort expressed in days, and at the point we assign it to a person, we need to calculate the expected duration for that person to complete it.

We have a list of people, and those people will have a daily availability multiplier - this relates to how much of their day can reasonably be spent working on tickets. We also have a list of the week day availability for those people for the next n weeks. This is calculated from a list of their own personal holidays and a list of public holidays and weekends, and includes a daily availability multiplier (0 for when they're on holiday on a weekday or are sick).

I've attached an example spreadsheet with 3 tables

View attachment calculation example.xlsx

tab_week_day_availability - Each Individual's daily availability

tab_ticket_assignment - List of tickets and who they are assigned to

tab_pers_non_working_day - list of non-working days for each person

tab_ticket_assignment is where I need to calculate the expected complete date and I'm struggling to devise a formula to do this. I had thought to use WEEKDAY but I can't see how to pass in the range of holidays only for the person that the ticket is assigned to. Also that still doesn't help with the calculation of how much time a person can actually spend on issues for any one day.

Does anyone have any ideas on how to do this with a formula? I'm happy to code it in VBA if necessary but it feels like there is something that can be done with formulas...

Thank you in advance

David