2 Replies Latest reply on Jun 30, 2018 12:22 PM by Shinichiro Murakami

# How to calculate the time from a string

Hello 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.

Sudheer

• ###### 1. Re: How to calculate the time from a string

Hi,

before calculations I will tidy data by custom splitting columns and changing data type date&time.

Anna

• ###### 2. Re: How to calculate the time from a string

Based on your providing data, it's too troublesome to handle multiple days. (23:42 in)

exclude that, still VERY troublesome but here is something.

(Split only help up to ten and yours are already exceeds 10)

and so on

and so on.

and so on.

Thanks,

Shin