Complete missing dates

P

Panoos64

Well-known Member
Joined
Mar 1, 2014
Messages
890
Hi all, i would like to support me so that to create a vba code, which should run through col. "C" and complete the missing dates by inserting a new row. Please note that the code should complete based on month's days. e.g. 30 or 31 or 28 The employees are 200+ and for each one i would like to complete the missing dates. below is an extract of the original data. Many thanks in advance.



<tbody>
[TD="align: center"][/TD]
[TD="width: 42, align: center"]A[/TD]
[TD="width: 113, align: center"]B[/TD]
[TD="width: 59, align: center"]C[/TD]

[TD="align: center"]1[/TD]
[TD="class: xl67, align: center"]I.D.[/TD]
[TD="class: xl73, align: center"]Name[/TD]
[TD="class: xl67, align: center"]Date[/TD]

[TD="align: center"]2[/TD]
[TD="class: xl65, align: center"]1555[/TD]
[TD="class: xl74, align: center"]EMPL. 1555[/TD]
[TD="class: xl66, align: center"]01-12-17[/TD]

[TD="align: center"]3[/TD]
[TD="class: xl65, align: center"]1555[/TD]
[TD="class: xl74, align: center"]EMPL. 1555[/TD]
[TD="class: xl66, align: center"]04-12-17[/TD]

[TD="align: center"]4[/TD]
[TD="class: xl65, align: center"]1555[/TD]
[TD="class: xl74, align: center"]EMPL. 1555[/TD]
[TD="class: xl66, align: center"]05-12-17[/TD]

[TD="align: center"]5[/TD]
[TD="class: xl65, align: center"]1555[/TD]
[TD="class: xl74, align: center"]EMPL. 1555[/TD]
[TD="class: xl66, align: center"]06-12-17[/TD]

[TD="align: center"]6[/TD]
[TD="class: xl65, align: center"]1555[/TD]
[TD="class: xl74, align: center"]EMPL. 1555[/TD]
[TD="class: xl66, align: center"]07-12-17[/TD]

[TD="align: center"]7[/TD]
[TD="class: xl65, align: center"]1555[/TD]
[TD="class: xl74, align: center"]EMPL. 1555[/TD]
[TD="class: xl66, align: center"]08-12-17[/TD]

[TD="align: center"]8[/TD]
[TD="class: xl65, align: center"]1555[/TD]
[TD="class: xl74, align: center"]EMPL. 1555[/TD]
[TD="class: xl66, align: center"]11-12-17[/TD]

[TD="align: center"]9[/TD]
[TD="class: xl65, align: center"]1555[/TD]
[TD="class: xl74, align: center"]EMPL. 1555[/TD]
[TD="class: xl66, align: center"]12-12-17[/TD]

[TD="align: center"]10[/TD]
[TD="class: xl65, align: center"]1555[/TD]
[TD="class: xl74, align: center"]EMPL. 1555[/TD]
[TD="class: xl66, align: center"]13-12-17[/TD]

[TD="align: center"]11[/TD]
[TD="class: xl65, align: center"]1555[/TD]
[TD="class: xl74, align: center"]EMPL. 1555[/TD]
[TD="class: xl66, align: center"]14-12-17[/TD]

[TD="align: center"]12[/TD]
[TD="class: xl65, align: center"]1555[/TD]
[TD="class: xl74, align: center"]EMPL. 1555[/TD]
[TD="class: xl66, align: center"]15-12-17[/TD]

[TD="align: center"]13[/TD]
[TD="class: xl65, align: center"]1555[/TD]
[TD="class: xl74, align: center"]EMPL. 1555[/TD]
[TD="class: xl66, align: center"]18-12-17[/TD]

[TD="align: center"]14[/TD]
[TD="class: xl65, align: center"]1555[/TD]
[TD="class: xl74, align: center"]EMPL. 1555[/TD]
[TD="class: xl66, align: center"]19-12-17[/TD]

[TD="align: center"]15[/TD]
[TD="class: xl65, align: center"]1555[/TD]
[TD="class: xl74, align: center"]EMPL. 1555[/TD]
[TD="class: xl66, align: center"]20-12-17[/TD]

[TD="align: center"]16[/TD]
[TD="class: xl65, align: center"]1555[/TD]
[TD="class: xl74, align: center"]EMPL. 1555[/TD]
[TD="class: xl66, align: center"]21-12-17[/TD]

[TD="align: center"]17[/TD]
[TD="class: xl65, align: center"]1555[/TD]
[TD="class: xl74, align: center"]EMPL. 1555[/TD]
[TD="class: xl66, align: center"]22-12-17[/TD]

[TD="align: center"]18[/TD]
[TD="class: xl65, align: center"]1555[/TD]
[TD="class: xl74, align: center"]EMPL. 1555[/TD]
[TD="class: xl66, align: center"]27-12-17[/TD]

[TD="align: center"]19[/TD]
[TD="class: xl65, align: center"]1555[/TD]
[TD="class: xl74, align: center"]EMPL. 1555[/TD]
[TD="class: xl66, align: center"]28-12-17[/TD]

[TD="align: center"]20[/TD]
[TD="class: xl65, align: center"]1555[/TD]
[TD="class: xl74, align: center"]EMPL. 1555[/TD]
[TD="class: xl66, align: center"]29-12-17[/TD]

[TD="align: center"]21[/TD]
[TD="class: xl72, align: center"]1555 Count[/TD]
[TD="class: xl75, align: center"][/TD]
[TD="class: xl70, align: center"][/TD]

[TD="align: center"]22[/TD]
[TD="class: xl68, align: center"]1555 Total[/TD]
[TD="class: xl75, align: center"][/TD]
[TD="class: xl70, align: center"][/TD]

[TD="align: center"]23[/TD]
[TD="class: xl68, align: center"][/TD]
[TD="class: xl69, align: center"][/TD]
[TD="class: xl70, align: center"][/TD]

[TD="align: center"]24[/TD]
[TD="class: xl65, align: center"]1578[/TD]
[TD="class: xl65, align: center"]EMPL. 1578[/TD]
[TD="class: xl66, align: center"]01-12-17[/TD]

[TD="align: center"]25[/TD]
[TD="class: xl65, align: center"]1578[/TD]
[TD="class: xl65, align: center"]EMPL. 1578[/TD]
[TD="class: xl66, align: center"]04-12-17[/TD]

[TD="align: center"]26[/TD]
[TD="class: xl65, align: center"]1578[/TD]
[TD="class: xl65, align: center"]EMPL. 1578[/TD]
[TD="class: xl66, align: center"]05-12-17[/TD]

[TD="align: center"]27[/TD]
[TD="class: xl65, align: center"]1578[/TD]
[TD="class: xl65, align: center"]EMPL. 1578[/TD]
[TD="class: xl66, align: center"]06-12-17[/TD]

[TD="align: center"]28[/TD]
[TD="class: xl65, align: center"]1578[/TD]
[TD="class: xl65, align: center"]EMPL. 1578[/TD]
[TD="class: xl66, align: center"]07-12-17[/TD]

[TD="align: center"]29[/TD]
[TD="class: xl65, align: center"]1578[/TD]
[TD="class: xl65, align: center"]EMPL. 1578[/TD]
[TD="class: xl66, align: center"]08-12-17[/TD]

[TD="align: center"]30[/TD]
[TD="class: xl65, align: center"]1578[/TD]
[TD="class: xl65, align: center"]EMPL. 1578[/TD]
[TD="class: xl66, align: center"]11-12-17[/TD]

[TD="align: center"]31[/TD]
[TD="class: xl65, align: center"]1578[/TD]
[TD="class: xl65, align: center"]EMPL. 1578[/TD]
[TD="class: xl66, align: center"]12-12-17[/TD]

[TD="align: center"]32[/TD]
[TD="class: xl65, align: center"]1578[/TD]
[TD="class: xl65, align: center"]EMPL. 1578[/TD]
[TD="class: xl66, align: center"]13-12-17[/TD]

[TD="align: center"]33[/TD]
[TD="class: xl65, align: center"]1578[/TD]
[TD="class: xl65, align: center"]EMPL. 1578[/TD]
[TD="class: xl66, align: center"]14-12-17[/TD]

[TD="align: center"]34[/TD]
[TD="class: xl65, align: center"]1578[/TD]
[TD="class: xl65, align: center"]EMPL. 1578[/TD]
[TD="class: xl66, align: center"]15-12-17[/TD]

[TD="align: center"]35[/TD]
[TD="class: xl65, align: center"]1578[/TD]
[TD="class: xl65, align: center"]EMPL. 1578[/TD]
[TD="class: xl66, align: center"]18-12-17[/TD]

[TD="align: center"]36[/TD]
[TD="class: xl65, align: center"]1578[/TD]
[TD="class: xl65, align: center"]EMPL. 1578[/TD]
[TD="class: xl66, align: center"]19-12-17[/TD]

[TD="align: center"]37[/TD]
[TD="class: xl65, align: center"]1578[/TD]
[TD="class: xl65, align: center"]EMPL. 1578[/TD]
[TD="class: xl66, align: center"]20-12-17[/TD]

[TD="align: center"]38[/TD]
[TD="class: xl65, align: center"]1578[/TD]
[TD="class: xl65, align: center"]EMPL. 1578[/TD]
[TD="class: xl66, align: center"]21-12-17[/TD]

[TD="align: center"]39[/TD]
[TD="class: xl65, align: center"]1578[/TD]
[TD="class: xl65, align: center"]EMPL. 1578[/TD]
[TD="class: xl66, align: center"]22-12-17[/TD]

[TD="align: center"]40[/TD]
[TD="class: xl65, align: center"]1578[/TD]
[TD="class: xl65, align: center"]EMPL. 1578[/TD]
[TD="class: xl66, align: center"]27-12-17[/TD]

[TD="align: center"]41[/TD]
[TD="class: xl65, align: center"]1578[/TD]
[TD="class: xl65, align: center"]EMPL. 1578[/TD]
[TD="class: xl66, align: center"]28-12-17[/TD]

[TD="align: center"]42[/TD]
[TD="class: xl65, align: center"]1578[/TD]
[TD="class: xl65, align: center"]EMPL. 1578[/TD]
[TD="class: xl66, align: center"]29-12-17[/TD]

[TD="align: center"]43[/TD]
[TD="class: xl71, align: center"]1578 Count[/TD]
[TD="class: xl69, align: center"][/TD]
[TD="class: xl70, align: center"][/TD]

[TD="align: center"]44[/TD]
[TD="class: xl71, align: center"]1578 Total[/TD]
[TD="class: xl69, align: center"][/TD]
[TD="class: xl70, align: center"][/TD]

[TD="align: center"]45[/TD]
[TD="class: xl71, align: center"][/TD]
[TD="class: xl69, align: center"][/TD]
[TD="class: xl70, align: center"][/TD]

</tbody>
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
Try this:-
Results start "E1"
Code: 
[COLOR=navy]Sub[/COLOR] MG17Jan14
[COLOR=navy]Dim[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Dat [COLOR=navy]As[/COLOR] Date, Num [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Mth [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Yr [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, K [COLOR=navy]As[/COLOR] Variant, Dt [COLOR=navy]As[/COLOR] Date, c [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
c = 1
Range("E1").Resize(, 3).Value = Array("I.D.", "Name", "Date")
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    [COLOR=navy]If[/COLOR] Dn.Offset(, 1).Value <> "" [COLOR=navy]Then[/COLOR]
        [COLOR=navy]If[/COLOR] Not .Exists(Dn.Value & "," & Dn.Offset(, 1).Value) [COLOR=navy]Then[/COLOR]
            .Add Dn.Value & "," & Dn.Offset(, 1).Value, DateSerial(Year(Dn.Offset(, 2).Value), Month(Dn.Offset(, 2).Value), 1)
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR]
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] K [COLOR=navy]In[/COLOR] .keys
    [COLOR=navy]For[/COLOR] n = 1 To 12
        [COLOR=navy]If[/COLOR] n = Month(.Item(K)) [COLOR=navy]Then[/COLOR]
            [COLOR=navy]If[/COLOR] n = 12 [COLOR=navy]Then[/COLOR]
                Mth = 1: Yr = Year(DateAdd("yyyy", 1, Dat))
            [COLOR=navy]Else[/COLOR]
                Mth = n + 1: Yr = Year(Dat)
            [COLOR=navy]End[/COLOR] If
            Num = Day(DateAdd("d", -1, DateSerial(Yr, Mth, 1)))
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] n

Dt = DateAdd("d", -1, CDate(.Item(K)))
    [COLOR=navy]For[/COLOR] n = 1 To Num
        c = c + 1
        Cells(c, "E") = Split(K, ",")(0)
        Cells(c, "F") = Split(K, ",")(1)
        Cells(c, "G") = DateAdd("d", n, Dt)
    [COLOR=navy]Next[/COLOR] n
        c = c + 1
        Cells(c, "E") = Split(K, ",")(0) & " " & "Count"
        c = c + 1
        Cells(c, "E") = Split(K, ",")(0) & " " & "Total"
[COLOR=navy]Next[/COLOR] K
[COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
 
Last edited:
Upvote 0
Hi Mick and many thanks for your support. The code works but, i wanted to insert entire rows for missing dates, entering the missing date in the same column "C" so that to know the dates for each employee, (she's /he's) days off. Apologies that i did not present exactly the original data. I would be greatly appreciate if you could modify the code.
I present below an extract of complete data. Thanks once again for your support. Have a great day!



<tbody>
[TD="class: xl70"]A
[/TD]
[TD="class: xl70, width: 95"]B[/TD]
[TD="class: xl70, width: 95"]C[/TD]
[TD="class: xl70, width: 95"]D[/TD]
[TD="class: xl70, width: 95"]E[/TD]
[TD="class: xl70, width: 80"]F[/TD]

[TD="class: xl68, width: 42"]1[/TD]
[TD="class: xl68, width: 103"]I.D.[/TD]
[TD="class: xl68, width: 95"]Name[/TD]
[TD="class: xl68, width: 95"]Date[/TD]
[TD="class: xl64"]IN[/TD]
[TD="class: xl64"]OUT[/TD]
[TD="class: xl65"]Total Time[/TD]

[TD="class: xl68, width: 42"]2[/TD]
[TD="class: xl68, width: 103"]1555[/TD]
[TD="class: xl68, width: 95"]EMPL. 1555[/TD]
[TD="class: xl69, width: 95"]1/12/2017[/TD]
[TD="class: xl63, align: right"]7:45:20[/TD]
[TD="class: xl63, align: right"]17:43:31[/TD]
[TD="class: xl66"]9:58[/TD]

[TD="class: xl68, width: 42"]3[/TD]
[TD="class: xl68, width: 103"]1555[/TD]
[TD="class: xl68, width: 95"]EMPL. 1555[/TD]
[TD="class: xl69, width: 95"]4/12/2017[/TD]
[TD="class: xl63, align: right"]7:46:06[/TD]
[TD="class: xl63, align: right"]18:01:07[/TD]
[TD="class: xl66"]10:15[/TD]

[TD="class: xl68, width: 42"]4[/TD]
[TD="class: xl68, width: 103"]1555[/TD]
[TD="class: xl68, width: 95"]EMPL. 1555[/TD]
[TD="class: xl69, width: 95"]5/12/2017[/TD]
[TD="class: xl63, align: right"]7:57:06[/TD]
[TD="class: xl63, align: right"]18:11:06[/TD]
[TD="class: xl66"]10:14[/TD]

[TD="class: xl68, width: 42"]5[/TD]
[TD="class: xl68, width: 103"]1555[/TD]
[TD="class: xl68, width: 95"]EMPL. 1555[/TD]
[TD="class: xl69, width: 95"]6/12/2017[/TD]
[TD="class: xl63, align: right"]7:46:34[/TD]
[TD="class: xl63, align: right"]16:25:49[/TD]
[TD="class: xl66"]8:39[/TD]

[TD="class: xl68, width: 42"]6[/TD]
[TD="class: xl68, width: 103"]1555[/TD]
[TD="class: xl68, width: 95"]EMPL. 1555[/TD]
[TD="class: xl69, width: 95"]7/12/2017[/TD]
[TD="class: xl63, align: right"]7:39:45[/TD]
[TD="class: xl63, align: right"]11:30:20[/TD]
[TD="class: xl67"]3:91[/TD]

[TD="class: xl68, width: 42"]7[/TD]
[TD="class: xl68, width: 103"]1555[/TD]
[TD="class: xl68, width: 95"]EMPL. 1555[/TD]
[TD="class: xl69, width: 95"]8/12/2017[/TD]
[TD="class: xl63, align: right"]7:45:18[/TD]
[TD="class: xl63, align: right"]17:43:49[/TD]
[TD="class: xl66"]9:58[/TD]

[TD="class: xl68, width: 42"]8[/TD]
[TD="class: xl68, width: 103"]1555[/TD]
[TD="class: xl68, width: 95"]EMPL. 1555[/TD]
[TD="class: xl69, width: 95"]11/12/2017[/TD]
[TD="class: xl63, align: right"]7:43:07[/TD]
[TD="class: xl63, align: right"]17:26:02[/TD]
[TD="class: xl66"]9:43[/TD]

[TD="class: xl68, width: 42"]9[/TD]
[TD="class: xl68, width: 103"]1555[/TD]
[TD="class: xl68, width: 95"]EMPL. 1555[/TD]
[TD="class: xl69, width: 95"]12/12/2017[/TD]
[TD="class: xl63, align: right"]7:43:54[/TD]
[TD="class: xl63, align: right"]17:54:35[/TD]
[TD="class: xl66"]10:11[/TD]

[TD="class: xl68, width: 42"]10[/TD]
[TD="class: xl68, width: 103"]1555[/TD]
[TD="class: xl68, width: 95"]EMPL. 1555[/TD]
[TD="class: xl69, width: 95"]13/12/2017[/TD]
[TD="class: xl63, align: right"]8:11:19[/TD]
[TD="class: xl63, align: right"]16:50:17[/TD]
[TD="class: xl66"]8:39[/TD]

[TD="class: xl68, width: 42"]11[/TD]
[TD="class: xl68, width: 103"]1555[/TD]
[TD="class: xl68, width: 95"]EMPL. 1555[/TD]
[TD="class: xl69, width: 95"]14/12/2017[/TD]
[TD="class: xl63, align: right"]7:55:30[/TD]
[TD="class: xl63, align: right"]17:29:32[/TD]
[TD="class: xl66"]9:34[/TD]

[TD="class: xl68, width: 42"]12[/TD]
[TD="class: xl68, width: 103"]1555[/TD]
[TD="class: xl68, width: 95"]EMPL. 1555[/TD]
[TD="class: xl69, width: 95"]15/12/2017[/TD]
[TD="class: xl63, align: right"]7:43:36[/TD]
[TD="class: xl63, align: right"]12:30:20[/TD]
[TD="class: xl67"]4:87[/TD]

[TD="class: xl68, width: 42"]13[/TD]
[TD="class: xl68, width: 103"]1555[/TD]
[TD="class: xl68, width: 95"]EMPL. 1555[/TD]
[TD="class: xl69, width: 95"]18/12/2017[/TD]
[TD="class: xl63, align: right"]7:43:31[/TD]
[TD="class: xl63, align: right"]17:04:09[/TD]
[TD="class: xl66"]9:21[/TD]

[TD="class: xl68, width: 42"]14[/TD]
[TD="class: xl68, width: 103"]1555[/TD]
[TD="class: xl68, width: 95"]EMPL. 1555[/TD]
[TD="class: xl69, width: 95"]19/12/2017[/TD]
[TD="class: xl63, align: right"]7:40:08[/TD]
[TD="class: xl63, align: right"]16:41:41[/TD]
[TD="class: xl66"]9:01[/TD]

[TD="class: xl68, width: 42"]15[/TD]
[TD="class: xl68, width: 103"]1555[/TD]
[TD="class: xl68, width: 95"]EMPL. 1555[/TD]
[TD="class: xl69, width: 95"]20/12/2017[/TD]
[TD="class: xl63, align: right"]7:41:35[/TD]
[TD="class: xl63, align: right"]17:20:32[/TD]
[TD="class: xl66"]9:39[/TD]

[TD="class: xl68, width: 42"]16[/TD]
[TD="class: xl68, width: 103"]1555[/TD]
[TD="class: xl68, width: 95"]EMPL. 1555[/TD]
[TD="class: xl69, width: 95"]21/12/2017[/TD]
[TD="class: xl63, align: right"]7:39:23[/TD]
[TD="class: xl63, align: right"]17:02:59[/TD]
[TD="class: xl66"]9:23[/TD]

[TD="class: xl68, width: 42"]17[/TD]
[TD="class: xl68, width: 103"]1555[/TD]
[TD="class: xl68, width: 95"]EMPL. 1555[/TD]
[TD="class: xl69, width: 95"]22/12/2017[/TD]
[TD="class: xl63, align: right"]7:44:40[/TD]
[TD="class: xl63, align: right"]16:45:47[/TD]
[TD="class: xl66"]9:01[/TD]

[TD="class: xl68, width: 42"]18[/TD]
[TD="class: xl68, width: 103"]1555[/TD]
[TD="class: xl68, width: 95"]EMPL. 1555[/TD]
[TD="class: xl69, width: 95"]27/12/2017[/TD]
[TD="class: xl63, align: right"]7:43:02[/TD]
[TD="class: xl63, align: right"]16:52:56[/TD]
[TD="class: xl66"]9:09[/TD]

[TD="class: xl68, width: 42"]19[/TD]
[TD="class: xl68, width: 103"]1555[/TD]
[TD="class: xl68, width: 95"]EMPL. 1555[/TD]
[TD="class: xl69, width: 95"]28/12/2017[/TD]
[TD="class: xl63, align: right"]7:50:06[/TD]
[TD="class: xl63, align: right"]17:39:08[/TD]
[TD="class: xl66"]9:49[/TD]

[TD="class: xl68, width: 42"]20[/TD]
[TD="class: xl68, width: 103"]1555[/TD]
[TD="class: xl68, width: 95"]EMPL. 1555[/TD]
[TD="class: xl69, width: 95"]29/12/2017[/TD]
[TD="class: xl63, align: right"]7:42:02[/TD]
[TD="class: xl63, align: right"]17:16:47[/TD]
[TD="class: xl66"]9:34[/TD]

[TD="class: xl68, width: 42"]21[/TD]
[TD="class: xl68, width: 103"]1555 Count[/TD]
[TD="class: xl68, width: 95"][/TD]
[TD="class: xl68, width: 95"][/TD]
[TD="class: xl68, width: 95"][/TD]
[TD="class: xl68, width: 95"][/TD]
[TD="class: xl68, width: 80"][/TD]

[TD="class: xl68, width: 42"]22[/TD]
[TD="class: xl68, width: 103"]1555 Total[/TD]
[TD="class: xl68, width: 95"][/TD]
[TD="class: xl68, width: 95"][/TD]
[TD="class: xl68, width: 95"][/TD]
[TD="class: xl68, width: 95"][/TD]
[TD="class: xl68, width: 80"][/TD]

[TD="class: xl68, width: 42"]23[/TD]
[TD="class: xl68, width: 103"][/TD]
[TD="class: xl68, width: 95"][/TD]
[TD="class: xl68, width: 95"][/TD]
[TD="class: xl68, width: 95"][/TD]
[TD="class: xl68, width: 95"][/TD]
[TD="class: xl68, width: 80"][/TD]

[TD="class: xl68, width: 42"]24[/TD]
[TD="class: xl68, width: 103"]1578[/TD]
[TD="class: xl68, width: 95"]EMPL. 1578[/TD]
[TD="class: xl69, width: 95"]1/12/2017[/TD]
[TD="class: xl63, align: right"]8:59:22[/TD]
[TD="class: xl63, align: right"]17:47:46[/TD]
[TD="class: xl66"]8:48[/TD]

[TD="class: xl68, width: 42"]25[/TD]
[TD="class: xl68, width: 103"]1578[/TD]
[TD="class: xl68, width: 95"]EMPL. 1578[/TD]
[TD="class: xl69, width: 95"]4/12/2017[/TD]
[TD="class: xl63, align: right"]9:04:54[/TD]
[TD="class: xl63, align: right"]17:44:30[/TD]
[TD="class: xl66"]8:40[/TD]

[TD="class: xl68, width: 42"]26[/TD]
[TD="class: xl68, width: 103"]1578[/TD]
[TD="class: xl68, width: 95"]EMPL. 1578[/TD]
[TD="class: xl69, width: 95"]5/12/2017[/TD]
[TD="class: xl63, align: right"]9:15:58[/TD]
[TD="class: xl63, align: right"]17:50:32[/TD]
[TD="class: xl66"]8:35[/TD]

[TD="class: xl68, width: 42"]27[/TD]
[TD="class: xl68, width: 103"]1578[/TD]
[TD="class: xl68, width: 95"]EMPL. 1578[/TD]
[TD="class: xl69, width: 95"]6/12/2017[/TD]
[TD="class: xl63, align: right"]9:16:06[/TD]
[TD="class: xl63, align: right"]17:48:02[/TD]
[TD="class: xl66"]8:32[/TD]

[TD="class: xl68, width: 42"]28[/TD]
[TD="class: xl68, width: 103"]1578[/TD]
[TD="class: xl68, width: 95"]EMPL. 1578[/TD]
[TD="class: xl69, width: 95"]7/12/2017[/TD]
[TD="class: xl63, align: right"]9:10:15[/TD]
[TD="class: xl63, align: right"]17:57:15[/TD]
[TD="class: xl66"]8:47[/TD]

[TD="class: xl68, width: 42"]29[/TD]
[TD="class: xl68, width: 103"]1578[/TD]
[TD="class: xl68, width: 95"]EMPL. 1578[/TD]
[TD="class: xl69, width: 95"]8/12/2017[/TD]
[TD="class: xl63, align: right"]9:16:26[/TD]
[TD="class: xl63, align: right"]18:00:21[/TD]
[TD="class: xl66"]8:44[/TD]

[TD="class: xl68, width: 42"]30[/TD]
[TD="class: xl68, width: 103"]1578[/TD]
[TD="class: xl68, width: 95"]EMPL. 1578[/TD]
[TD="class: xl69, width: 95"]11/12/2017[/TD]
[TD="class: xl63, align: right"]8:57:33[/TD]
[TD="class: xl63, align: right"]17:49:08[/TD]
[TD="class: xl66"]8:52[/TD]

[TD="class: xl68, width: 42"]31[/TD]
[TD="class: xl68, width: 103"]1578[/TD]
[TD="class: xl68, width: 95"]EMPL. 1578[/TD]
[TD="class: xl69, width: 95"]12/12/2017[/TD]
[TD="class: xl63, align: right"]9:11:47[/TD]
[TD="class: xl63, align: right"]17:50:18[/TD]
[TD="class: xl66"]8:39[/TD]

[TD="class: xl68, width: 42"]32[/TD]
[TD="class: xl68, width: 103"]1578[/TD]
[TD="class: xl68, width: 95"]EMPL. 1578[/TD]
[TD="class: xl69, width: 95"]13/12/2017[/TD]
[TD="class: xl63, align: right"]9:22:20[/TD]
[TD="class: xl63, align: right"]17:43:47[/TD]
[TD="class: xl66"]8:21[/TD]

[TD="class: xl68, width: 42"]33[/TD]
[TD="class: xl68, width: 103"]1578[/TD]
[TD="class: xl68, width: 95"]EMPL. 1578[/TD]
[TD="class: xl69, width: 95"]14/12/2017[/TD]
[TD="class: xl63, align: right"]9:24:01[/TD]
[TD="class: xl63, align: right"]17:42:29[/TD]
[TD="class: xl66"]8:18[/TD]

[TD="class: xl68, width: 42"]34[/TD]
[TD="class: xl68, width: 103"]1578[/TD]
[TD="class: xl68, width: 95"]EMPL. 1578[/TD]
[TD="class: xl69, width: 95"]15/12/2017[/TD]
[TD="class: xl63, align: right"]9:21:59[/TD]
[TD="class: xl63, align: right"]17:48:47[/TD]
[TD="class: xl66"]8:27[/TD]

[TD="class: xl68, width: 42"]35[/TD]
[TD="class: xl68, width: 103"]1578[/TD]
[TD="class: xl68, width: 95"]EMPL. 1578[/TD]
[TD="class: xl69, width: 95"]18/12/2017[/TD]
[TD="class: xl63, align: right"]9:18:16[/TD]
[TD="class: xl63, align: right"]17:59:35[/TD]
[TD="class: xl66"]8:41[/TD]

[TD="class: xl68, width: 42"]36[/TD]
[TD="class: xl68, width: 103"]1578[/TD]
[TD="class: xl68, width: 95"]EMPL. 1578[/TD]
[TD="class: xl69, width: 95"]19/12/2017[/TD]
[TD="class: xl63, align: right"]9:22:11[/TD]
[TD="class: xl63, align: right"]18:07:04[/TD]
[TD="class: xl66"]8:45[/TD]

[TD="class: xl68, width: 42"]37[/TD]
[TD="class: xl68, width: 103"]1578[/TD]
[TD="class: xl68, width: 95"]EMPL. 1578[/TD]
[TD="class: xl69, width: 95"]20/12/2017[/TD]
[TD="class: xl63, align: right"]9:25:46[/TD]
[TD="class: xl63, align: right"]17:55:05[/TD]
[TD="class: xl66"]8:30[/TD]

[TD="class: xl68, width: 42"]38[/TD]
[TD="class: xl68, width: 103"]1578[/TD]
[TD="class: xl68, width: 95"]EMPL. 1578[/TD]
[TD="class: xl69, width: 95"]21/12/2017[/TD]
[TD="class: xl63, align: right"]9:22:22[/TD]
[TD="class: xl63, align: right"]18:04:41[/TD]
[TD="class: xl66"]8:42[/TD]

[TD="class: xl68, width: 42"]39[/TD]
[TD="class: xl68, width: 103"]1578[/TD]
[TD="class: xl68, width: 95"]EMPL. 1578[/TD]
[TD="class: xl69, width: 95"]22/12/2017[/TD]
[TD="class: xl63, align: right"]9:24:21[/TD]
[TD="class: xl63, align: right"]18:13:59[/TD]
[TD="class: xl66"]8:49[/TD]

[TD="class: xl68, width: 42"]40[/TD]
[TD="class: xl68, width: 103"]1578[/TD]
[TD="class: xl68, width: 95"]EMPL. 1578[/TD]
[TD="class: xl69, width: 95"]27/12/2017[/TD]
[TD="class: xl63, align: right"]9:34:37[/TD]
[TD="class: xl63, align: right"]11:41:03[/TD]
[TD="class: xl66"]7:54[/TD]

[TD="class: xl68, width: 42"]41[/TD]
[TD="class: xl68, width: 103"]1578[/TD]
[TD="class: xl68, width: 95"]EMPL. 1578[/TD]
[TD="class: xl69, width: 95"]28/12/2017[/TD]
[TD="class: xl63, align: right"]8:26:49[/TD]
[TD="class: xl63, align: right"]17:45:21[/TD]
[TD="class: xl66"]9:19[/TD]

[TD="class: xl68, width: 42"]42[/TD]
[TD="class: xl68, width: 103"]1578[/TD]
[TD="class: xl68, width: 95"]EMPL. 1578[/TD]
[TD="class: xl69, width: 95"]29/12/2017[/TD]
[TD="class: xl63, align: right"]8:45:51[/TD]
[TD="class: xl63, align: right"]17:44:01[/TD]
[TD="class: xl66"]8:59[/TD]

[TD="class: xl68, width: 42"]43[/TD]
[TD="class: xl68, width: 103"]1578 Count[/TD]
[TD="class: xl68, width: 95"][/TD]
[TD="class: xl68, width: 95"][/TD]
[TD="class: xl68, width: 95"][/TD]
[TD="class: xl68, width: 95"][/TD]
[TD="class: xl68, width: 80"][/TD]

[TD="class: xl68, width: 42"]44[/TD]
[TD="class: xl68, width: 103"]1578 Total[/TD]
[TD="class: xl68, width: 95"][/TD]
[TD="class: xl68, width: 95"][/TD]
[TD="class: xl68, width: 95"][/TD]
[TD="class: xl68, width: 95"][/TD]
[TD="class: xl68, width: 80"][/TD]

</tbody>
 
Upvote 0
Try this for results on sheet2.
NB:- Columns 3 to 6 of missing dates, are now shown as blank.
Code: 
[COLOR="Navy"]Sub[/COLOR] MG18Jan53
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Dat [COLOR="Navy"]As[/COLOR] Date, Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Mth [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Yr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] k [COLOR="Navy"]As[/COLOR] Variant, Dt [COLOR="Navy"]As[/COLOR] Date, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object, Ray [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] nDt [COLOR="Navy"]As[/COLOR] Date
 c = 1
Ray = Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 6)
[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("Scripting.Dictionary")
    Dic.CompareMode = 1
   [COLOR="Navy"]For[/COLOR] n = 2 To UBound(Ray, 1)
        [COLOR="Navy"]If[/COLOR] Ray(n, 2) <> "" [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Not Dic.exists(Ray(n, 1) & "," & Ray(n, 2)) [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Set[/COLOR] Dic(Ray(n, 1) & "," & Ray(n, 2)) = CreateObject("Scripting.Dictionary")
        [COLOR="Navy"]End[/COLOR] If
        
        [COLOR="Navy"]If[/COLOR] Not Dic(Ray(n, 1) & "," & Ray(n, 2)).exists(Ray(n, 3)) [COLOR="Navy"]Then[/COLOR]
                Dic(Ray(n, 1) & "," & Ray(n, 2)).Add Ray(n, 3), Array(Ray(n, 4), Ray(n, 5), Ray(n, 6))
        [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] n
   
ReDim nRay(1 To (Dic.Count + 3) * 31, 1 To 6)
nRay(1, 1) = "I.D.": nRay(1, 2) = "Name": nRay(1, 3) = "Date"
nRay(1, 4) = "IN": nRay(1, 5) = "OUT": nRay(1, 6) = "Total Time"
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] Dic.keys
    [COLOR="Navy"]For[/COLOR] n = 1 To 12
        [COLOR="Navy"]If[/COLOR] n = Month(Dic(k).keys()(0)) [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]If[/COLOR] n = 12 [COLOR="Navy"]Then[/COLOR]
                Mth = 1: Yr = Year(DateAdd("yyyy", 1, Dat))
            [COLOR="Navy"]Else[/COLOR]
                Mth = n + 1: Yr = Year(Dat)
            [COLOR="Navy"]End[/COLOR] If
            Num = Day(DateAdd("d", -1, DateSerial(Yr, Mth, 1)))
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] n
nDt = DateSerial(Year(Dic(k).keys()(0)), Month(Dic(k).keys()(0)), 1)
Dt = DateAdd("d", -1, CDate(nDt))
    [COLOR="Navy"]For[/COLOR] n = 1 To Num
        c = c + 1
        nRay(c, 1) = Split(k, ",")(0)
        nRay(c, 2) = Split(k, ",")(1)
        nRay(c, 3) = DateAdd("d", n, Dt)
            [COLOR="Navy"]If[/COLOR] Dic(k).exists(DateAdd("d", n, Dt)) [COLOR="Navy"]Then[/COLOR]
                nRay(c, 4) = Format(Dic(k).Item(DateAdd("d", n, Dt))(0), "hh:mm:ss")
                nRay(c, 5) = Format(Dic(k).Item(DateAdd("d", n, Dt))(1), "hh:mm:ss")
                nRay(c, 6) = Format(Dic(k).Item(DateAdd("d", n, Dt))(2), "hh:mm:ss")
             [COLOR="Navy"]End[/COLOR] If
   [COLOR="Navy"]Next[/COLOR] n
        c = c + 1
        nRay(c, 1) = Split(k, ",")(0) & " " & "Count"
        c = c + 1
        nRay(c, 1) = Split(k, ",")(0) & " " & "Total"
[COLOR="Navy"]Next[/COLOR] k
[COLOR="Navy"]With[/COLOR] Sheets("Sheet13").Range("A1").Resize(c, 6)
    .Value = nRay
    .Borders.Weight = 2
    .Columns.AutoFit
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Typo!!!!!
The following line at end of code should read :-
Code: 
[COLOR=#000080]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(c, 6)

Please amend code !!
 
Upvote 0
Perfect work Mick! Really i appreciated for what you done for me and especially the solutions for my projects. You support me once again and i owe to you a great thanks. My best wishes to you. Have a great lovely day!
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Count entry by time
Replies
3
Views
253
admvlad
A
CubaRJ
  • Question Question
Calculate Overtime with 2 codes for the same date
Replies
0
Views
191
CubaRJ
CubaRJ
D
  • Question Question
Locate first instance of all dates in a column and hide all remaining rows
Replies
4
Views
126
Peter_SSs
Peter_SSs
R
  • Solved
Age Group Ranging by gender
Replies
2
Views
275
Raj1979
R
W
  • Question Question
Calculate total time taken to complete task in working days (6am to 6pm)
Replies
4
Views
490
Wibab
W

Forum statistics

Threads
1,224,823
Messages
6,181,182
Members
453,021
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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