Leadtime for jobs based on available resources

Hi,

I have attached a workbook in which I calculate leadtimes for jobs.

All workers have a start and finish date, as well as a capacity percentage for that given availability (see table 1)
Each job has a defined duration in number of Man Days, i.e. the number of work days to complete the task for one FTE (full time employee), as well as a start date for the job. (see table 3).

By using the calculations given in table 2, I am able to come up with the following results:

- Duration for the job in number of work days based on the assumption that all available resources are using their capacity to contribute to the job, e.g. doubling the number of FTEs will cut the duration in half. (cell D23)
- Duration for the job in number of calendar days. (cell E23)
- Finish date for the job based on start date + number of calculated calendar days. (cell F23)

I want to bypass the need for table 2 for these calculations, and instead have formulas in cells D23, E23 and F23 which calculates these results directly from what is given in table 1 and table 3.

Can someone have a look?

Best regards,
Marbleking

Hi AliGW,

I need to get rid of table 2 because I am going to calculate the results for a long list of jobs at once (using the previous job’s finish date as the start of the next, and so forth). Therefore, it will be very unpractical to have to use this method. Also, I need to get rid of table 2 to get more flexibility regarding additions and removals in table 1.

Regards,
Marbleking

Thanks, Bo_Ry! That works perfectly. I'll go with the first option. Thanks for the extra columns of formulas which made it easy to understand your thinking.

Regards,
Marbleking

Hi kvsrinivasamurthy,

An UDF would also be interesting to see for this, if you want to give it a try.

Regards,
Marbleking

Thanks, kvsrinivasamurthy! This is also a very useful solution. You have a point regarding table 2, and double counting of days when the "availability from dates" are equal to the previous "availabilty to dates". Correct would be "availability from dates" = "previous availabilty to dates" + 1.

In this example, we then get finish date 2 August 2022 for the defined job. But Bo_Ry's solution also seem correct, ending at 1 August 2022 when the needed 200 Man Days will be fulfilled (and slightly exceeded at some point that day). I am not able to see exactly why the finish dates differ.

Regards,
Marbleking

Thanks for the explanation, kvsrinivasamurthy! I'll mark this thread SOLVED. Regards, Marbleking