Time Attendance in excel. Need to calculate absentees automatically from only present days data

Question

I have one Time Attendance excel sheet which only show no. of attended days of our employees.TA System has no capability to schedule and mark absentees automatically. So I need to calculate no. of absentees of all employee. Is there any solution to this in excel with formula ? or can you guide what i can do to get absentees from Present days data with minimal work? See below the format of attendance data. It is the sample of 6 employees with six. Day staff working for 5 days a week while shift employees are on on different days with different shifts.

+-------+----------+----------+------------------+------------------+---------------+--------------+---------------+-------------------+------------+
| Name  | Badge ID | SYSTEMID | Check In         | Check Out        | Time Duration | Access       | Shift Type    | Shift Description | Date       |
+-------+----------+----------+------------------+------------------+---------------+--------------+---------------+-------------------+------------+
| EMP A | 0-1005   | 10000095 | 06/10/2018 9:32  | 06/10/2018 16:28 | 6:56:11       | Valid access | General Shift | Day Staff         | 06/10/2018 |
+-------+----------+----------+------------------+------------------+---------------+--------------+---------------+-------------------+------------+
| EMP A | 0-1005   | 10000095 | 04/10/2018 8:39  | 04/10/2018 15:49 | 7:10:16       | Valid access | General Shift | Day Staff         | 04/10/2018 |
+-------+----------+----------+------------------+------------------+---------------+--------------+---------------+-------------------+------------+
| EMP A | 0-1005   | 10000095 | 03/10/2018 8:01  | 03/10/2018 18:30 | 10:28:10      | Valid access | General Shift | Day Staff         | 03/10/2018 |
+-------+----------+----------+------------------+------------------+---------------+--------------+---------------+-------------------+------------+
| EMP A | 0-1005   | 10000095 | 02/10/2018 7:03  | 02/10/2018 17:41 | 10:38:26      | Valid access | General Shift | Day Staff         | 02/10/2018 |
+-------+----------+----------+------------------+------------------+---------------+--------------+---------------+-------------------+------------+
| EMP A | 0-1005   | 10000095 | 01/10/2018 7:49  | 01/10/2018 17:58 | 10:09:01      | Valid access | General Shift | Day Staff         | 01/10/2018 |
+-------+----------+----------+------------------+------------------+---------------+--------------+---------------+-------------------+------------+
| EMP B | 0-1020   | 10000040 | 04/10/2018 9:03  | 04/10/2018 17:20 | 8:17:44       | Valid access | General Shift | Day Staff         | 04/10/2018 |
+-------+----------+----------+------------------+------------------+---------------+--------------+---------------+-------------------+------------+
| EMP B | 0-1020   | 10000040 | 03/10/2018 17:51 | 03/10/2018 17:51 | 0:00:00       | Valid access | General Shift | Day Staff         | 03/10/2018 |
+-------+----------+----------+------------------+------------------+---------------+--------------+---------------+-------------------+------------+
| EMP B | 0-1020   | 10000040 | 02/10/2018 9:47  | 02/10/2018 17:54 | 8:07:29       | Valid access | General Shift | Day Staff         | 02/10/2018 |
+-------+----------+----------+------------------+------------------+---------------+--------------+---------------+-------------------+------------+
| EMP B | 0-1020   | 10000040 | 01/10/2018 8:38  | 01/10/2018 17:00 | 8:21:03       | Valid access | General Shift | Day Staff         | 01/10/2018 |
+-------+----------+----------+------------------+------------------+---------------+--------------+---------------+-------------------+------------+
| EMP C | 0-1039   | 10000031 | 04/10/2018 7:33  | 04/10/2018 16:03 | 8:29:24       | Valid access | Shift A       | Shift Employee    | 04/10/2018 |
+-------+----------+----------+------------------+------------------+---------------+--------------+---------------+-------------------+------------+
| EMP C | 0-1039   | 10000031 | 03/10/2018 7:33  | 03/10/2018 17:39 | 10:06:02      | Valid access | Shift A       | Shift Employee    | 03/10/2018 |
+-------+----------+----------+------------------+------------------+---------------+--------------+---------------+-------------------+------------+
| EMP C | 0-1039   | 10000031 | 02/10/2018 7:28  | 02/10/2018 17:14 | 9:45:52       | Valid access | Shift A       | Shift Employee    | 02/10/2018 |
+-------+----------+----------+------------------+------------------+---------------+--------------+---------------+-------------------+------------+
| EMP C | 0-1039   | 10000031 | 01/10/2018 7:35  | 01/10/2018 15:41 | 8:06:10       | Valid access | Shift A       | Shift Employee    | 01/10/2018 |
+-------+----------+----------+------------------+------------------+---------------+--------------+---------------+-------------------+------------+
| EMP D | 0-1063   | 10000120 | 03/10/2018 6:23  | 03/10/2018 18:14 | 11:50:53      | Valid access | Shift A       | Shift Employee    | 03/10/2018 |
+-------+----------+----------+------------------+------------------+---------------+--------------+---------------+-------------------+------------+
| EMP D | 0-1063   | 10000120 | 02/10/2018 6:24  | 02/10/2018 17:47 | 11:22:49      | Valid access | Shift A       | Shift Employee    | 02/10/2018 |
+-------+----------+----------+------------------+------------------+---------------+--------------+---------------+-------------------+------------+
| EMP D | 0-1063   | 10000120 | 01/10/2018 6:57  | 01/10/2018 17:48 | 10:50:45      | Valid access | Shift A       | Shift Employee    | 01/10/2018 |
+-------+----------+----------+------------------+------------------+---------------+--------------+---------------+-------------------+------------+
| EMP E | 0-1038   | 10000047 | 03/10/2018 18:05 | 04/10/2018 5:52  | 11:47:02      | Valid access | Shift A       | Shift Employee    | 03/10/2018 |
+-------+----------+----------+------------------+------------------+---------------+--------------+---------------+-------------------+------------+
| EMP E | 0-1038   | 10000047 | 02/10/2018 18:05 | 03/10/2018 6:15  | 12:09:59      | Valid access | Shift A       | Shift Employee    | 02/10/2018 |
+-------+----------+----------+------------------+------------------+---------------+--------------+---------------+-------------------+------------+
| EMP E | 0-1038   | 10000047 | 01/10/2018 18:07 | 02/10/2018 5:47  | 11:39:55      | Valid access | Shift A       | Shift Employee    | 01/10/2018 |
+-------+----------+----------+------------------+------------------+---------------+--------------+---------------+-------------------+------------+
| EMP F | 0-1078   | 10000097 | 04/10/2018 6:12  | 04/10/2018 17:49 | 11:37:04      | Valid access | Shift A       | Shift Employee    | 04/10/2018 |
+-------+----------+----------+------------------+------------------+---------------+--------------+---------------+-------------------+------------+
| EMP F | 0-1078   | 10000097 | 03/10/2018 6:17  | 03/10/2018 18:13 | 11:56:52      | Valid access | Shift A       | Shift Employee    | 03/10/2018 |
+-------+----------+----------+------------------+------------------+---------------+--------------+---------------+-------------------+------------+
| EMP F | 0-1078   | 10000097 | 02/10/2018 6:18  | 02/10/2018 17:47 | 11:28:54      | Valid access | Shift A       | Shift Employee    | 02/10/2018 |
+-------+----------+----------+------------------+------------------+---------------+--------------+---------------+-------------------+------------+
| EMP F | 0-1078   | 10000097 | 01/10/2018 7:54  | 01/10/2018 18:18 | 10:23:27      | Valid access | Shift A       | Shift Employee    | 01/10/2018 |
+-------+----------+----------+------------------+------------------+---------------+--------------+---------------+-------------------+------------+

I guess you could make a sheet with the days as the rows and the employees as the columns, then in each cell use `COUNTIF` to see if there is a check in date for that employee for that day — Dan, Oct 27 '18 at 08:39

score 0 · Answer 1 · answered Oct 27 '18 at 10:20

0

Dan has already commented the solution. I'm just illustrating the same here with a sample picture.

Please use Date of a particular month or the desired period for which you want the find present / absent data plotted in rows and Employee Names or BadgeIDs (for uniqueness) across columns - Here I have used Employee Names as it is unique.

Use the MATCH formula against the 'Date' column (last column in your data) which represent Check-in date and against the Employee Name column. If the returned value is a number then the employee was 'Present' else 'Absent'.

Excel snip:

Hope this helps!

answered Oct 27 '18 at 10:20

Karthick Ganesan

375
4
11

Thanks Dan and Karthick for the illustration, and i can exclude off days as weekands such sa Saturday and Sunday. It is really helping but for Day staff. but Shift employees have different off-days every week depend on their shift schedule.Is there any way to exclude those days for complete month ? No. of employees are almost 100 in 4 different shifts. – fahadrana Oct 28 '18 at 07:29
Can you send me file with the formula. I am having some difficulty to understand the formula. – fahadrana Oct 28 '18 at 09:19

Time Attendance in excel. Need to calculate absentees automatically from only present days data

1 Answers1