Calculate working hours for each user

mr_alok

New Member
Joined
Mar 30, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDE
1DateTimeUserEventRemarks
21/1/20216:35:53UserBCONNECTS
31/1/20217:45:20UserCCONNECTS
41/1/20218:15:39UserBDISCONNECTS
51/1/20218:25:46UserACONNECTS
61/1/20219:20:25UserDCONNECTS
71/1/202110:14:38UserBCONNECTS
81/1/202111:24:17UserCDISCONNECTS
91/1/202112:13:42UserBDISCONNECTS
101/1/202112:14:43UserADISCONNECTS
111/1/202113:57:32UserACONNECTS
121/1/202114:19:27UserBCONNECTS
131/1/202114:23:07UserCCONNECTSUserC does not disconnect. Working hour is calculated at 8 hr from first connection
141/1/202115:24:19UserDDISCONNECTS
151/1/202117:28:54UserBDISCONNECTS
161/1/202118:32:02UserADISCONNECTS
172/1/20216:32:45UserADISCONNECTSIgnore this as this is error. For each user, every day, first event should be CONNECTION
182/1/20216:45:52UserDCONNECTS
192/1/20217:50:13UserCCONNECTS
202/1/20218:30:44UserACONNECTS
212/1/20219:20:35UserDDISCONNECTS
222/1/202110:14:19UserDCONNECTS
232/1/202111:48:37UserADISCONNECTS
242/1/202115:41:47UserCDISCONNECTS
252/1/202117:34:08UserDDISCONNECTS
26
27
28
29User Working Hours
30DateUserTotal Working Hours
311/1/2021UserA8:23:27
321/1/2021UserB6:48:17
331/1/2021UserC4:46:30
341/1/2021UserD6:03:54
352/1/2021UserA3:17:53
362/1/2021UserBUser did not worked
372/1/2021UserC7:51:34
382/1/2021UserD9:54:32
Sheet1
Cell Formulas
RangeFormula
C31C31=B16-B5-(B11-B10)
C32C32=B15-B2-(B7-B4)-(B12-B9)
C33C33=(B3+8)-(B13-B8)
C34C34=B14-B6
C35C35=B23-B20
C37C37=B24-B19
C38C38=B25-B18-(B22-B21)
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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
VBA Code:
=(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)
VBA Code:
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:
Excel Formula:
=worktime(A31,B31)
 
Upvote 0

Forum statistics

Threads
1,223,898
Messages
6,175,274
Members
452,628
Latest member
dd2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top