Scheduling Projects
 

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 :)

Scheduling Projects
 

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



"Skip2Maloo" wrote in message
oups.com...
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 :)