Time Sheet Calculation

[FinPro]

I will be grateful If someone can help me with this task:

I have the data from our "Attendance Machine". I need to calculate the "Total Time" considering that solution should be able to sort the data by employee and date. It should also be able to recognize if there are two time stamps are available. If only one is available, 0 hrs should be calculated. If there are more than 2 time stamps for the same date than first and last time stamps should be considered. There will be cases where "IN" stamp will be in before the midnight and "OUT" stamp will be in next calendar date. If all this can be done in a separate formatted sheet, will be excellent.

It appears to me that this quite a big task and I will be very thankful for the solutions. Sample data is as follows:

No.	User ID	Department	Date	Time	Event
1636	1000	General	3/3/2019	8:33:27	IN
1614	1000	General	3/3/2019	17:04:01	OUT
1557	1000	General	4/3/2019	8:26:51	IN
1475	1000	General	5/3/2019	8:24:26	IN
1453	1000	General	5/3/2019	16:56:15	OUT
1300	1000	General	7/3/2019	8:29:59	IN
1279	1000	General	7/3/2019	16:56:40	OUT
1133	1000	General	10/3/2019	8:29:50	IN
1111	1000	General	10/3/2019	16:57:38	OUT
1040	1000	General	11/3/2019	8:36:10	IN
996	1000	General	11/3/2019	18:29:13	OUT
953	1000	General	12/3/2019	8:35:18	IN
932	1000	General	12/3/2019	17:00:36	OUT
861	1000	General	13/3/2019	8:53:35	IN
839	1000	General	13/3/2019	17:21:51	OUT
776	1000	General	14/3/2019	8:34:38	IN
751	1000	General	14/3/2019	16:47:39	OUT
631	1000	General	17/3/2019	13:36:28	IN
613	1000	General	17/3/2019	16:46:22	OUT
544	1000	General	18/3/2019	8:12:44	IN
518	1000	General	18/3/2019	16:56:47	OUT
449	1000	General	19/3/2019	8:23:25	IN
434	1000	General	19/3/2019	14:38:50	OUT
357	1000	General	20/3/2019	8:57:19	IN
341	1000	General	20/3/2019	16:29:25	OUT
273	1000	General	21/3/2019	8:05:13	IN
89	1000	General	24/3/2019	8:31:56	IN
72	1000	General	24/3/2019	16:53:30	OUT
9	1000	General	25/3/2019	8:14:53	IN
1664	1012	General	2/3/2019	22:14:43	IN
1689	1012	General	2/3/2019	13:47:03	IN
1663	1012	General	2/3/2019	22:14:52	OUT
1627	1012	General	3/3/2019	13:54:03	IN
1590	1012	General	3/3/2019	21:56:07	OUT
1507	1012	General	4/3/2019	21:55:23	IN
1545	1012	General	4/3/2019	13:55:54	IN
1463	1012	General	5/3/2019	13:55:12	IN
1422	1012	General	5/3/2019	22:02:22	OUT
1369	1012	General	6/3/2019	15:01:54	IN
1335	1012	General	6/3/2019	21:59:16	OUT
1289	1012	General	7/3/2019	13:58:54	IN
1249	1012	General	7/3/2019	22:03:21	OUT
1199	1012	General	9/3/2019	13:26:24	IN
1174	1012	General	9/3/2019	22:01:35	OUT
1122	1012	General	10/3/2019	13:45:37	IN
1077	1012	General	10/3/2019	22:00:02	OUT
1032	1012	General	11/3/2019	13:09:33	IN
989	1012	General	11/3/2019	22:02:30	OUT
944	1012	General	12/3/2019	13:33:48	IN
897	1012	General	12/3/2019	22:06:29	OUT
857	1012	General	13/3/2019	13:07:09	IN
814	1012	General	13/3/2019	22:00:25	OUT
768	1012	General	14/3/2019	13:43:56	IN
720	1012	General	14/3/2019	22:12:05	OUT
703	1012	General	16/3/2019	6:10:47	IN
693	1012	General	16/3/2019	13:54:37	OUT
658	1012	General	17/3/2019	6:16:53	IN
529	1012	General	18/3/2019	14:03:03	OUT
475	1012	General	19/3/2019	5:57:42	IN
435	1012	General	19/3/2019	14:11:43	OUT
376	1012	General	20/3/2019	7:12:38	IN
350	1012	General	20/3/2019	13:57:58	OUT
282	1012	General	21/3/2019	7:52:40	IN
260	1012	General	21/3/2019	14:01:28	OUT
182	1012	General	23/3/2019	6:03:44	IN
162	1012	General	23/3/2019	14:02:15	OUT
104	1012	General	24/3/2019	7:53:13	IN
77	1012	General	24/3/2019	14:10:04	OUT
25	1012	General	25/3/2019	6:59:51	IN
1647	1015	General	3/3/2019	7:15:21	IN
1618	1015	General	3/3/2019	16:22:47	OUT
1567	1015	General	4/3/2019	7:27:51	IN
1537	1015	General	4/3/2019	16:28:08	OUT
1474	1015	General	5/3/2019	8:24:50	IN
1455	1015	General	5/3/2019	16:27:54	OUT
1399	1015	General	6/3/2019	7:15:35	IN
1311	1015	General	7/3/2019	7:21:03	IN
1280	1015	General	7/3/2019	16:29:26	OUT
1146	1015	General	10/3/2019	7:22:41	IN
1113	1015	General	10/3/2019	16:22:00	OUT
1052	1015	General	11/3/2019	7:21:15	IN
1021	1015	General	11/3/2019	16:29:42	OUT
866	1015	General	13/3/2019	8:08:38	IN
620	1015	General	17/3/2019	16:30:59	IN
642	1015	General	17/3/2019	8:14:20	IN
619	1015	General	17/3/2019	16:31:05	OUT
555	1015	General	18/3/2019	7:12:30	IN
522	1015	General	18/3/2019	16:26:39	OUT
461	1015	General	19/3/2019	7:09:22	IN
427	1015	General	19/3/2019	16:26:32	OUT
355	1015	General	20/3/2019	11:37:47	IN
342	1015	General	20/3/2019	16:24:49	OUT
283	1015	General	21/3/2019	7:17:02	IN
254	1015	General	21/3/2019	16:26:51	OUT
23	1015	General	25/3/2019	7:14:51	IN
1668	1023	General	2/3/2019	21:57:56	IN
1685	1023	General	2/3/2019	14:10:51	IN
1666	1023	General	2/3/2019	21:58:07	OUT
1619	1023	General	3/3/2019	16:09:02	IN

<tbody>
</tbody>

 

[FinPro]

Reviving the thread and still looking for support.

Thanks in advance.