Overlapping Dates per Name

Hi There,

Below I have a table start and end dates for specific employees.

I have 43 days overlapping, but this does not take into account the the names, but rather the entire list. (Using this array formula {=SUM(IF(FREQUENCY(IF((MIN(Start)+COLUMN(OFFSET($B$1, , 0, , MAX(End)-MIN(Start)+1))-1<=End)+(MIN(Start)+COLUMN(OFFSET($B$1, , 0, , MAX(End)-MIN(Start)+1))-1>=Start)>1, MIN(Start)+COLUMN(OFFSET($B$1, , 0, , MAX(End)-MIN(Start)+1))-1, ""), MIN(Start)+COLUMN(OFFSET($B$1, , 0, , MAX(End)-MIN(Start)+1))-1)>1, 1, 0))}.

(Note: the above formula has not been fully tested, but appears to work)

"Start" and "End" dates have been defined as names.

My first challenge is to use the formula above, but only sum the days that pertinent to the "Name", not the entire list

"Overlaps with" - is the number of other transactions (row/line items) relating to the same employee that overlap. (2nd challenge)

"First Overlap" - is the first line (duplicate name) (from top to bottom) that it overlaps with. (3rd Challenge). (I realize another column with transaction number would be required in this case)

I would prefer to avoid VBA and use formulas. Any assitance would be greatly appreciated as I know this is quite a toughie:)


Name Start End overlaps with by how many days First Overlap
Liur, ANC_00005 2013/04/08 2013/04/18 43.00
Bob, AB_00002 2013/04/19 2013/04/29 43.00
Bob, AB_00002 2013/04/30 2013/05/10 43.00
Beoln, ABD_00004 2013/05/11 2013/05/21 43.00
Liur, ANC_00005 2013/04/08 2013/04/18 43.00
Beoln, ABD_00004 2013/04/19 2013/04/29 43.00
Beoln, ABD_00004 2013/04/30 2013/05/10 43.00
Beoln, ABD_00004 2013/05/11 2013/05/21 43.00
Beoln, ABD_00004 2013/05/22 2013/06/01 43.00
Beoln, ABD_00004 2013/06/02 2013/06/12 43.00
Beoln, ABD_00004 2013/06/13 2013/06/23 43.00
Beoln, ABD_00004 2013/06/24 2013/07/04 43.00
Beoln, ABD_00004 2013/07/05 2013/07/15 43.00
Beoln, ABD_00004 2013/07/16 2013/07/26 43.00
Beoln, ABD_00004 2013/07/27 2013/08/06 43.00

Thanks,
ENDO123

Originally Posted by WinteE

Use SUMPRODUCT() instead of SUM() and IF(). This gives you the opportunity to sum on multiple criteria.

Thanks! I gave it a bash, with no luck though. Still finding my way around the sumproduct function.

=SUMPRODUCT(FREQUENCY(IF((MIN(Start)+COLUMN(OFFSET($B$1,,0,,MAX(End)-MIN(Start)+1))-1<=End)+(MIN(Start)+COLUMN(OFFSET($B$1,,0,,MAX(End)-MIN(Start)+1))-1>=Start)>1,MIN(Start)+COLUMN(OFFSET($B$1,,0,,MAX(End)-MIN(Start)+1))-1,""),MIN(Start)+COLUMN(OFFSET($B$1,,0,,MAX(End)-MIN(Start)+1))-1)>1,1,0)*(Name=A8)

This formula gave me a value error. I'm not sure I've gone about the SUMPRODUCT function above in the correct fashion. Could you perhaps point me in the right direction?

Thanks! that sorts out the number of overlaps per name. I had to tweak the formula a little:
=SUMPRODUCT((Start<=D8)*(End>=C8)*(Name=B8))-1

would you be in a position to help me out with the others?

Namely the first line item it overlaps with and the number of days it overlaps by;

LineNumbeEmployee_Sys Start End Overlaps FirstOverlap NumberofDays
_0001 Bobette, ABN_00003 2013/04/08 2013/04/18 1 _0002 43
_0002 Bobette, ABN_00003 2013/04/17 2013/04/27 1 _0001 43
_0003 Huir, A_00006 2013/04/28 2013/05/08 1 43
_0004 Huir, A_00006 2013/05/09 2013/05/19 1 43
_0005 Huir, A_00006 2013/04/04 2013/07/13 2 43

Originally Posted by WinteE

I guess something with INDEX and MATCH, but I don't know enough of these to solve this for you.

No worries. Thanks anyway for the help