Count of time (attendance)
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. |
Count of time (attendance)
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 |
Count of time (attendance)
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 |
Count of time (attendance)
Thank you for your reply.
|
Count of time (attendance)
Thank you again for your promptness.
Formula working. |
Count of time (attendance)
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 |
All times are GMT +1. The time now is 07:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com