Referring to the row where a running sum exceeds a threshold

Hello


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

Good afternoon,

I do not have a complete solution, but hopefully I can provide some assistance. I believe the function you are needing is called WORKDAY. This is the opposite of the NETWORKDAYS function (also very helpful, probably worth a stroll through Microsoft's page on it). Anyway, it looks something like this:

= WORKDAYS ( ---Start Date--- , ROUNDUP ( Effort / Efficiency *** I would suggest making a table for this *** , 0) , ---Holiday Range---)

The only part that I can't seem to work out is the dynamic holiday range. I had thought that this could be accomplished with an index-match but I've been unsuccessful.

*NOTE* The formula does return 7/8/14 for the second example. The way I interpret this is that all of 7/7/14 is exhausted at the time the ticket is done. You could subtract something really small from the efficiency calculation in the middle if you don't want this.

Sorry that it's kind of a ragged solution, but it's all I can piece together :-/

Best of luck,