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

[liorchn]

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

 

[MickG]

Try this, for results on sheet2.

[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]
                Dic(Dn.Value).Add (Dn.Offset(, 2).Value), _
                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