Seeking Formula help to count consecutive weekday absences

[dms11463]

test worksheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Employee ID	Worker	Type	Time Off Date	Approved Hours		need to count consecutive WEEKDAYS absent at each change in Employee ID - using the absence dates in Column H and using the Saturday/Sunday weekends	Expected Response	Note - actual worksheet will have over 50,000 rows
2	100339	Aalia	Vacation	4/20/2023	8			1
3	100339	Aalia	Day of Service	4/21/2023	8			2
4	100339	Aalia	Vacation	4/24/2023	8			3
5	100339	Aalia	Sick	4/27/2023	2			1
6	100339	Aalia	Vacation	4/28/2023	8			2
7	10136	Abbi	Sick	1/27/2023	8			1
8	10136	Abbi	Sick	2/17/2023	6.07			1
9	10136	Abbi	Sick	2/22/2023	3			1
10	10136	Abbi	Sick	2/23/2023	3			2
11	10136	Abbi	Sick	3/1/2023	1.92			1
12	10136	Abbi	Sick	3/10/2023	8			1
13	10136	Abbi	Vacation	3/15/2023	8			1
14	10136	Abbi	Vacation	3/24/2023	8			1
15	10136	Abbi	Vacation	3/27/2023	8			2
16	10136	Abbi	Sick	3/29/2023	2			1
17	10136	Abbi	Sick	4/5/2023	6			1
18	10136	Abbi	Sick	4/21/2023	1.5			1
19	11767	Abby	Vacation	1/6/2023	3.48			1
20	11767	Abby	Vacation	3/13/2023	7.73			1
21	11767	Abby	Vacation	3/13/2023	0.02			1
22	13215	Abby	Sick	1/4/2023	2.5			1
23	13215	Abby	Sick	1/5/2023	8			2
24	13215	Abby	Sick	1/6/2023	7.25			3
25	13215	Abby	Sick	1/25/2023	7			1
26	13215	Abby	Vacation	2/3/2023	4			1
27	13215	Abby	Sick	2/10/2023	7.47			1
28	13215	Abby	Sick	3/1/2023	1.7			1
29	13215	Abby	Sick	3/13/2023	8			1
30	13215	Abby	Sick	3/16/2023	1			1
31	13215	Abby	Sick	3/17/2023	0.89			2
32	13215	Abby	Sick	3/27/2023	7.19			1
33	13215	Abby	Sick	4/3/2023	8			1
34	13215	Abby	Sick	4/18/2023	1			1
35	13215	Abby	Sick	4/19/2023	8			2
36	13215	Abby	Vacation	4/20/2023	8			3
37	13215	Abby	Vacation	4/21/2023	8			4
38	13215	Abby	Sick	4/28/2023	1			1
39	14064	Abigail	Vacation	1/3/2023	8			1
40	14064	Abigail	Vacation	1/31/2023	8			1
41	14064	Abigail	Vacation	2/1/2023	8			2
42	14064	Abigail	Vacation	2/2/2023	4.5			3
43	14064	Abigail	Vacation	2/23/2023	4			1
44	14064	Abigail	Vacation	2/24/2023	8			2
45	14064	Abigail	Vacation	3/13/2023	8			1
46	14064	Abigail	Vacation	3/14/2023	8			2
47	14064	Abigail	Vacation	3/15/2023	8			3
48	14064	Abigail	Vacation	3/16/2023	8			4
49	14064	Abigail	Vacation	3/17/2023	8			5
50	14187	Addie	Day of Service	2/17/2023	8			1
51	14187	Addie	Vacation	3/27/2023	8			1
52	14187	Addie	Vacation	3/28/2023	8			2
53	14187	Addie	Vacation	3/29/2023	8			3
54	14187	Addie	Vacation	3/30/2023	8			4
55	14187	Addie	Vacation	3/31/2023	8			5
56	13457	Adrian	Vacation	2/17/2023	8			1
57	13457	Adrian	Sick	2/22/2023	5			1
58	13457	Adrian	Vacation	4/3/2023	8			1
59	13457	Adrian	Vacation	4/4/2023	8			2
60	13457	Adrian	Vacation	4/5/2023	8			3
61	13457	Adrian	Vacation	4/6/2023	8			4
62	13457	Adrian	Vacation	4/7/2023	8			5
63	12908	Adriana	Sick	1/16/2023	6.1			1
64	12908	Adriana	Sick	1/17/2023	8			2
65	12908	Adriana	Sick	2/1/2023	7			1
66	12908	Adriana	Sick	4/7/2023	7			1
67	100356	Adriana	Vacation	2/17/2023	8			1
68	100356	Adriana	Vacation	2/21/2023	2.26667			1
69	100356	Adriana	Vacation	3/22/2023	7			1
70	100356	Adriana	Vacation	4/18/2023	8			1
71	100356	Adriana	Sick	4/21/2023	7			1
72	12290	Aggi	Vacation	1/3/2023	8			1
73	12290	Aggi	Sick	2/2/2023	8			1
74	12290	Aggi	Day of Service	2/3/2023	8			2
75	12290	Aggi	Sick	2/28/2023	0.6			1
76	12290	Aggi	Vacation	3/13/2023	8			1
77	12290	Aggi	Vacation	3/14/2023	8			2
78	12290	Aggi	Vacation	3/15/2023	8			3
79	12290	Aggi	Vacation	3/16/2023	8			4
80	12290	Aggi	Vacation	3/17/2023	8			5
81	10137	Aileen	Vacation	1/3/2023	8			1
82	10137	Aileen	Vacation	1/4/2023	8			2
83	10137	Aileen	Vacation	1/5/2023	8			3
84	10137	Aileen	Vacation	1/6/2023	8			4
85	10137	Aileen	Vacation	1/9/2023	8			5
86	10137	Aileen	Sick	1/12/2023	1.716666			1
87	10137	Aileen	Day of Service	1/30/2023	8			1
88	10137	Aileen	Vacation	2/2/2023	8			1
89	10137	Aileen	Vacation	2/3/2023	5.683333			2
90	10137	Aileen	Sick	2/14/2023	1.499999			1
91	10137	Aileen	Vacation	2/15/2023	6.316667			2
92	10137	Aileen	Sick	2/24/2023	3.166666			1
93	10137	Aileen	Sick	2/28/2023	2.933334			1
94	10137	Aileen	Vacation	4/7/2023	8			1
95	10137	Aileen	Sick	4/11/2023	3.549999			1
96	100362	Aimee	Vacation	2/13/2023	8			1
97	100362	Aimee	Vacation	2/14/2023	8			2
98	100362	Aimee	Sick	2/21/2023	1			1
99	100362	Aimee	Sick	3/21/2023	1.3			1
100	100362	Aimee	Vacation	4/4/2023	1.55			1
101	100362	Aimee	Sick	4/17/2023	8			1
102	12970	AJ	Sick	1/25/2023	1			1
103	12970	AJ	Day of Service	1/26/2023	8			2
104	12970	AJ	Sick	1/31/2023	2			1
105	12970	AJ	Sick	2/7/2023	1			1
106	12970	AJ	Vacation	3/8/2023	8			1
107	12970	AJ	Vacation	3/9/2023	8			2
108	12970	AJ	Sick	3/22/2023	1			1
109	12970	AJ	Sick	3/31/2023	2			1
110	12970	AJ	Sick	4/4/2023	1			1
111	12970	AJ	Vacation	4/5/2023	8			2
112	12970	AJ	Vacation	4/6/2023	8			3
Sheet1

 

[dms11463]

Hello - I need assistance with a formula to count the consecutive number of weekdays an employee is absent, even when it crosses calendar weeks. Example, if an employee is out Thursday/Friday of one week, then Monday/Tuesday of the next, the count would need to be 4.

My full file will have over 50,000 lines of data, so manually counting this is too cumbersome.

 

[Arturthemaster]

Use subtotal, this function can sum the workers, but you have to order the ID

 

[dms11463]

Use subtotal, this function can sum the workers, but you have to order the ID

This will only count the total number of absences. I need to count the CONSECUTIVE weekdays absent.

 

[Arturthemaster]

The subtotal will have this format.

 

[dms11463]

View attachment 90773
The subtotal will have this format.

This does not solve the need, or the question asked. I need by employee ID in Column A, the count of Consecutive Weekdays Absent for that specific worker using the dates in Column D. I have my sample data loaded, and the expected totals. SO I need a complex formula that will determine if the dates in Column D are consecutive WEEKDAYS (including dates that are from separate work weeks)

 

[Arturthemaster]

unt the CONSECUTIVE weekdays absent.

So it have to create another table and sum with sumif (in portuguese, somase)

 

[dms11463]

So it have to create another table and sum with sumif (in portuguese, somase)View attachment 90774

Arthurthemaster - this is still not the formula I am seeking and too many steps. Please allow others to respond to this issue. Again - I need to determine if the DATES are Consecutive Weekdays even if they cross calendar weeks.

 

[Arturthemaster]

Its realy complex, have a lot of details, and Excel can´t do that. Maybe with programming in VBA, i am a begginer in that and I can´t help u, but I tried

 

[dms11463]

I tried this type of formula - but it appears I either did something incorrectly, or the formula is missing a component. This is the type of formula I am seeking help with - this would have the employee ID in 2 columns - both in Column A and in Column D, and the DATES of the absence in Column B in this formula example.