Finding and adding together overlaps in dates from

问题:

I have 2 tables on my excel workbook. One to track tasks and one to track leave. I want to see the impact leave would have on each tasks (i.e. the total number of leave days of a resource that would fall within the start and end dates of a task)

The table on the left contains the tasks. The table on the right contains the leaves. What formula can I use in the column 'E' to obtain the total number of leave days that would fall within the start and end dates of a task corresponding to a particular resource?

Notes:

• Working days are from Monday - Friday (leave impact from only
  weekdays should be considered)
• Holidays should not be considered in
  the calculation.
• Total leave will be inclusive of start and end
  dates.

I'd appreciate greatly any help on this.

Thanks!

回答1:

Apologies for late posting but this has taken a bit of thought. One way to do it is to generate an array of all the days in November, then test the array elements one at a time against each pair of leave dates to see if they fall within those dates, using countifs. So the basic formula is

=SUMPRODUCT(COUNTIFS($H$2:$H$7,$A2,$I$2:$I$7,"<="&ROW(INDIRECT($C2&":"&$D2)),$J$2:$J$7,">="&ROW(INDIRECT($C2&":"&$D2))))

However we want to ignore weekends, so to do this I set all weekend dates to zero before counting up the matches

=SUMPRODUCT(COUNTIFS($H$2:$H$7,$A2,$I$2:$I$7,"<="&ROW(INDIRECT($C2&":"&$D2))*(WEEKDAY(ROW(INDIRECT($C2&":"&$D2)),2)<=5),$J$2:$J$7,">="&ROW(INDIRECT($C2&":"&$D2))))

A fairly long formula but it seems to give the correct results.

Columns E, L and M are for checking only and are not part of the final result in column F.

回答2:

I used this "array formula" in E2 confirmed with CTRL+SHIFT+ENTER and copied down

=SUM((H$2:H$7=A2)*TEXT(NETWORKDAYS(IF(I$2:I$7>C2,I$2:I$7,C2),IF(J$2:J$7<D2,J$2:J$7,D2)),"0;\0"))

This checks that the Resource matches, and for all matching rows does a NETWORKDAYS calculation (which ignores weekends) within any overlapping date periods between leaves and tasks.

Where there is no overlap a negative value is returned so TEXT function is used to convert the negatives to zeroes.

SUM function sums the resulting array to give you the required total - see screenshot

If you want to allow blanks in the "leaves" end date field then you can use this adjusted version which will assume end date is today

=SUM((H$2:H$7=A2)*TEXT(NETWORKDAYS(IF(I$2:I$7>C2,I$2:I$7,C2),IF(IF(J$2:J$7="",TODAY(),J$2:J$7)<D2,IF(J$2:J$7="",TODAY(),J$2:J$7),D2)),"0;\0"))

...or you can replace the two instances of TODAY() with another date or cell reference containing a date