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!

 

[smartin]

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!

Try this, must be array* entered:

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

*Commit the array formula by pressing Ctrl+Shift+Enter; do not just
press Enter or Tab.

 

[John]

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

 

[John]

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

 

[smartin]

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

I would get rid of merged cells if I were you. If merged cells is
absolutely critical to the formatting/presentation, a work-around might
be to do the calculations on a worksheet with no merged cells, then look
up the values on a formatted worksheet.

BTW "merge and center" can effectively be replaced with no merged cells.
Select the cells in question and apply Format | Cells | Alignment |
Horizontal | Center Across Selection.

 

[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?

 

[smartin]

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.

 

[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.

 

[smartin]

Big help! Thanks!!!...

Small correction/clarification--

I said

SMALL returns the k (A3)'th member of the array,

Actually SMALL returns the k'th smallest member of the array. Since the
array of dates is in ascending order, this is functionally equivalent to
the k'th member where FALSE values are ignored.