3

I'm having a complete brain fart trying to figure this out. Let's say I have 600 man-days left of work to complete, and this is located in cell E3. I want to calculate the target date for completion based on a given team size. Currently I'm doing this with

=WORKDAY(TODAY(), E3/6.5) 

but this only works for a fixed team-size of 6.5. However, I know that for the next 30 days of this project, I'm going to have a team size of 10.

Is there a formula I can use that will give me the target date while factoring in the staffing change?

0
2

Is there a formula I can use that will give me the target date while factoring in the staffing change?

Here is how you can modify the formula:

  • If 30 is the number of business days: =WORKDAY(TODAY(), ((30/10 +(E3-30))/6.5))

  • If 30 is the number of calendar days:

    1. Use this formula in another cell (say D19) to calculate the number of business days in the 30 calendar days: =NETWORKDAYS(TODAY(),TODAY() + 30)

    2. Then your formula for the target date will be: =WORKDAY(TODAY(), ((D19/10 +(E3-D19))/6.5))

4
  • No, though I'm not sure why that would be relevant. But thanks for the formula.
    – Leif.
    Jul 20 '15 at 18:08
  • Actually, I do have a question -- does this take into account that the 30 days at full staffing are calendar days, when subtracting from E3? It seems to me that this is simply subtracting 30 man-days from the E3 total? Sorry if this is a dumb question, spreadsheet formulas are not my strong point.
    – Leif.
    Jul 20 '15 at 18:18
  • I edited my response with an option of 30 calendar days. Jul 20 '15 at 19:47
  • Actually it looks like neither formula works, because the 30 is already the number of calendar workdays, so dividing it by 10 is unnecessary. As well, the parenthesis gave the wrong order of operations. But, it did help me hash out the correct formula, which is =WORKDAY(TODAY(), (330/10 +(E3-330)/7.5)), which I verified manually outputting the correct answer.
    – Leif.
    Jul 20 '15 at 20:14
0

Ended up figuring out that it was =WORKDAY(TODAY(), (330/10 +(E3-330)/7.5)) that gives me the answer that I want.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.