00:00This is a pretty controversial Excel dataset table, the employee timekeeping table.
00:05Let me show you how you can improve your employee timekeeping table so that you can identify
00:10late comers and early departures.
00:13Let's start off with late comers.
00:15You're going to start off with filter function.
00:17The first argument on the filter function will be the employee name, and that's what
00:21you're going to return, comma.
00:23The second argument on the filter function is essentially your condition.
00:26We're going to say clock in time greater than the official start time at cell C4, which
00:34is 8 a.m., and close parenthesis, and then enter.
00:37And these are the list of names of people who came to work after 8 a.m.
00:43Likewise, for early departure, you're going to use a filter function, open parenthesis, and
00:48the first argument on filter function would be the employee name, comma.
00:53The second argument, like the late comers, is actually a condition.
00:57We're going to, this time around, use clock out time, lesser than official end time, close
01:05parenthesis, and hit enter.
01:07And this is a list of people who left work before 5 p.m.
01:11Like any other company, they do have allowance.
01:14They don't expect people to arrive exactly at 8 or live exactly at 5.
01:19And I've created another cell to hold 30-minute allowance, which means you're okay to come
01:25later by 30 minutes.
01:27So in order to incorporate these 30 minutes, you're going to have to modify your filter
01:32function like this.
01:33On the second argument of filter function, on that C4, where the official start time is
01:38there, you're going to basically add 30 minutes.
01:41But 30 minutes, you have to convert them into official time using a time function.
01:46And the first argument on time function is your hour, which is going to be zero.
01:50Second argument is the allowance time, which is C6 of 30 minutes, comma.
01:55And the third argument is seconds.
01:58And let's close it with two parenthesis for the time and hit enter.
02:02And these are the people who will show up for work after 8.30.
02:05Likewise, for the early departure, you're going to have to basically minus time, and the first
02:13argument is zero, comma, and the second argument is minute, which is this one here, comma, zero,
02:19close parenthesis twice.
02:21And these are people who left before 4.30 of the official end time.
02:27Thanks for listening.
Comments