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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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

CubaRJ
  • Question Question
Calculate Overtime with 2 codes for the same date
Replies
0
Views
179
CubaRJ
CubaRJ
R
  • Solved
Age Group Ranging by gender
Replies
2
Views
231
Raj1979
R
W
  • Question Question
Calculate total time taken to complete task in working days (6am to 6pm)
Replies
4
Views
437
Wibab
W
A
  • Question Question
SUMIFS
Replies
1
Views
301
Rijnsent
Rijnsent
M
  • Question Question
SLA Time Calculation
Replies
5
Views
306
felixstraube
F

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

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