How to calculate the time from a string
sudheer.kumar.5 Jun 30, 2018 10:09 PMHello Community,
I have employee attendance/access records as below:
Punch Records 
02:44(in);04:04(out);04:04(in);04:04(out);06:23(in);06:36(out);08:43(in);08:59(out);10:02(in);10:42(out);13:01(in); 
07:59(in);09:05(out);09:14(in);11:31(out);12:03(in);14:21(out);14:28(in);17:41(out);17:50(in);18:08(out); 
23:42(in);02:57(out);03:50(in);05:13(out);06:32(in);09:46(out); 
10:03(in);10:03(out);13:01(in);13:05(out);15:14(in); 
08:29(in);09:00(out);09:12(in);11:42(out);13:03(in);13:23(out);13:25(in);15:08(out);15:13(in);15:13(out);15:13(in);18:16(out); 
08:49(in);09:51(out);10:00(in);10:00(out);10:53(in);11:09(out);12:19(in);12:54(out);15:02(in);15:04(out);15:04(in);15:12(out);17:08(in);17:19(out);18:14(in); 
09:57(in);11:17(out);12:10(in);14:36(out);14:54(in);18:18(out); 
08:50(in);12:19(out);13:01(in);16:04(out);16:27(in);18:01(out); please see the detailed explanation below. 
First in and last out will tell the total hours spent in office (the difference between last out and first in). Other values will give the break hours. Bear me if it is confusing.
Let me try to explain with a record 08:50(in);12:19(out);13:01(in);16:04(out);16:27(in);18:01(out);.
The employee was logged in at 08:50(in) and logged out at 18:01(out), total time spent in the office.
08:50(in);12:19(out);13:01(in);16:04(out);16:27(in);18:01(out);
The employee went to break at 12:19(out) and came me in at 13:01(in), first break.
08:50(in);12:19(out);13:01(in);16:04(out);16:27(in);18:01(out);
The employee went to break at 16:04(out) and came me in at 16:27(in), second break.
What I am trying to calculate from each punch record is the total number of hours spent in the office, the total number of breaks, time spent for each break. Please note that the number of breaks will warry from employee to employee and not fixed.
Please help me. Thanks in advance.
Sudheer

EmployeePunchRecords.twbx 20.5 KB