Identify 6th working day out of 7 consecutive working days

[Angel_n]

Hi All,
Please help!
I need a formula to identify any 6th and 7th working days out of 7 working days for F column. So if an employee works on 6th and 7th day need to pay overtime in P column.
However, if skipping one day on 7 working days, the next working day will be counted minus 1. Sorry if it is confusion, below is an example if an employee does not work on that 4th day, it would not be counted. 7th working day will be come day 6 to be paid overtime.

 

[Armstrong_N14]

Hope this helps

Book3
	A	B	C
1	Attendance	Date	Working Days
2	W	9/15/2023	1
3	W	9/16/2023	2
4	W	9/17/2023	3
5	W	9/18/2023	4
6	W	9/19/2023	5
7	O	9/20/2023
8	O	9/21/2023
9	W	9/22/2023	1
10	W	9/23/2023	2
11	W	9/24/2023	3
12	W	9/25/2023	4
13	W	9/26/2023	5
14	W	9/27/2023	6
15	W	9/28/2023	7
16	O	9/29/2023
17	O	9/30/2023
18	W	10/1/2023	1
19	W	10/2/2023	2
20	W	10/3/2023	3
21	W	10/4/2023	4
22	W	10/5/2023	5
Sheet1

Cell Formulas
Range	Formula
C2:C22	C2	=IF(A2="w",IFERROR(IF(C1>=7,1,C1+1),1),IF(A2="O","",IF(C1>7,1)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C22	Expression	=OR(C2=6,C2=7)	text	NO

 

[Angel_n]

Thanks a lot! I will try

 

[Angel_n]

Hi Armstrong, thanks a lot.

However, actually I would like to find any 6th or 7th shift in any 7 days (no need consecutive working days)
Below highlighted yellow is the desired result, when there is a non-working day in between, it is still counted within any 7 days but not counted to find 6th or 7th shift.

SOrry about the earlier confusion. Hope you could help!

 

[Armstrong_N14]

this works?

Book1
	A	B	C
1	Attendance	Date	Working Day
2	W	9/15/2023	1
3	W	9/16/2023	2
4	W	9/17/2023	3
5	W	9/18/2023	4
6	W	9/19/2023	5
7	O	9/20/2023
8	O	9/21/2023
9	W	9/22/2023	6
10	W	9/23/2023	1
11	W	9/24/2023	2
12	W	9/25/2023	3
13	W	9/26/2023	4
14	W	9/27/2023	5
15	W	9/28/2023	6
16	O	9/29/2023
17	O	9/30/2023
18	O	10/1/2023
19	W	10/2/2023	1
20	W	10/3/2023	2
21	W	10/4/2023	3
22	W	10/5/2023	4
Sheet3

Cell Formulas
Range	Formula
C2:C22	C2	=IF(A2="W",IFERROR(IF(C1=6,1,C1+1),(IF(MAX($C$1:C1)+1>6,1,MAX($C$1:C1)+1))),"")