Capacity Planner
Posted by Heather Goddard on September 21, 2001 9:02 AM
What kind of formula would i use to calculate a Projected Program End Date.
I have 4 people each using 5.5 hours a day for calling for a total of 22 hours per day. If they have 1000 calls to make and spend 4 minutes on each call that would give them a total of 67 hours for the program. I need a formula to calculate the end date if the program was started Oct 1/01. they do not work weekends.
Posted by Juan Pablo on September 21, 2001 9:39 AM
You can do this without a formula...if you start on Monday, Oct 1st, and you need 66.66667 hours to do the job (4000 min / 60 min), and you have 22 hours / day, that gives you 3.03030303 days to do the job (66.6667 / 22), right ?
So, just take 1/10/01 (In A1 for example), and in B1 put =A1 + 3.030303 and that gives you Thu, Oct 4th.
If you want the formula, use in B1 =NETWORKDAY(A1,66.6667 / 22) and you'll get the same result...
Juan Pablo
----------------------
Posted by Barrie Davidson on September 21, 2001 9:43 AM
Try using
=WORKDAY(A1,B2)
where A1 is your start date and B2 is your length of time the program will run. Note that B2 must represent time (i.e., you can't put in 67, you would have to enter 67/24).
Hope this helps you out.
Regards,
BarrieBarrie Davidson
Posted by Juan Pablo on September 21, 2001 10:59 AM
Barrie,
You should'nt put 67/24, because the days are 22 hour long (I mean, that's what she has available each day, 5.5 hours per worker, 22 in total)...
Juan Pablo
Posted by Barrie Davidson on September 21, 2001 11:10 AM
Good point but
if she calculates the total time is 67 hours you still need to divide by 24 to arrive at the number of days.
Regards,
Barrie
Posted by Juan Pablo on September 21, 2001 11:56 AM
Re: Good point but
I don't wanna pick a fight or anything, but i still disagree with you... with the same example:
We start at 9:00 am of Monday, Oct 1st, right ? we have 4 workers that will spend 5.5 hours doing this, so on monday, at 14:30 they've used their available time (22 hours between the four of them), and have made 330 calls (22 hours = 1320 minutes, divide by 4 minutes per call = 330 calls), they still need to do 670.
Tuesday, begin at 9:00am and end, again at 14:30, and have made another 330 calls, total of 660, still 340 to go.
Wednesday, another 330, total of 990, ten to go.
Thursday, begin at 9:00 am, they should be finished by 9:15am.... so that's 3.0303030 days (Being each day a 22 hour long day, not 24 hours), that's why you instead of using =WORKDAY(A1,67/24) should use =WORKDAY(A1,67/22)
Again, not trying to pick a fight or anything like that...
Juan Pablo
-----------------
Posted by Barrie Davidson on September 21, 2001 12:11 PM
No offense taken and..
after reading your explanation I think you're right (darn it, I hate making mistakes!).
Thanks Juan.
