Find number of overlapping days per person

Hello

I have a data set which contains:

Names on Column A
Start Date on Column B
End Date on Column C
Team Name on Column D
Role on Column E

There are multiple entries for the same names on Column A as some players are part of different teams. I need to find how many days a name on Column A was part of Team A and Team B at the same time.

What do you want to happen when the player was on teams A & B for the exact same period of time (e.g. Shirley)? Do you want to return the value (48 days) twice (F2 & F3), not at all, or just for one team (if so, A or B?)?

The formula below works for me. Put it in F2 and fill down. It should return the overlap hours. If the player was on two teams for the exact same duration, it should show the overlap in the row for Team A while leaving the entry for Team B blank to avoid a duplicate listing. It works for me on your sample, though it does not account for drastic differences like a third team.

=IFERROR(IF(SUMPRODUCT(--($A:$A=$A2),--($B:$B=$B2),--($C:$C=$C2))>1,IF($E2="Team A",DATEDIF($B2,SUMPRODUCT(--($A:$A=$A2),--($E:$E<>$E2),--($B2>=$B:$B),$C:$C),"D"),""),DATEDIF($B2,SUMPRODUCT(--($A:$A=$A2),--($E:$E<>$E2),--($B2>=$B:$B),$C:$C),"D")),"")