Dear Team,

I have employee attendance data as below

Given;
A column emp id
B column emp name
C column reporting1
D column reporting 2
E column in time for day 1
F column out time for day 1
G column working mins for day 1

I have used below formula to count number of days employee are late that is after 10 am & early logout that is before 6 pm.

For 10 am
=symproduct((mod(column(E5:BL5),3)=2)*(E5:BL5time (10,0,))))

For <6pm
Last function <time(6,0,)

Question;

1. After 10 am formula counts blank cells also (if an employee absent in time cell is blank)

2. After 10 am formula not counting time after 12 noon. Observed for one employee in time is 12:41 pm. But this not included in the count formula.