To find the break consumed

Sathisc · Mar 8, 2018

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

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

[/TD]
[/TR]
</tbody><colgroup><col><col><col><col></colgroup>[/TABLE]

Peter_SSs · Mar 8, 2018

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

Sathisc · Mar 8, 2018

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

Calculation steps

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

Required Output
[TABLE="width: 318"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Emp Name[/TD]
[TD]Break Hours for the day (hh:mm:ss)[/TD]
[/TR]
[TR]
[TD]26/02/2018[/TD]
[TD]Satz[/TD]
[TD="align: right"]1:45:44[/TD]
[/TR]
[TR]
[TD]27/02/2018[/TD]
[TD]Satz[/TD]
[TD="align: right"]1:44:44[/TD]
[/TR]
[TR]
[TD]28/02/2018[/TD]
[TD]Satz[/TD]
[TD="align: right"]2:24:05[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]

Thanks
Sathish

Sathisc · Mar 8, 2018

Peter_SSs said:
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 · Mar 8, 2018

Sathisc said:
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