# Calculate an average entry time and exit time per day for each employee

#### liorchn

##### New Member
Hi,
Excel 2013.
I have a table containing employee attendance data every day (entry and exit). Each employee have a unique ID number, and also there may be multiple entrances and exits on any given day.
Ask for help in creating a table will be concentrated Average attendance of those employees ie, an average time of first entry, the mean final departure time and average working hours per day (net of course).
DATABASE:

 EMP.NUMBER EMP.NAME DATE ENTRY LEAVE WORKING HOURS 1111 JOHN 01/01/2016 07:00:00 17:00:00 10:00:00 1111 JOHN 02/01/2016 07:10:00 08:30:00 01:20:00 1111 JOHN 02/01/2016 09:30:00 16:30:00 07:00:00 1111 JOHN 02/01/2016 17:30:00 20:00:00 02:30:00 1111 JOHN 03/01/2016 10:00:00 18:00:00 08:00:00 2222 DANY 01/01/2016 11:00:00 14:30:00 03:30:00 2222 DANY 01/01/2016 16:00:00 18:00:00 02:00:00 2222 DANY 02/01/2016 08:30:00 18:00:00 09:30:00 2222 DANY 03/01/2016 07:00:00 12:00:00 05:00:00 2222 DANY 03/01/2016 14:30:00 20:30:00 06:00:00 33 PETER 01/01/2016 08:00:00 17:00:00 09:00:00 33 PETER 02/01/2016 08:30:00 16:30:00 08:00:00 33 PETER 03/01/2016 09:00:00 18:00:00 09:00:00 33 PETER 18/01/2016 07:00:00 11:00:00 04:00:00 33 PETER 18/01/2016 14:00:00 19:00:00 05:00:00

<colgroup><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>

<tbody>
</tbody>

 OUTPUT EMP.NUMBER EMP.NAME FIRST ENTRY TIME-AVERAGE LAST LEAVE TIME - AVERAGE DAILY WORKING HOURS - AVERAGE 1111 JOHN 08:03:20 18:20:00 09:36:40 2222 DANY 08:50:00 18:50:00 08:40:00 33 PETER 08:07:30 17:37:30 08:45:00

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

<tbody>
</tbody>

THX

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Try this, for results on sheet2.
Code:
``````[COLOR="Navy"]Sub[/COLOR] MG01Jan46
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range, Tot [COLOR="Navy"]As[/COLOR] Double, Q [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, k  [COLOR="Navy"]As[/COLOR] Variant, AvL [COLOR="Navy"]As[/COLOR] Double, AvH [COLOR="Navy"]As[/COLOR] Double
[COLOR="Navy"]Dim[/COLOR] Dic  [COLOR="Navy"]As[/COLOR] Object, p [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("Scripting.Dictionary")
Dic.CompareMode = 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
[COLOR="Navy"]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]Set[/COLOR] Dic(Dn.Value) = CreateObject("Scripting.Dictionary")
[COLOR="Navy"]End[/COLOR] If

[COLOR="Navy"]If[/COLOR] Not Dic(Dn.Value).exists(Dn.Offset(, 2).Value) [COLOR="Navy"]Then[/COLOR]
Array(Dn.Offset(, 3).Value, Dn.Offset(, 4).Value, Dn.Offset(, 5).Value, Dn)
[COLOR="Navy"]Else[/COLOR]
Q = Dic(Dn.Value).Item(Dn.Offset(, 2).Value)
[COLOR="Navy"]If[/COLOR] Dn.Offset(, 4).Value > Q(1) [COLOR="Navy"]Then[/COLOR] Q(1) = Dn.Offset(, 4).Value
[COLOR="Navy"]If[/COLOR] Dn.Offset(, 3).Value < Q(0) [COLOR="Navy"]Then[/COLOR] Q(0) = Dn.Offset(, 3).Value
Q(2) = Q(2) + Dn.Offset(, 5).Value
Dic(Dn.Value).Item(Dn.Offset(, 2).Value) = Q

[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn

ReDim ray(1 To Dic.Count + 1, 1 To 5)
ray(1, 1) = "EMP.No": ray(1, 2) = "EMP.NAME"
ray(1, 3) = "FIRST ENTRY" & vbLf & "TIME-AVERAGE"
ray(1, 4) = "LAST LEAVE" & vbLf & " TIME-AVERAGE"
ray(1, 5) = "DAILY WORKING" & vbLf & "HOURS-Average"
c = 1

[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] Dic.Keys
c = c + 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] p [COLOR="Navy"]In[/COLOR] Dic(k)
ray(c, 1) = k
ray(c, 2) = Dic(k).Item(p)(3).Offset(, 1).Value
AvL = AvL + Dic(k).Item(p)(0)
AvH = AvH + Dic(k).Item(p)(1)
Tot = Tot + Dic(k).Item(p)(2)
[COLOR="Navy"]Next[/COLOR] p

ray(c, 3) = Format(AvL / Dic(k).Count, "hh:mm:ss")
ray(c, 4) = Format(AvH / Dic(k).Count, "hh:mm:ss")
ray(c, 5) = Format(Tot / Dic(k).Count, "hh:mm:ss")
Tot = 0: AvL = 0: AvH = 0
[COLOR="Navy"]Next[/COLOR] k
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(UBound(ray, 1), 5)
.Value = ray
.Borders.Weight = 2
.Columns.AutoFit
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]``````
Regards Mick

Replies
6
Views
201
Replies
1
Views
113
Replies
1
Views
99
Replies
11
Views
391
Replies
3
Views
262

1,203,224
Messages
6,054,234
Members
444,711
Latest member
Stupid Idiot

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

### Which adblocker are you using?

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

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