Hi, I have a table exported from our system which logs Date, Time, User, Event.
I need to calculate working hours for each user on daily basis.
The event CONNECTION refers to when user is connected to system. DISCONNECTION refers to when user logged-off from system.
Total working hour per user, per day is: Last DISCONNECTION - First CONNECTION - (difference between any CONNECTION - DISCONNECTION)
Sometimes, the system gives error where for some user there is a DISCONNECTION event first instead of CONNECTION. This should be ignored. On any particular day, for any user, the first event is Connection.
Sometimes, user does not disconnect and leaves the machine. In that case the working hour calculation should be 8 hours - first CONNECTION - (difference between any Connection - Disconnection if any)
I need to calculate working hours for each user on daily basis.
The event CONNECTION refers to when user is connected to system. DISCONNECTION refers to when user logged-off from system.
Total working hour per user, per day is: Last DISCONNECTION - First CONNECTION - (difference between any CONNECTION - DISCONNECTION)
Sometimes, the system gives error where for some user there is a DISCONNECTION event first instead of CONNECTION. This should be ignored. On any particular day, for any user, the first event is Connection.
Sometimes, user does not disconnect and leaves the machine. In that case the working hour calculation should be 8 hours - first CONNECTION - (difference between any Connection - Disconnection if any)
UserEvents.xlsx | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | Date | Time | User | Event | Remarks | ||
2 | 1/1/2021 | 6:35:53 | UserB | CONNECTS | |||
3 | 1/1/2021 | 7:45:20 | UserC | CONNECTS | |||
4 | 1/1/2021 | 8:15:39 | UserB | DISCONNECTS | |||
5 | 1/1/2021 | 8:25:46 | UserA | CONNECTS | |||
6 | 1/1/2021 | 9:20:25 | UserD | CONNECTS | |||
7 | 1/1/2021 | 10:14:38 | UserB | CONNECTS | |||
8 | 1/1/2021 | 11:24:17 | UserC | DISCONNECTS | |||
9 | 1/1/2021 | 12:13:42 | UserB | DISCONNECTS | |||
10 | 1/1/2021 | 12:14:43 | UserA | DISCONNECTS | |||
11 | 1/1/2021 | 13:57:32 | UserA | CONNECTS | |||
12 | 1/1/2021 | 14:19:27 | UserB | CONNECTS | |||
13 | 1/1/2021 | 14:23:07 | UserC | CONNECTS | UserC does not disconnect. Working hour is calculated at 8 hr from first connection | ||
14 | 1/1/2021 | 15:24:19 | UserD | DISCONNECTS | |||
15 | 1/1/2021 | 17:28:54 | UserB | DISCONNECTS | |||
16 | 1/1/2021 | 18:32:02 | UserA | DISCONNECTS | |||
17 | 2/1/2021 | 6:32:45 | UserA | DISCONNECTS | Ignore this as this is error. For each user, every day, first event should be CONNECTION | ||
18 | 2/1/2021 | 6:45:52 | UserD | CONNECTS | |||
19 | 2/1/2021 | 7:50:13 | UserC | CONNECTS | |||
20 | 2/1/2021 | 8:30:44 | UserA | CONNECTS | |||
21 | 2/1/2021 | 9:20:35 | UserD | DISCONNECTS | |||
22 | 2/1/2021 | 10:14:19 | UserD | CONNECTS | |||
23 | 2/1/2021 | 11:48:37 | UserA | DISCONNECTS | |||
24 | 2/1/2021 | 15:41:47 | UserC | DISCONNECTS | |||
25 | 2/1/2021 | 17:34:08 | UserD | DISCONNECTS | |||
26 | |||||||
27 | |||||||
28 | |||||||
29 | User Working Hours | ||||||
30 | Date | User | Total Working Hours | ||||
31 | 1/1/2021 | UserA | 8:23:27 | ||||
32 | 1/1/2021 | UserB | 6:48:17 | ||||
33 | 1/1/2021 | UserC | 4:46:30 | ||||
34 | 1/1/2021 | UserD | 6:03:54 | ||||
35 | 2/1/2021 | UserA | 3:17:53 | ||||
36 | 2/1/2021 | UserB | User did not worked | ||||
37 | 2/1/2021 | UserC | 7:51:34 | ||||
38 | 2/1/2021 | UserD | 9:54:32 | ||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C31 | C31 | =B16-B5-(B11-B10) |
C32 | C32 | =B15-B2-(B7-B4)-(B12-B9) |
C33 | C33 | =(B3+8)-(B13-B8) |
C34 | C34 | =B14-B6 |
C35 | C35 | =B23-B20 |
C37 | C37 | =B24-B19 |
C38 | C38 | =B25-B18-(B22-B21) |