Help on Days worked within criteria

[ElfordB]

Hey all,

Was hoping some one could help with this as i cant get my head around it.

I have a set of data (pictured) where each day i have an employee number tagged if the are active, i als have how many weeks they have been active.
What im looking for is to create a formula that will, (on each day within each week per employee number) show the working day number of that week as shown to the right of the picture as an example.

Hope that explains what im trying to do and look forward to hearing some advise

TIA

DRTtobeCLEANed20221115.xlsx
	A	B	C	D	E	F
1	WEEK_COMMENCING	ACTIVE_DATE	EMPLOYEE	WKS_NUM		Like This
2	18/09/2022	16/09/2022	101001046	1		1
3	18/09/2022	17/09/2022	101001046	1		2
4	25/09/2022	20/09/2022	101001046	2		1
5	25/09/2022	21/09/2022	101001046	2		2
6	25/09/2022	22/09/2022	101001046	2		3
7	25/09/2022	23/09/2022	101001046	2		4
8	25/09/2022	24/09/2022	101001046	2		5
9	02/10/2022	26/09/2022	101001046	3		1
10	02/10/2022	27/09/2022	101001046	3		2
11	02/10/2022	28/09/2022	101001046	3		3
12	02/10/2022	29/09/2022	101001046	3		4
13	02/10/2022	30/09/2022	101001046	3		5
14	02/10/2022	01/10/2022	101001046	3		6
15	21/08/2022	18/08/2022	101010103	1		1
16	21/08/2022	19/08/2022	101010103	1		2
17	21/08/2022	20/08/2022	101010103	1		3
18	28/08/2022	22/08/2022	101010103	2		1
19	28/08/2022	23/08/2022	101010103	2		2
20	28/08/2022	24/08/2022	101010103	2		3
21	28/08/2022	25/08/2022	101010103	2		4
22	28/08/2022	26/08/2022	101010103	2		5
23	28/08/2022	27/08/2022	101010103	2		6
24	04/09/2022	30/08/2022	101010103	3		1
25	04/09/2022	31/08/2022	101010103	3		2
26	04/09/2022	01/09/2022	101010103	3		3
27	04/09/2022	02/09/2022	101010103	3		4
28	04/09/2022	03/09/2022	101010103	3		5
Sheet2

 

[Fluff]

Hi & welcome to MrExcel.
How about

=COUNTIFS(C$2:C2,C2,D$2:D2,D2)

 

[ElfordB]

Hi & welcome to MrExcel.
How about

=COUNTIFS(C$2:C2,C2,D$2:D2,D2)

I think thats it, i cant believe i didnt think of that and was trying to be overly complicated.
Thank you fluff thats awesome!

 

[Fluff]

Glad to help & thanks for the feedback.