Scheduling Projects

[Skip2Maloo]

Hi all,

We schedule our projects backwards... we have contracted completion
dates and must work backwards from there to determine a start date. We
can closely approximate the number of days each project would take us,
so this is an example of what I need:

Delivery Date - Number of Days = Start Date

The problem I'm having is that the "Number of Days" needs to be "Number
of WORKING days" to exclude weekends and holidays so that when that
number is subtracted from Delivery Date is gives us a correct Start
Date. The paradox is that to use the WORKDAYS or NETWORKDAYS function
we already need to know the Delivery Date and Start Date, the latter of
which hasn't been computed yet. I hope this makes sense, but here's an
example how I need it to work:

(input) Delivery Date: 07/21/06
(input) # of Days: 15
(computed) Start Date: 07/03/06

If we entered it in just as you see it, the Start Date would actually
compute to 07/06/06, not 07/03/06. We need to tell it to count
backwards 15 WORKING DAYS, but I can't figure this one out. I suppose
if I spent a couple days on it I might come up with a questionable
formula, but I was just wondering if anyone out there had a more
elegant solution.

Thanks.

p.s. And no... MS Project won't work for us

 

[Chip Pearson]

You can use negative numbers with WORKDAY. For example

=WORKDAY(delivery_date,-1*number_of_days,holidays)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com