Hi,

Am Sat, 9 Sep 2017 11:27:48 -0700 (PDT) schrieb TIMOTHY:

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.

try:
=SUMPRODUCT(--(MOD(COLUMN(E5:BL5),3)=2),--(E5:BL5<""),--(E5:BL5TIME(10,,)))
and
=SUMPRODUCT(--(MOD(COLUMN(E5:BL5),3)=2),--(E5:BL5<""),--(E5:BL5<TIME(18,,)))


Regards
Claus B.
--
Windows10
Office 2016