Consecutive Days Off Assistance

[ZvK]

Hello Everyone,

For my company, employees are required to take 10 consecutive days off. I can't seem to get the formula to count consecutive days (including weekend & holidays working). For example, Employee 2 should be 7 days, counting 12/25, 12/26, and 12/27. Here's the formula I tried to use: {=SUMPRODUCT(--($A$2:$A$28119=D4),--($B$3:$B$28120*($A$3:$A$28120=D4)<>WORKDAY(N(IF({1},$B$2:$B$28119)),1)))}. Is it possible to also identify the start of the consecutive date and end with a formula? Thank you!!

NAME	TIME OFF DATE		NAME	# of Days Consecutive	Start Date	End Date
Employee 1	12/01/2020		Employee 1	2
Employee 1	12/02/2020		Employee 2	5
Employee 2	12/14/2020		Employee 3	5
Employee 2	12/22/2020		Employee 4	1
Employee 2	12/23/2020		Employee 5	7
Employee 2	12/24/2020		Employee 6	2
Employee 2	12/28/2020		Employee 7	4
Employee 3	12/21/2020		Employee 8	9
Employee 3	12/22/2020		Employee 9	6
Employee 3	12/23/2020		Employee 10	6
Employee 3	12/24/2020		Employee 11	9
Employee 3	12/28/2020		Employee 12	4
Employee 4	12/04/2020
Employee 5	12/02/2020
Employee 5	12/03/2020
Employee 5	12/09/2020
Employee 5	12/14/2020
Employee 5	12/18/2020
Employee 5	12/24/2020
Employee 5	12/31/2020
Employee 6	12/30/2020
Employee 6	12/31/2020
Employee 7	12/28/2020
Employee 7	12/29/2020
Employee 7	12/30/2020
Employee 7	12/31/2020
Employee 8	12/01/2020
Employee 8	12/02/2020
Employee 8	12/03/2020
Employee 8	12/04/2020
Employee 8	12/07/2020
Employee 8	12/08/2020
Employee 8	12/09/2020
Employee 8	12/10/2020
Employee 8	12/11/2020
Employee 9	12/07/2020
Employee 9	12/08/2020
Employee 9	12/09/2020
Employee 9	12/10/2020
Employee 9	12/11/2020
Employee 9	12/24/2020
Employee 10	12/23/2020
Employee 10	12/24/2020
Employee 10	12/28/2020
Employee 10	12/29/2020
Employee 10	12/30/2020
Employee 10	12/31/2020
Employee 11	12/18/2020
Employee 11	12/21/2020
Employee 11	12/22/2020
Employee 11	12/23/2020
Employee 11	12/24/2020
Employee 11	12/28/2020
Employee 11	12/29/2020
Employee 11	12/30/2020
Employee 11	12/31/2020
Employee 12	12/21/2020
Employee 12	12/22/2020
Employee 12	12/23/2020
Employee 12	12/24/2020