Intervals entry - output

[jumiprado]

Hey guys! How r u? I have a data like DATA excel attached, and i m looking for a solution like this table

ef_name	in	out	Dif hh:mm	Total hh:mm	Out time hh:mm	Total Out Time
Agent 1	29/01/2015 12:09	29/01/2015 16:13	04:04:00	06:51:00	-	00:19:00
Agent 1	29/01/2015 16:16	29/01/2015 16:21	00:05:00	06:51:00	00:03:00	00:19:00
Agent 1	29/01/2015 16:24	29/01/2015 16:56	00:32:00	06:51:00	00:03:00	00:19:00
Agent 1	29/01/2015 17:00	29/01/2015 17:15	00:15:00	06:51:00	00:04:00	00:19:00
Agent 1	29/01/2015 17:18	29/01/2015 17:50	00:32:00	06:51:00	00:03:00	00:19:00
Agent 1	29/01/2015 17:53	29/01/2015 18:06	00:13:00	06:51:00	00:03:00	00:19:00
Agent 1	29/01/2015 18:09	29/01/2015 19:19	01:10:00	06:51:00	00:03:00	00:19:00
Agent 2	06/03/2019 07:08	06/03/2019 08:31	01:23:00	04:39:00	Error	01:37:00
Agent 2	06/03/2019 08:37	06/03/2019 08:50	00:13:00	04:39:00	00:06:00	01:37:00
Agent 2	06/03/2019 08:50	06/03/2019 08:51	00:01:00	04:39:00	00:00:00	01:37:00
Agent 2	06/03/2019 08:52		Error	04:39:00	00:01:00	01:37:00
Agent 2	06/03/2019 09:02		Error	04:39:00	Error	01:37:00
Agent 2	06/03/2019 09:17		Error	04:39:00	Error	01:37:00
Agent 2	06/03/2019 09:32	06/03/2019 09:36	00:04:00	04:39:00	Error	01:37:00
Agent 2	06/03/2019 09:36		Error	04:39:00	00:00:00	01:37:00
Agent 2	06/03/2019 09:58	06/03/2019 09:59	00:01:00	04:39:00	Error	01:37:00
Agent 2	06/03/2019 10:01		Error	04:39:00	Error	01:37:00
Agent 2	06/03/2019 10:05		Error	04:39:00	Error	01:37:00
Agent 2	06/03/2019 10:17		Error	04:39:00	Error	01:37:00
Agent 2	06/03/2019 10:25		Error	04:39:00	Error	01:37:00
Agent 2	06/03/2019 10:46	06/03/2019 10:49	00:03:00	04:39:00	Error	01:37:00
Agent 2	06/03/2019 11:02	06/03/2019 11:11	00:09:00	04:39:00	00:13:00	01:37:00
Agent 2	06/03/2019 11:16	06/03/2019 11:16	00:00:00	04:39:00	00:05:00	01:37:00
Agent 2	06/03/2019 11:19	06/03/2019 11:19	00:00:00	04:39:00	00:03:00	01:37:00
Agent 2	06/03/2019 11:21		Error	04:39:00	00:02:00	01:37:00
Agent 2	06/03/2019 11:24	06/03/2019 11:53	00:29:00	04:39:00	Error	01:37:00
Agent 2	06/03/2019 11:58		Error	04:39:00	00:05:00	01:37:00
Agent 2	06/03/2019 12:00		Error	04:39:00	Error	01:37:00
Agent 2	06/03/2019 12:01		Error	04:39:00	Error	01:37:00
Agent 2	06/03/2019 12:05		Error	04:39:00	Error	01:37:00
Agent 2	06/03/2019 12:07	06/03/2019 12:47	00:40:00	04:39:00	Error	01:37:00
Agent 2	06/03/2019 12:52		Error	04:39:00	00:05:00	01:37:00
Agent 2	06/03/2019 13:07		Error	04:39:00	Error	01:37:00
Agent 2	06/03/2019 13:11	06/03/2019 13:38	00:27:00	04:39:00	Error	01:37:00
Agent 2	06/03/2019 13:51	06/03/2019 13:51	00:00:00	04:39:00	00:13:00	01:37:00
Agent 2	06/03/2019 14:01	06/03/2019 14:02	00:01:00	04:39:00	00:10:00	01:37:00
Agent 2	06/03/2019 14:02		Error	04:39:00	00:00:00	01:37:00
Agent 2	06/03/2019 14:10	06/03/2019 14:11	00:01:00	04:39:00	Error	01:37:00
Agent 2	06/03/2019 14:11		Error	04:39:00	00:00:00	01:37:00
Agent 2	06/03/2019 14:14		Error	04:39:00	Error	01:37:00
Agent 2	06/03/2019 14:16		Error	04:39:00	Error	01:37:00
Agent 2	06/03/2019 14:17		Error	04:39:00	Error	01:37:00
Agent 2	06/03/2019 14:18		Error	04:39:00	Error	01:37:00
Agent 2	06/03/2019 14:20		Error	04:39:00	Error	01:37:00
Agent 2	06/03/2019 14:23		Error	04:39:00	Error	01:37:00
Agent 2	06/03/2019 14:26		Error	04:39:00	Error	01:37:00
Agent 2	06/03/2019 14:27		Error	04:39:00	Error	01:37:00
Agent 2	06/03/2019 14:28		Error	04:39:00	Error	01:37:00
Agent 2	06/03/2019 14:29		Error	04:39:00	Error	01:37:00
Agent 2	06/03/2019 14:32		Error	04:39:00	Error	01:37:00
Agent 2	06/03/2019 14:33		Error	04:39:00	Error	01:37:00
Agent 2	06/03/2019 14:34		Error	04:39:00	Error	01:37:00
Agent 2	06/03/2019 14:35		Error	04:39:00	Error	01:37:00
Agent 2	06/03/2019 14:36	06/03/2019 14:37	00:01:00	04:39:00	Error	01:37:00
Agent 2	06/03/2019 14:38	06/03/2019 14:38	00:00:00	04:39:00	00:01:00	01:37:00
Agent 2	06/03/2019 14:39	06/03/2019 14:40	00:01:00	04:39:00	00:01:00	01:37:00
Agent 2	06/03/2019 14:40		Error	04:39:00	00:00:00	01:37:00
Agent 2	06/03/2019 14:42		Error	04:39:00	Error	01:37:00
Agent 2	06/03/2019 14:43		Error	04:39:00	Error	01:37:00
Agent 2	06/03/2019 14:44		Error	04:39:00	Error	01:37:00
Agent 2	06/03/2019 14:45		Error	04:39:00	Error	01:37:00
Agent 2	06/03/2019 14:46		Error	04:39:00	Error	01:37:00
Agent 2	06/03/2019 14:54	06/03/2019 15:00	00:06:00	04:39:00	Error	01:37:00
Agent 2	06/03/2019 15:01		Error	04:39:00	00:01:00	01:37:00
Agent 2	06/03/2019 15:02	06/03/2019 15:04	00:02:00	04:39:00	Error	01:37:00
Agent 2	06/03/2019 15:06		Error	04:39:00	00:02:00	01:37:00
Agent 2	06/03/2019 15:10		Error	04:39:00	Error	01:37:00
Agent 2	06/03/2019 15:11		Error	04:39:00	Error	01:37:00
Agent 2	06/03/2019 15:12		Error	04:39:00	Error	01:37:00
Agent 2	06/03/2019 15:13		Error	04:39:00	Error	01:37:00
Agent 2	06/03/2019 15:14	06/03/2019 15:31	00:17:00	04:39:00	Error	01:37:00
Agent 2	06/03/2019 15:34		Error	04:39:00	00:03:00	01:37:00
Agent 2	06/03/2019 15:37		Error	04:39:00	Error	01:37:00
Agent 2	06/03/2019 15:38	06/03/2019 15:39	00:01:00	04:39:00	Error	01:37:00
Agent 2	06/03/2019 15:41	06/03/2019 15:41	00:00:00	04:39:00	00:02:00	01:37:00
Agent 2	06/03/2019 15:46		Error	04:39:00	00:05:00	01:37:00
Agent 2	06/03/2019 15:48	06/03/2019 16:27	00:39:00	04:39:00	Error	01:37:00

Is It possible?

Thanks!

[tincholiver]

Yes, it is possible but can you tell us how the last two columns are calculated?

[jumiprado]

of course, thanks for answer!

the field Out Time is the difference between the previous out and the next "in" for each Agent and for each day. in the example the first row dont have it because the day starts at the first row, but the second row yes.

For the Total Out time is the sum for each agent each day, so i need to calculate how much time is each agent out and sum that per day!

ef_name	in	out	Dif hh:mm	Total hh:mm	Out time hh:mm	Total Out Time
Agent 1	29/01/2015 12:09	29/01/2015 16:13	04:04:00	06:51:00	-	00:19:00
Agent 1	29/01/2015 16:16	29/01/2015 16:21	00:05:00	06:51:00	00:03:00	00:19:00

i hope i was clear!

Thanks again!

[anushree1]

Please check the attached

[tincholiver]

Thank you very much Anushree,
I hope it is the solution that Jumiprado is looking for.