Calculating dates based on changing priorities

Question

I'm trying to figure out a formula which backs up a start date from the due date based upon the hours (converted to workdays) each task takes. That's easy, just subtract them.

What's makes it complicated is: the formula needs to take into account the other tasks assigned to that same person and then sort them based upon each person's priorities.

So, in the image below, Gru has 3 tasks, Priorities 1, 2, and 6. In order for Gru to finish his first priority on time, I can't just take the end date and subtract the 40 hours, because that will tell Gru he can start Priority 1 one week before the due date, but he has 60 hours in Priority 2 and 6 unaccounted for.

So the formula would have to basically find all of Gru's tasks, sort them by priority, tally up the total time (in workdays) and then make Priority 1's start date take into account all those hours.

i.e., Gru has to start Priority 1 100 hours before the due date (40 + 40 + 20) in order to finish on time, because Priority 2 and 6 are adding 60 hours of work (8 workdays) Gru has to do. He can't work on all 3 at the same time, so lower priorities push the start date of higher priorities back to account for the time those lower priorities take to complete.

Likewise, Priority 2 needs to be started 60 hours before due date because he needs time to finish Priority 6, which, being the last, would show a start date of 20 hours before due date, since he can do those 20 hours just before the due date.

Additionally, the formula needs to be dynamic as I'm using Filter to create the below image (pulling from another spreadsheet), and at any time Dr. Nefario or one of the minions could take over one of Gru's tasks, which, when you make that name switch, the dates should auto update to reflect the new owners and new priorities since Gru doesn't have all 3 tasks now.

I hope all that makes sense... I'm thinking the answer is in some sort of conglomerate between filter, sort, and sequence, but like Kevin sticking his finger into a light socket, my brain fritzes whenever I try to start down any of these formula paths. Any help would be greatly appreciated!!

Expected results with basic formulas displayed to the right:

Answer 1

The following assumes the tasks are ordered by priority, as in your example:

=WORKDAY(D$2,-(SUMIF(B4:B$9,B4,C4:C9))/8)

The above assumes an 8 hour work shift

Sample: