Rolling Count

[errollflynn]

Hello,

I have a dataset that contains pay period starting and ending dates as well as the shift. I am trying to figure out how to identify instances where an employee has worked a minimum of ten shifts within a 6 month period. The challenge seems to be in calculating a rolling total of sorts and aligning that with a date difference of some type. I'm honestly stumped so any help would be appreciated.

Book3
	A	B	C	D	E	F
1	Key	Full Name	Hoursworked	Shift Count	pay_period_start	pay_period_end
2	.8482	., DEX	63.7	12.7	04/03/22	04/16/22
3	.8482	., DEX	60.94	12.2	04/10/22	04/23/22
4	.8482	., DEX	57.61	11.5	04/17/22	04/30/22
5	.8482	., DEX	23.54	4.7	04/24/22	04/30/22
6	SMIT4768	SMITH, JOHN	9.92	2	01/02/22	01/08/22
7	MAY4768	SMITH, JOHN	4.61	0.9	01/09/22	01/15/22
8	MAY4768	SMITH, JOHN	6.51	1.3	01/16/22	01/22/22
9	MAY4768	SMITH, JOHN	9.62	1.9	01/30/22	02/05/22
10	MAY4768	SMITH, JOHN	5.25	1.1	02/06/22	02/12/22
11	MAY4768	SMITH, JOHN	8.15	1.6	02/06/22	02/19/22
12	MAY4768	SMITH, JOHN	4.62	0.9	02/06/22	02/12/22
13	MAY4768	SMITH, JOHN	4.44	0.9	02/13/22	02/19/22
14	MAY4768	SMITH, JOHN	15.5	3.1	02/13/22	02/19/22
15	MAY4768	SMITH, JOHN	5.22	1	02/20/22	02/26/22
16	MAY4768	SMITH, JOHN	13.21	2.6	02/20/22	03/05/22
17	MAY4768	SMITH, JOHN	7.06	1.4	02/27/22	03/05/22
18	MAY4768	SMITH, JOHN	17.16	3.4	02/27/22	03/05/22
19	MAY4768	SMITH, JOHN	8.08	1.6	03/06/22	03/12/22
20	MAY4768	SMITH, JOHN	13.59	2.7	03/06/22	03/12/22
21	MAY4768	SMITH, JOHN	11.26	2.3	03/13/22	03/19/22
22	MAY4768	SMITH, JOHN	14.24	2.8	03/13/22	03/26/22
23	MAY4768	SMITH, JOHN	4.83	1	03/20/22	03/26/22
24	MAY4768	SMITH, JOHN	11.54	2.3	03/20/22	03/26/22
25	MAY4768	SMITH, JOHN	4.35	0.9	03/27/22	04/02/22
26	MAY4768	SMITH, JOHN	8.24	1.6	03/27/22	04/02/22
27	MAY4768	SMITH, JOHN	6.89	1.4	04/03/22	04/09/22
28	MAY4768	SMITH, JOHN	11.31	2.3	04/03/22	04/09/22
29	MAY4768	SMITH, JOHN	9.19	1.8	04/10/22	04/23/22
30	MAY4768	SMITH, JOHN	18.47	3.7	04/10/22	04/16/22
31	MAY4768	SMITH, JOHN	3.22	0.6	04/17/22	04/23/22
32	MAY4768	SMITH, JOHN	27.49	5.5	04/17/22	04/30/22
33	MAY4768	SMITH, JOHN	8.73	1.7	04/24/22	05/07/22
34	MAY4768	SMITH, JOHN	19.82	4	04/24/22	04/30/22
35	MAY4768	SMITH, JOHN	12.55	2.5	05/01/22	05/07/22
36	MAY4768	SMITH, JOHN	4.2	0.8	05/08/22	05/14/22
37	MAY4768	SMITH, JOHN	14.52	2.9	05/08/22	05/21/22
38	MAY4768	SMITH, JOHN	17.54	3.5	05/15/22	05/21/22
39	MAY4768	SMITH, JOHN	4.07	0.8	05/22/22	05/28/22
40	MAY4768	SMITH, JOHN	13.85	2.8	05/22/22	05/28/22
41	MAY4768	SMITH, JOHN	9.73	1.9	06/12/22	06/18/22
42	MAY4768	SMITH, JOHN	4.51	0.9	06/19/22	06/25/22
43	MAY4768	SMITH, JOHN	4.51	0.9	06/26/22	07/02/22
44	MAY4768	SMITH, JOHN	17.44	3.5	06/26/22	07/02/22
45	MAY4768	SMITH, JOHN	9.77	2	07/03/22	07/09/22
46	MAY4768	SMITH, JOHN	4.66	0.9	07/10/22	07/16/22
47	MAY4768	SMITH, JOHN	4.92	1	07/24/22	07/30/22
48	MAY4768	SMITH, JOHN	4.95	1	07/24/22	07/30/22
49	MAY4768	SMITH, JOHN	4.53	0.9	07/31/22	08/06/22
50	MAY4768	SMITH, JOHN	6.18	1.2	08/07/22	08/20/22
51	MAY4768	SMITH, JOHN	9.42	1.9	08/07/22	08/13/22
Sheet1

 

[awoohaw]

What do you define a 6 month period as? There could be as many as 7 different 6 month period permutations.
-6 to 0, -5 to 1, -4 to 2, -3 to 3, -2 to 4, - 1 to 5, 0 to 6. And it can vary with each pay period.