Calculate working hours for each user

[mr_alok]

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)

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)

 

[maabadi]

1. First Change Number format at Total Working Hours to Custom & at type section write: [h]:mm:ss to see correct time without AM & PM.
2. With your calculation, I think your formula at Cell C33 is Wrong. It should be

=(B8 - B3) + 8 / 24

Note: each Day at Excel calculate 1 & then * hour = 8/24
3. Add this UDF (User Define Function) To VBA and Save your file as Macro-Enabled WorkBook (.xlsm)

Function WorkTime(Dt As Long, User As String) As Double
Dim i As Long, C As Double, D As Double, Target As Range, T As Double
Dim Cr1 As Long, Cr2 As Long, FV As Long
Set Target = ActiveCell
'Lr2 = 56
Cr1 = 0
For i = 2 To 25
 If Range("A" & i).Value = Dt Then
  If Range("C" & i).Value = User Then
   If Range("D" & i).Value = "CONNECTS" Then
   C = C + Range("B" & i).Value
   If Cr1 = 0 Then FV = i
   Cr1 = i
   ElseIf Cr1 > 0 Then
   D = D + Range("B" & i).Value
   Cr2 = i
   End If
  End If
 End If
Next i
If Cr2 < Cr1 Then D = D + Range("B" & FV).Value + 1 / 3
WorkTime = D - C
End Function

4. At your Cell Write =WorkTime( Select Cell with Date, Select Cell with User). Example:

=worktime(A31,B31)