How to distribute a variable sum over cells with different starting values

[Berry Helmich]

Hi all,

Say I have 5 employees, that start at different times, for example...

13:30
13:30
13:45
14:00
14:00

...and I have a workload of a variable amount of hours. How could I use Excel to calculate when they will finish?

In this example, a workload of 5 hours would mean they should be finished at 14:45

13:30 > 14:45 = 1,25 hrs
13:30 > 14:45 = 1,25 hrs
13:45 > 14:45 = 1 hr
14:00 > 14:45 = 0,75 hr
14:00 > 14:45 = 0,75 hr
Which totals to 5 hrs

I'd like to be able to automate this, so that Excel calculates the time at which they finish, but I'm having some trouble with the fact that the starting times vary.
Does anyone know how I could set up a formula for this? Thanks in advance!

 

[Kaper]

If nobody starts too late to be involved (see the right columns on the picture with last person workon for no time - the last possible value for formula to work properly), and assuming the starting hours are in B2:B6 and the required total of 5:00 is in C2 then in D2 write formula:

=(C2-SUM(MAX(B2:B6)-B2:B6))/COUNT(B2:B6)+MAX(B2:B6)