finding 7th day working attendance

N

Nagaraj475

New member
Joined
Apr 6, 2014
Messages
29
Reaction score
0
Points
0
Location
India
Excel Version(s)
Excel2010
In my organisation an employee is not supposed work for 7 days consecutively. when updating attendance manually, it often happens that an employee is marked consecutively present for 7 days. How do we highlight 7th day working that excludes weekly offs and leaves but highlights only if value P is repeated 7 times consecutively. 1st day for Payroll starts from 21st of each month and ends on 20th of every month. Please check attached sample file.


Thanks,
NagarajaView attachment AttendanceSample.xlsxView attachment AttendanceSample.xlsx
 
Nagaraj475 said:
How do we highlight 7th day working that excludes weekly offs and leaves but highlights only if value P is repeated 7 times consecutively.
Click to expand...
See if this formula helps
Code: 
=AND(COUNTIF(A4:F4;G4)=6;LEN(G4)>0)
See attached file
 

Attachments

  • Nagaraj475-Navic.xlsx
    9.5 KB · Views: 15
Thank you for your help. While the logic appears to get result, it's not highlighting the 7th day of presence. Can you suggest correction? Thanks
 
Nagaraj475 said:
Thank you for your help. While the logic appears to get result, it's not highlighting the 7th day of presence. Can you suggest correction? Thanks
Click to expand...
I do not understand you. Sorry, English is not my mother tongue. What's the problem?
In the picture you can see that it is highlight every 7th day in a sequence.
Can you set the expected result (eg a red font for expected a specific day)

Nagaraj475.png
 
Thank you very much for your interst in helping. The formula is highlighting the first cell in the selected range. I wanted the one that's the 7th one. By the way, your web page is not opening. Can you share the correct address of your website.
 
Nagaraj475 said:
I wanted the one that's the 7th one. By the way, your web page is not opening. Can you share the correct address of your website.
Click to expand...
Again,

1. Select Range G4:AF9
2. Run CF
3. Put formula =AND(COUNTIF(A4:F4;G4)=6;LEN(G4)>0) or =AND(COUNTIF(A4:F4,G4)=6,LEN(G4)>0)
4. Format color
5. Apply

or apply Pecoflyer's solution

btw: My website has been migrated to the new server and the DNS settings have been changed.
It takes 24 hours for DNS addresses to be propagated? :(
 

Attachments

  • Nagaraj475-Navic2.xlsx
    9.9 KB · Views: 12
Hi Navic,

Thank you so much for your patience in helping me . Very well explained. Appreciate your time.

Regards,
Nagaraja
 
You must log in or register to reply here.
Back
Top