Calculate working days between two dates for multiple tasks

Hi,

I have 4 tasks that are done in an order. First task1, then task 2 so on. Each task takes certain number of days.

I have calculated Start & End date for each task using MIN & MAX from a list dates from Sheet 1 (from the beginning of task 1 till the end of last task 4).


For example

Sheet 2

A --------------- B --------------- C
(Task) (Start Date - MIN) (End Date - MAX)

Task 1 ------ 1-Jan --------- 6-Jan
Task 2 ------ 7-Jan -------- 12-Jan
Task 3 ------ 13-Jan -------- 14-Jan
Task 4 ------ 15-Jan -------- 16-Jan

Sheet 3

In this sheet, I have a "column D" for Dates, starting from 1-Jan till 16-Jan (beginning of Task1 - end of task 4) D2:D17
In the same sheet I have marked "W" for working day (works on Saturday as well), "H" for national holiday & "A" for Absent, "S"for Sunday in "column F"

D -------- E ------- F

1-Jan Friday ------ W
2-Jan Saturday --- W
3-Jan Sunday ----- S
4-Jan Monday ----- H
5-Jan Tuesday ---- A
6-Jan Wednesday - W
.
.
.
16-Jan Saturday --- W

Now, I want to calculate the number of working days ("W") between start & end date for each task. For Task1, it should first check if each cell in column D (D2:D17) is in between 1-Jan & 6-Jan, if yes, count "W" in column F.

Task2, between 7-Jan & 12-Jan etc...

As to the first query,try pasting this formula in AO10:

Please Login or Register  to view this content.

EDIT: as to the second query, assuming the names are the artists on sheet one, paste the following array formula in Sheet1!G311, press the F2 key, press the Ctrl, Shift and Enter keys simultaneously (the formula will be automatically be wrapped in curly brackets):

Please Login or Register  to view this content.

Let me know if you have any questions.

Originally Posted by Quasar82

Second query worked too, but it returned #DIV/0! error when applied to cells that also contain blanks. (Column O & S in Sheet1).

Modify the array formula (active with Ctrl, Shift and Enter) for G311 to read:

Please Login or Register  to view this content.

Copy to O311 and S311.
Let me know if you have any questions.