How to find if there is 7th day working attendance

[Nagaraja]

I have attendance file for employees with attendance marking as Present, P, Weekly OFf, WO and leave. At max, an employee is supposed to work consecutively for 6 days. One is not supposed to work on 7th day consecutively. How do I check if there is a 7th day working attendance? Start day 21st of previous month and end date is 20th of current month. Please check attached file.

 

[vletm]

Nagaraja
For me,
from Your named marks,
only 'Present P',
would mean something
which I could call as 'working day'.

Hmm ... 'only' John seems worked 'consecutively for 6 days'?

 

[GraH - Guido]

if you don't mind a helper table:

in [C13]= IF(OR(C4="P",AND(C4="P",D4="P")),N(B13)+1,0)
drag down and across

Conditional format: =MAX($C13:$AF13)>=7, highlights the names in red.

 

[GraH - Guido]

Nagaraja

Using this clever solution from @vletm

- as CF =COUNTIF(C4:I4,"P")>=7
- applied to =$C$4:$AF$10

Highlights the start of period (first "P") where employee worked 7 or more days, and colours the number of "P"s over 6.

 

[Peter Bartholomew]

Similar to @vletm but set to conditionally format the violations.
= SUM( N( last7days="P" ) ) =7

 

[Peter Bartholomew]

I think the solution with COUNTIF is better so I have changed my workbook so that the name 'exceeded?' refers to
= COUNTIF( last7days, "P" ) = 7
and 'last7days' is
= Sheet1!RC:RC[-6]

p.s. I switched to R1C1 because A1 is a pile of junk!

 

[Peter Bartholomew]

Whilst I think of it, conditional formatting has one or two extra tricks up its sleeve. Besides formatting fill, border and font properties, one can also change the number format so that cells that trigger the formatting rule show a designated text string rather than the "P" that is the actual cell value. The format I tried is
;;;"VEL"
standing for 'Violation of Employment Law' or maybe it should just be company employment practice.

 

[Nagaraja]

Thank you all for your time and response to my first request. Much appreciated.