calculating the average Time Taken, with consecutive days and multiple employees

[AudyAnalyst]

Can anyone help create a formula showing the average length of time taken for each employee? For Example, "EMP2000" took 5/22 (1), 5/19 (1), 5/11 (1), & 3/3-3/1 (3), so, "EMP2000" average time off was (6/4) =1.5 days. The formula needs to be mindful of both the employee ID and the date Taken being sequential/consecutive.

Book3
	A	B	C
1	Employee ID	Time Off Date	Approved
2	EMP2000	5/22/2023	8
3	EMP2000	5/19/2023	8
4	EMP2000	5/11/2023	8
5	EMP2000	3/3/2023	8
6	EMP2000	3/2/2023	8
7	EMP2000	3/1/2023	8
8	EMP2007	2/1/2023	4
9	EMP2007	5/26/2023	8
10	EMP2009	4/6/2023	8
11	EMP2009	5/19/2023	8
12	EMP2009	5/20/2023	8
13	EMP2009	3/10/2023	8
14	EMP2009	3/9/2023	8
15	EMP2009	3/8/2023	4
16	EMP2009	3/1/2023	4
17	EMP2009	1/11/2023	4
18	EMP2016	1/5/2023	4
19	EMP2016	5/12/2023	8
20	EMP2016	5/11/2023	8
21	EMP2016	4/14/2023	8
22	EMP2016	4/15/2023	8
23	EMP2070	1/3/2023	8
24	EMP2070	5/17/2023	8
25	EMP2070	2/10/2023	8
26	EMP2070	2/9/2023	5.25
27	EMP2070	2/8/2023	8
28	EMP2070	2/7/2023	8
29	EMP2070	2/6/2023	8
Sheet1

 

[Jeffrey Mahoney]

I read the post wrong. Need to rework

 

[AudyAnalyst]

I have a resolution, but it's a bit clunky...

Dates taken v2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Employee ID	Time Off Date	Approved	Date Above	Date Below	If 1?	Seq?	True Seq	Max in Seq	If more than Max number and 0 or 1		If greater than 1 than number if not ""
2	EMP2000	5/22/2023	8	0	0	0	0	1	0	0	1	1				Employee ID
3	EMP2000	5/19/2023	8	0	0	0	0	1	0	0	1	1				EMP2000	2
4	EMP2000	5/11/2023	8	0	0	0	0	1	0	0	0					EMP2007	1
5	EMP2000	3/3/2023	8	1	0	1	1	2	0	0	0					EMP2009	2
6	EMP2000	3/2/2023	8	1	1	1	2	3	0	0	0					EMP2016	2.5
7	EMP2000	3/1/2023	8	0	1	1	3	4	4	4	4	4				EMP2070	3.5
8	EMP2007	2/1/2023	4	0	0	0	0	1	0	0	1	1
9	EMP2007	5/26/2023	8	0	0	0	0	1	0	0	1	1
10	EMP2009	4/6/2023	8	0	0	0	0	1	0	0	1	1
11	EMP2009	5/19/2023	8	0	0	0	0	1	0	0	0
12	EMP2009	5/20/2023	8	0	0	0	1	2	0	0	0
13	EMP2009	3/10/2023	8	1	0	1	2	3	0	0	0
14	EMP2009	3/9/2023	8	1	1	1	3	4	0	0	0
15	EMP2009	3/8/2023	4	0	1	1	4	5	5	5	5	5
16	EMP2009	3/1/2023	4	0	0	0	0	1	0	0	1	1
17	EMP2009	1/11/2023	4	0	0	0	0	1	0	0	1	1
18	EMP2016	1/5/2023	4	0	0	0	0	1	0	0	0
19	EMP2016	5/12/2023	8	1	0	1	1	2	0	0	0
20	EMP2016	5/11/2023	8	0	1	1	2	3	3	3	3	3
21	EMP2016	4/14/2023	8	0	0	0	0	1	0	0	0
22	EMP2016	4/15/2023	8	0	0	0	1	2	2	2	2	2
23	EMP2070	1/3/2023	8	0	0	0	0	1	0	0	1	1
24	EMP2070	5/17/2023	8	0	0	0	0	1	0	0	0
25	EMP2070	2/10/2023	8	1	0	1	1	2	0	0	0
26	EMP2070	2/9/2023	5.25	1	1	1	2	3	0	0	0
27	EMP2070	2/8/2023	8	1	1	1	3	4	0	0	0
28	EMP2070	2/7/2023	8	1	1	1	4	5	0	0	0
29	EMP2070	2/6/2023	8	0	1	1	5	6	6	6	6	6
Sheet1

Cell Formulas
Range	Formula
D2:D29	D2	=IF(AND(A2=A3,B2=B3+1),1,0)
F2:F29	F2	=IF(AND(D2=0,E2=0),0,1)
H2:H29	H2	=G2+1
I2:J29	I2	=IF(H2>H3,H2,0)
K2:K29	K2	=IF(H3>=2,0,H2)
L2:L29	L2	=IF(K2>=1,K2,"")
E3:E29	E3	=IF(AND(A3=A2,B3=B2-1),1,0)
G3:G29	G3	=IF(OR(F3=1,AND(A3=A2,B3=B2+1)),G2+1,0)
Q3:Q7	Q3	=AVERAGEIF(A2:A29,P3,L2:L29)

 

[fjns]

Hi, see the linked file (with an auxiliary column) for a possible solution...

The formulas used in the table...

D2: =IF(A2="","",IFERROR(IF(AND(A2=A1,B2=B1-1),0,1),1)) (Range: D2:D30 ...)
F2: =UNIQUE(A2:A29)
G2: =IFERROR(COUNTIF(A:A,F2)/SUMIF(A:A,F2,D:D),0) (Range: G2:G6)

TimeAverage.xlsx

 

[Peter_SSs]

Is your actual data really like your ample data?
I ask because in the sample of it below, the consecutive yellow days go up the column whereas the consecutive green days go down the column.
Is there any consistency with your actual data?