Calculating End Date - Given working 4, 5, 6, or 7 day workweek.

[John]

I am having trouble figuring out how to calculate an end date, give the fact
that a work crew can work 4, 5, 6, or 7 days a week and the activity will
take X working days. Here's a simple setup:
A1 - Start Date (Provided by Crew)
A2 - Work Schedule (4,5,6 or 7 days a week)
A3 - Duration of Activity (in working days - 10 for example)
A4 - End Date (what I want to calculate)

All workdays start on Monday. So if an activity sarts on 9/28/09 and is 10
days long and the crew is working 4/10's (4 days a week, 10 hours a day),
then the calculated end date should be 10/13/09 because 10/02 - 10/04 is
non-work and 10/09 to 10/11 is also non-work.

Any help would be great!

 

[John]

Absolutly amazing!!! Thanks - just what I needed...

 

[John]

XL says it will not work in merged cells... anyway around this?

 

[John]

I can work around merged cells w/o any issues...

One more thing if you don't mind. (?) I thought I could figure out how this
works but I am not seeing how. I am not an XL guru. Can you explan briefly
how this works?

 

[John]

One other note... The part of the formula ...ROW(1:1000) when I copy it down
to other rows automatically incriments IE: ROW(2:1002), ROW(3:1003) etc.
Because I don't understand how the formula works, I don't know the affect
this has on the results. Does it matter?

 

[John]

Big help! Thanks!!!...


--
Thanks in advance!
**John**

One other note... The part of the formula ...ROW(1:1000) when I copy it down
to other rows automatically incriments IE: ROW(2:1002), ROW(3:1003) etc.
Because I don't understand how the formula works, I don't know the affect
this has on the results. Does it matter?

Yes, yes it does. Please Find & Replace "1:1000" with "$1:$1000" before
copying / filling the formula elsewhere. Also note 1000 is just an
arbitrarily large number to contain the maximum number of calendar days
a project is expected to span.

Here's how it all works. [Hint: this is much easier to follow with the
formula evaluator (Tools | Formula Auditing | Evaluate Formula). Also,
it is easier to follow on a very small sample. Try setting up a small
sample using ROW($1:$10), work week = 4 and duration = 5 and click your
way through the evaluator.]

On with the full solution:

=SMALL(IF((A1-1+ROW($1:$1000))*(WEEKDAY(A1-1+ROW($1:$1000),2)<=A2)<>0,A1-1+ROW($1:$1000)),A3)

As is best in most debugging procedures, I shall work from the inside-out:

ROW($1:$1000) in an array formula acts like a counter that takes on
values from 1 to 1000. Very useful, that.

A1-1+(counter) is your Date -1 + the counter. IOW, an array of dates
from A1 to A1 + 999. Let's call this array "MyDates". Our simplified
pseudo-formula now looks like

=SMALL(IF((MyDates)*(WEEKDAY(MyDates,2)<=A2)<>0,MyDates),A3)

WEEKDAY(MyDates,2) returns 1-7, with Monday=1 through Sunday=7.

Now, A2=#workdays per week. Since work weeks begin on Monday and work
days are consecutive per your spec, this lets us check

WEEKDAY(MyDates,2)<=#workdays

which returns a boolean TRUE or FALSE. If TRUE, the date in question is
in the work week, otherwise FALSE. The TRUE or FALSE result is
multiplied by MyDates. The arithmetic coerces a numeric result, which is
either MyDates or 0.

IF checks to see if the result above <> 0. If so, MyDates is returned
(remember, MyDates is an array of values!) Otherwise, since there is
nothing in the ELSE clause, FALSE is returned.

Now the pseudo-formula reduces to

=SMALL(Array of dates and FALSE values,A3)

where A3 is the project duration.

SMALL returns the k (A3)'th member of the array, ignoring FALSE, so it
picks the A3'th member, with the non-work days removed from the array of
dates we generated with ROW($1:$1000).

Clear as mud, eh?

Hope it helps.