How can I calculate the completion date?

**michael.k** · 06-23-2015, 04:53 PM

Hello, I have what seems a simple question, but it drives me crazy and I can not figure it out.

Here is the situation.

I have a project that Will take 252 work days to complete if 1 crew is working. I need to complete the project much sooner, so I want to have 4 crews working. 3 crews working the full time of of the project, and 1 crew working the partial time. I want the project to be over in 83 work days or sooner.

How can I setup, to manipulate the amount of days each crew will work, where the output will give me the total work days it would take to complete the project, with the current crew schedule, and show the completion date?

Seems pretty simple, but I just cant figure it out. Thank you for support.

**Pete_UK** · 06-23-2015, 08:06 PM

If you had just 3 crew working on the project, this would take them 252/3 (=84 days), which is longer than you would like, so you would have to employ the 4th crew for 3 days, so that the other 3 crew would each be working for 83 days. This is the maximum time you can allow for the project.

If 4 crews worked on the project, it would take them 252/4 (=63 days), but as you only want the 4th crew to work for part of the time, you would have to take 3 days off them (60 days), so that the other three crews would then each work for 64 days. This is the shortest time to complete the project.

There are many other solutions in between, where you can reduce the time for the 4th crew by 3 days and increase the time for the other crews by 1 day each.

If you have a start date, then you can add the number of workdays onto it (plus non-workdays) to get the completion date.

Hope this helps.

Pete

**6StringJazzer** · 06-23-2015, 08:11 PM

If it takes 252 days for 1 crew, and you want it to take 83 days, then 252/83 will tell you how many crews you need. In this case the answer is 3.036144578. So you need 3 full-time crews, plus one crew for only 4% of their time. Another way to figure this is you need 3 full-time crews (249 labor-days) plus one more crew for 3 days to get to 252.

Your question goes on to say you want to know the total days, but you have already said the total days are 83 so I'm unclear on what information you are starting with and what results you want. Then you mention dates, which means you need to use the WORKDAY function.

If you attach the Excel file you're working with I can give a more Excel-specific answer.

**michael.k** · 06-23-2015, 08:30 PM

Pete_UK and 6StringJazzer, thank you both for replying. What you both are saying makes sense, but here is where my difficulty is:

It does make sense to just do manual math, but here is what I need to be calculated automatically.

If I employ the crews for:

1st crew plan: 15 weeks = 75 work days
2nd crew plan: 15 weeks = 75 work days
3rd crew plan: 15 weeks = 75 work days
4th crew plan: 10 weeks 50 work days

Total 275 work days.

I only have 252 work days worth of work, so if I plan to employ the crews for that amount of time, what will be my new project end date? How many work days would it take for the current setup of crews to perform 252 days worth of work? It would be sooner than the 83 days. What will be my new overall project end date, and how many days will I have before my overall project deadline.

I can do manual math, but I need it to be done automatically. Things will shift. For example, once in a while the 2 day job can turn into a 3 day job, I need everything to be recalculated automatically, to ensure I am on track, and I employ the 4th crew enough days, to have a safety margin of no work planned, to be able to go back to some projects and finish up things missed. Or one of the weeks, one of the crews may have to do something different, so the crew will no longer work the full 15 weeks, but 14 weeks. Basically I need it to be dynamic, and keep counting my projected completion date. I hope this makes sense, it makes perfect sense in my head, I just don't know how to set it up logically in excel.