excel formula to calculate time I would like to present to higher manager on the

Question

excel formula to calculate time

I would like to present to higher manager on the time punctuality within the team

some time x employee will login late and logout early, so I would like to know:

1)on an average how many days the x has worked less than 9 hours
2)Average time he work for less than 9 hours

for example employee X
login 7:00AM
logout 3:00PM
actual login = 7:00AM
actual logout= 4:00PM
so this time he has worked only for 8 hours and the difference 1 hours and some time the difference will be 15minutes

Thanks for help !

InTime Time IN Outtime Time OUT Actual LogIn Actual LogOut 18/08/14 07:43 7:43 AM 18/08/14 15:56 3:56 PM 7:00 AM 4:00 PM 19/08/14 07:34 7:34 AM 19/08/14 16:05 4:05 PM 7:00 AM 4:00 PM 20/08/14 07:31 7:31 AM 20/08/14 15:52 3:52 PM 7:00 AM 4:00 PM 21/08/14 07:36 7:36 AM 21/08/14 15:52 3:52 PM 7:00 AM 4:00 PM 22/08/14 07:39 7:39 AM 22/08/14 15:45 3:45 PM 7:00 AM 4:00 PM 05/08/14 17:50 5:50 PM 06/08/14 02:24 2:24 AM 5:30 PM 2:30 AM 06/08/14 17:37 5:37 PM 07/08/14 02:04 2:04 AM 5:30 PM 2:30 AM 07/08/14 17:51 5:51 PM 08/08/14 02:18 2:18 AM 5:30 PM 2:30 AM 08/08/14 17:44 5:44 PM 09/08/14 02:14 2:14 AM 5:30 PM 2:30 AM 11/08/14 17:47 5:47 PM 12/08/14 02:23 2:23 AM 5:30 PM 2:30 AM 12/08/14 17:52 5:52 PM 13/08/14 00:21 12:21 AM 5:30 PM 2:30 AM 13/08/14 20:21 8:21 PM 14/08/14 02:02 2:02 AM 5:30 PM 2:30 AM 14/08/14 17:50 5:50 PM 15/08/14 02:16 2:16 AM 5:30 PM 2:30 AM 25/08/14 17:20 5:20 PM 26/08/14 02:07 2:07 AM 5:30 PM 2:30 AM 26/08/14 17:38 5:38 PM 27/08/14 02:21 2:21 AM 5:30 PM 2:30 AM 27/08/14 17:36 5:36 PM 28/08/14 02:27 2:27 AM 5:30 PM 2:30 AM 28/08/14 17:24 5:24 PM 29/08/14 02:05 2:05 AM 5:30 PM 2:30 AM 29/08/14 17:28 5:28 PM 30/08/14 02:13 2:13 AM 5:30 PM 2:30 AM

Explanation / Answer

Hi,

I'm not sure which columns we're looking at here but the formula below will count the number of times the hours worked; based upon columns B & D, is less than 8 hours...

=SUMPRODUCT(--(D2:D19-B2:B19+(D2:D19<B2:B19)<TIME(8,0,0)))

If you want the count row by row then put this in in (say) G2 and drag down. Format the formula cell as general.

=(D2-B2+(D2<B2))*24

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.