Projected Program End Date

Posted by Heather Goddard on September 21, 2001 1:54 PM

If there are 4 reps each calling 5.5 hrs per day with weekends off, how would you calculate projected End date for each program. For example program A begins Oct 1/01. There are 1000 calls to be made @ 4 minutes per call. Program A should end Oct 3/01. Program B would begin Oct. 4/01 and has 6000 calls to be made @ 2.5 minutes per call. End date should be Oct 19/01. Can the if function be used to calculate this and if so what would it be. Thanks

Posted by IML on September 21, 2001 2:26 PM

You need the analyst tool pak installed for the workday function.
A1 in start day. A2 is number of reps. A3 is hours per day the reps work. A4 is number of calls. A5 is call time in minutes
the formula should be
=WORKDAY(A1,A4/(A3*60*A2/A5))
In the first example, it returns a 10/4 end date. This is because after three days, the only would have completed 990 calls.

Good luck