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.

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

Hi again,

Am Sat, 9 Sep 2017 20:49:55 +0200 schrieb Claus Busch:

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,,)))

sorry, the second formula in the first answer is wrong.
Try:
=SUMPRODUCT(--(MOD(COLUMN(E5:BL5),3)=2),--(E5:BL5<""),--(E5:BL5TIME(10,,)))
and
=SUMPRODUCT(--(MOD(COLUMN(F5:BL5),3)=0),--(F5:BL5<""),--(F5:BL5<TIME(18,,)))


Regards
Claus B.
--
Windows10
Office 2016

Thank you for your reply.

Thank you again for your promptness.

Formula working.

On Sunday, 10 September 2017 00:24:10 UTC+5:30, Claus Busch wrote:
Hi again,

Am Sat, 9 Sep 2017 20:49:55 +0200 schrieb Claus Busch:

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,,)))

sorry, the second formula in the first answer is wrong.
Try:
=SUMPRODUCT(--(MOD(COLUMN(E5:BL5),3)=2),--(E5:BL5<""),--(E5:BL5TIME(10,,)))
and
=SUMPRODUCT(--(MOD(COLUMN(F5:BL5),3)=0),--(F5:BL5<""),--(F5:BL5<TIME(18,,)))


Regards
Claus B.
--
Windows10
Office 2016

Claus... I am Still Using your work out done for me.... Really Appreciates your Prompt help in this forum