To find the break consumed

[Sathisc]

Hi,

I have a swipe report in the below mentioned format. I need to find out the break hours consumed by every user for particular day.

in the data column D has IN/OUT data contains when the time is captured either IN(coming inside) OUT(Leaving office)
need to find out the break timings for the entire day. I have given 3 users timings where the data is captured for entire week.

Request you help in capturing the break timings for individual users for each day for the entire week.

Thanks,
Sathish

Date	Time	EmpName	InOut
26/02/2018	13:10:32	Satz	In
26/02/2018	14:26:49	Satz	Out
26/02/2018	14:57:22	Satz	In
26/02/2018	16:58:37	Satz	Out
26/02/2018	17:55:50	Satz	In
26/02/2018	18:47:16	Satz	Out
26/02/2018	18:48:18	Satz	In
26/02/2018	19:29:15	Satz	Out
26/02/2018	19:46:11	Satz	In
26/02/2018	21:08:30	Satz	Out
27/02/2018	11:57:37	Satz	In
27/02/2018	13:24:17	Satz	Out
27/02/2018	13:59:26	Satz	In
27/02/2018	16:12:08	Satz	Out
27/02/2018	16:32:13	Satz	In
27/02/2018	19:14:50	Satz	Out
27/02/2018	20:04:20	Satz	In
27/02/2018	21:40:21	Satz	Out
28/02/2018	12:19:12	Satz	In
28/02/2018	13:26:12	Satz	Out
28/02/2018	13:55:35	Satz	In
28/02/2018	15:54:55	Satz	Out
28/02/2018	15:55:23	Satz	In
28/02/2018	15:57:15	Satz	Out
28/02/2018	16:12:32	Satz	In
28/02/2018	18:47:13	Satz	Out
28/02/2018	19:27:19	Satz	In
28/02/2018	19:44:43	Satz	Out
28/02/2018	19:47:35	Satz	In
28/02/2018	19:47:40	Satz	Out
28/02/2018	20:10:32	Satz	In
28/02/2018	20:31:54	Satz	Out
28/02/2018	21:05:01	Satz	In
28/02/2018	21:35:43	Satz	Out
26/02/2018	11:45:20	Vinod	In
26/02/2018	11:48:53	Vinod	Out
26/02/2018	11:53:47	Vinod	In
26/02/2018	17:50:22	Vinod	Out
26/02/2018	18:24:57	Vinod	In
26/02/2018	21:08:17	Vinod	Out
27/02/2018	11:38:13	Vinod	In
27/02/2018	18:36:21	Vinod	Out
27/02/2018	19:18:42	Vinod	In
27/02/2018	21:09:53	Vinod	Out
28/02/2018	11:40:45	Vinod	In
28/02/2018	13:46:41	Vinod	Out
28/02/2018	13:47:46	Vinod	In
28/02/2018	17:12:13	Vinod	Out
28/02/2018	17:13:16	Vinod	In
28/02/2018	18:51:37	Vinod	Out
28/02/2018	19:30:26	Vinod	In
28/02/2018	21:09:26	Vinod	Out
01/03/2018	11:32:21	Vinod	In
01/03/2018	19:04:39	Vinod	Out
01/03/2018	19:37:50	Vinod	In
01/03/2018	21:09:23	Vinod	Out
02/03/2018	11:36:50	Vinod	In
02/03/2018	14:01:35	Vinod	Out
02/03/2018	14:43:55	Vinod	In
02/03/2018	17:59:54	Vinod	Out
02/03/2018	18:00:30	Vinod	In
02/03/2018	21:03:47	Vinod	Out
26/02/2018	11:45:51	Dhana	In
26/02/2018	14:26:23	Dhana	Out
26/02/2018	14:27:53	Dhana	In
26/02/2018	17:37:35	Dhana	In
26/02/2018	18:53:10	Dhana	Out
26/02/2018	19:16:06	Dhana	In
26/02/2018	20:39:14	Dhana	Out
26/02/2018	20:42:19	Dhana	In
26/02/2018	21:15:46	Dhana	Out
27/02/2018	11:44:39	Dhana	In
27/02/2018	16:26:52	Dhana	Out
27/02/2018	17:24:28	Dhana	In
27/02/2018	18:17:37	Dhana	Out
27/02/2018	18:19:58	Dhana	In
27/02/2018	19:34:12	Dhana	Out
27/02/2018	19:38:28	Dhana	In
27/02/2018	21:10:12	Dhana	Out
28/02/2018	11:43:39	Dhana	In
28/02/2018	14:33:23	Dhana	Out
28/02/2018	14:45:29	Dhana	In
28/02/2018	18:14:02	Dhana	Out
28/02/2018	18:17:34	Dhana	In
28/02/2018	21:09:57	Dhana	Out
01/03/2018	11:44:35	Dhana	In
01/03/2018	18:47:36	Dhana	Out
01/03/2018	19:28:46	Dhana	In
01/03/2018	20:06:07	Dhana	Out
02/03/2018	11:51:21	Dhana	In
02/03/2018	13:17:41	Dhana	Out
02/03/2018	13:23:27	Dhana	In
02/03/2018	16:10:20	Dhana	Out
02/03/2018	17:06:58	Dhana	In
02/03/2018	21:03:40	Dhana	Out

<tbody>
</tbody><colgroup><col><col><col><col></colgroup>

 

[Peter_SSs]

So, for that sample data, can you also post what the results would look like?

 

[Sathisc]

Thanks for your response, I have taken example of Satz ouput

Calculation steps

Date	Time	EmpName	InOut	Break Hours
26/02/2018	13:10:32	Satz	In	Coming Inside office
26/02/2018	14:26:49	Satz	Out	0:30
26/02/2018	14:57:22	Satz	In
26/02/2018	16:58:37	Satz	Out	0:57
26/02/2018	17:55:50	Satz	In
26/02/2018	18:47:16	Satz	Out	0:01
26/02/2018	18:48:18	Satz	In
26/02/2018	19:29:15	Satz	Out	0:16
26/02/2018	19:46:11	Satz	In
26/02/2018	21:08:30	Satz	Out	Leaving Office
27/02/2018	11:57:37	Satz	In	Coming Inside office
27/02/2018	13:24:17	Satz	Out	0:35
27/02/2018	13:59:26	Satz	In
27/02/2018	16:12:08	Satz	Out	0:20
27/02/2018	16:32:13	Satz	In
27/02/2018	19:14:50	Satz	Out	0:49
27/02/2018	20:04:20	Satz	In
27/02/2018	21:40:21	Satz	Out	Leaving Office
28/02/2018	12:19:12	Satz	In	Coming Inside office
28/02/2018	13:26:12	Satz	Out	0:29
28/02/2018	13:55:35	Satz	In
28/02/2018	15:54:55	Satz	Out	0:00
28/02/2018	15:55:23	Satz	In
28/02/2018	15:57:15	Satz	Out	0:15
28/02/2018	16:12:32	Satz	In
28/02/2018	18:47:13	Satz	Out	0:40
28/02/2018	19:27:19	Satz	In
28/02/2018	19:44:43	Satz	Out	0:02
28/02/2018	19:47:35	Satz	In
28/02/2018	19:47:40	Satz	Out	0:22
28/02/2018	20:10:32	Satz	In
28/02/2018	20:31:54	Satz	Out	0:33
28/02/2018	21:05:01	Satz	In
28/02/2018	21:35:43	Satz	Out	Leaving Office

<tbody>
</tbody><colgroup><col><col><col><col><col></colgroup>

Required Output

Date	Emp Name	Break Hours for the day (hh:mm:ss)
26/02/2018	Satz	1:45:44
27/02/2018	Satz	1:44:44
28/02/2018	Satz	2:24:05

<tbody>
</tbody><colgroup><col><col><col></colgroup>

Thanks
Sathish

 

[Sathisc]

So, for that sample data, can you also post what the results would look like?

Hi Peter I have responded with the example

 

[Peter_SSs]

Hi Peter I have responded with the example

Yes, I can see post #3 as easily as post #4 . Be patient.

Try this, copied down

Excel Workbook
	A	B	C	D
1	Date	Time	EmpName	InOut
2	26/02/2018	13:10:32	Satz	In
3	26/02/2018	14:26:49	Satz	Out
4	26/02/2018	14:57:22	Satz	In
5	26/02/2018	16:58:37	Satz	Out
6	26/02/2018	17:55:50	Satz	In
7	26/02/2018	18:47:16	Satz	Out
8	26/02/2018	18:48:18	Satz	In
9	26/02/2018	19:29:15	Satz	Out
10	26/02/2018	19:46:11	Satz	In
11	26/02/2018	21:08:30	Satz	Out
12	27/02/2018	11:57:37	Satz	In
13	27/02/2018	13:24:17	Satz	Out
14	27/02/2018	13:59:26	Satz	In
15	27/02/2018	16:12:08	Satz	Out
16	27/02/2018	16:32:13	Satz	In
17	27/02/2018	19:14:50	Satz	Out
18	27/02/2018	20:04:20	Satz	In
19	27/02/2018	21:40:21	Satz	Out
20	28/02/2018	12:19:12	Satz	In
34	28/02/2018	21:05:01	Satz	In
35	28/02/2018	21:35:43	Satz	Out
36
37
38	Date	Emp Name	Break Hours
39	26/02/2018	Satz	1:45:44
40	27/02/2018	Satz	1:44:44
41	28/02/2018	Satz	2:24:05
Break

 

[Sathisc]

Thank you so much Peter, it works superbly.

Need one more help(getting a feel that I am asking too much)
some people work in shifts like the below example where the work timings is 18:00 hrs to 06:00 hrs

Date	Time	EmpName	InOut
26-02-18	18:05:38	Prave	In
26-02-18	20:23:07	Prave	Out
26-02-18	21:00:38	Prave	In
27-02-18	06:30:41	Prave	Out
27-02-18	18:21:05	Prave	In
27-02-18	20:22:08	Prave	Out
27-02-18	20:54:40	Prave	In
27-02-18	23:13:08	Prave	Out
28-02-18	00:08:21	Prave	In
28-02-18	05:12:22	Prave	Out
28-02-18	05:12:45	Prave	In
28-02-18	05:14:32	Prave	Out
28-02-18	18:10:38	Prave	In
28-02-18	22:09:58	Prave	Out
28-02-18	22:38:13	Prave	In
01-03-18	07:08:05	Prave	Out
01-03-18	18:17:40	Prave	In
01-03-18	22:23:38	Prave	Out
01-03-18	22:57:25	Prave	In
02-03-18	03:50:37	Prave	Out
02-03-18	18:19:42	Prave	In
02-03-18	19:50:29	Prave	Out
02-03-18	20:24:21	Prave	In
02-03-18	22:50:36	Prave	Out
02-03-18	23:40:26	Prave	In
03-03-18	04:55:29	Prave	Out

<tbody>
</tbody><colgroup><col span="2"><col><col></colgroup>

1st day working hrs

Date	Time	EmpName	InOut
26-02-18	18:05:38	Prave	In
26-02-18	20:23:07	Prave	Out
26-02-18	21:00:38	Prave	In
27-02-18	06:30:41	Prave	Out

<tbody>
</tbody><colgroup><col span="2"><col><col></colgroup>

Can u help me with this one. Thank you very much in advance

Sathish

 

[Peter_SSs]

Can u help me with this one.

I'm not sure that I can.

 

[Sathisc]

Thanks Peter...