VBA help - store dates in dictionary using dateserial

[Mallesh23]

Hi Team,

I have below data, I want to store Date Columns(B) to Dictionary using Dateserial.
if there are two dates in a Cell then macro to pick max date. Right side of 10 Digit or Left side of 10 Digit.

Date is in dd/mm/yyyy format.

In my attempted code - I have used Dateserial I am not sure, whether its right. plz check and suggest correct way. Thanks in advance.


Below is data read column A and B and paste in Column I.

Book13
	A	B	C	D	E	F	G	H	I	J
1	Invoice	Date	Left-10	Expected				Invoice	Output	Mismatch
2	38044047	07/12/2019	43806	07/12/2019				38044047	07/12/2019
3	38044048	04/06/2019	43620	09/12/2019				38044048	04/06/2019
4	38044049	05/06/2019	43621	09/12/2019				38044049	05/06/2019
5	38044050	06/06/2019	43622	09/12/2019				38044050	06/06/2019
6	38044065	07/06/2019	43623	09/12/2019				38044065	07/06/2019
7	38044084	08/06/2019	43624	09/12/2019				38044084	08/06/2019
8	38403245	09/06/2019	43625	09/12/2019				38403245	09/06/2019
9	38044051	10/06/2019	43626	09/12/2019				38044051	10/06/2019
10	38044198	11/06/2019	43627	09/12/2019				38044198	11/06/2019
11	38044052	12/06/2019	43628	09/12/2019				38044052	12/06/2019
12	38044053	13/06/2019	43629	09/12/2019				38044053	13/06/2019
13	9045690	14/06/2019	43630	09/12/2019				9045690	14/06/2019
14	9045691	15/06/2019	43631	09/12/2019				9045691	15/06/2019
15	9045706	16/06/2019	43632	09/12/2019				9045706	16/06/2019
16	9045707	17/06/2019	43633	10/12/2019				9045707	17/06/2019
17	12031653	18/06/2019	43634	10/12/2019				12031653	18/06/2019
18	12032002	19/06/2019	43635	10/12/2019				12032002	19/06/2019
19	12401082	20/06/2019	43636	10/12/2019				12401082	20/06/2019
20	12031784	10/12/2019	43809	10/12/2019				12031784	10/12/2019
21	12031985	10/12/2019	43809	10/12/2019				12031985	10/12/2019
22	12032018	10/12/2019	43809	10/12/2019				12032018	10/12/2019
23	12031986	10/12/2019	43809	10/12/2019				12031986	10/12/2019
24	12031999	10/12/2019	43809	10/12/2019				12031999	10/12/2019
25	40031027	10/12/2019	43809	10/12/2019				40031027	10/12/2019
26	40031052	10/12/2019	43809	10/12/2019				40031052	10/12/2019
27	40031053	10/12/2019	43809	10/12/2019				40031053	10/12/2019
28	2100060442	10/12/2019	43809	10/12/2019				2.1E+09	10/12/2019
29	2100060443	10/12/2019	43809	10/12/2019				2.1E+09	10/12/2019
30	6029850	10/12/2019	43809	10/12/2019				6029850	10/12/2019
31	6029876	10/12/2019	43809	10/12/2019				6029876	10/12/2019
32	6029851	10/12/2019	43809	10/12/2019				6029851	10/12/2019
33	32019673	10/12/2019	43809	10/12/2019				32019673	10/12/2019
34	32019922	10/12/2019	43809	10/12/2019				32019922	10/12/2019
35	14035727	10/12/2019	43809	10/12/2019				14035727	10/12/2019
36	14035728	11/12/2019	43810	11/12/2019				14035728	11/12/2019
37	2100060417	11/12/2019	43810	11/12/2019				2.1E+09	11/12/2019
38	2100060418	11/12/2019	43810	11/12/2019				2.1E+09	11/12/2019
39	13026092	11/12/2019	43810	11/12/2019				13026092	11/12/2019
40	38044094	11/12/2019	43810	11/12/2019				38044094	11/12/2019
41	38044095	11/12/2019	43810	11/12/2019				38044095	11/12/2019
42	38044096	11/12/2019	43810	11/12/2019				38044096	11/12/2019
43	38044097	11/12/2019	43810	11/12/2019				38044097	11/12/2019
44	38044113	11/12/2019	43810	11/12/2019				38044113	11/12/2019
45	38044098	11/12/2019	43810	11/12/2019				38044098	11/12/2019
46	38044114	11/12/2019	43810	11/12/2019				38044114	11/12/2019
47	38044099	11/12/2019	43810	11/12/2019				38044099	11/12/2019
48	38044101	11/12/2019	43810	11/12/2019				38044101	11/12/2019
49	12032240	11/12/2019	43810	11/12/2019				12032240	11/12/2019
50	12032249	12/12/2019	43811	12/12/2019				12032249	12/12/2019
51	6030007	12/12/2019	43811	12/12/2019				6030007	12/12/2019
52	6030008	12/12/2019	43811	12/12/2019				6030008	12/12/2019
53	6030009	12/12/2019	43811	12/12/2019				6030009	12/12/2019
54	13025883	12/12/2019	43811	12/12/2019				13025883	12/12/2019
55	38044028	12/12/2019	43811	12/12/2019				38044028	12/12/2019
56	33039235	12/12/2019	43811	12/12/2019				33039235	12/12/2019
57	9045970	12/12/2019	43811	12/12/2019				9045970	12/12/2019
58	9045969	12/12/2019	43811	12/12/2019				9045969	12/12/2019
59	9403436	12/12/2019	43811	12/12/2019				9403436	12/12/2019
60	8000092	12/12/2019	43811	12/12/2019				8000092	12/12/2019
61	8000091	12/12/2019	43811	12/12/2019				8000091	12/12/2019
62	32020139	12/12/2019	43811	12/12/2019				32020139	12/12/2019
63	14036008	12/12/2019	43811	12/12/2019				14036008	12/12/2019
64	38044388	12/12/2019	43811	12/12/2019				38044388	12/12/2019
65	38044389	12/12/2019	43811	12/12/2019				38044389	12/12/2019
66	38403263	12/12/2019	43811	12/12/2019				38403263	12/12/2019
67	6030157	12/12/2019	43811	12/12/2019				6030157	12/12/2019
68	6030156	12/12/2019	43811	12/12/2019				6030156	12/12/2019
69	6401448	12/12/2019	43811	12/12/2019				6401448	12/12/2019
70	7000129	12/12/2019	43811	12/12/2019				7000129	12/12/2019
71	7000130	12/12/2019	43811	12/12/2019				7000130	12/12/2019
72	13026249	12/12/2019	43811	12/12/2019				13026249	12/12/2019
73	33000108	12/12/2019	43811	12/12/2019				33000108	12/12/2019
74	33000089	12/12/2019	43811	12/12/2019				33000089	12/12/2019
75	12032307	13/12/2019	43812	13/12/2019				12032307	13/12/2019
76	12401098	13/12/2019	43812	13/12/2019				12401098	13/12/2019
77	36000021	13/12/2019	43812	13/12/2019				36000021	13/12/2019
78	40031547	13/12/2019	43812	13/12/2019				40031547	13/12/2019
79	24000037	13/12/2019	43812	13/12/2019				24000037	13/12/2019
80	2100000064	13/12/2019	43812	13/12/2019				2.1E+09	13/12/2019
81	2100000063	13/12/2019	43812	13/12/2019				2.1E+09	13/12/2019
82	14036062	13/12/2019	43812	13/12/2019				14036062	13/12/2019
83	14036063	13/12/2019	43812	13/12/2019				14036063	13/12/2019
84	14036064	13/12/2019	43812	13/12/2019				14036064	13/12/2019
85	2100061510	13/12/2019	43812	13/12/2019				2.1E+09	13/12/2019
86	2100061509	13/12/2019	43812	13/12/2019				2.1E+09	13/12/2019
87	2100061508	13/12/2019	43812	13/12/2019				2.1E+09	13/12/2019
88	13026262	13/12/2019	43812	13/12/2019				13026262	13/12/2019
89	38044463	13/12/2019	43812	13/12/2019				38044463	13/12/2019
90	38044461	13/12/2019	43812	13/12/2019				38044461	13/12/2019
91	33000155	13/12/2019	43812	13/12/2019				33000155	13/12/2019
92	38044462	13/12/2019	43812	13/12/2019				38044462	13/12/2019
93	38044464	13/12/2019	43812	13/12/2019				38044464	13/12/2019
94	33000154	13/12/2019	43812	13/12/2019				33000154	13/12/2019
95	9046173	13/12/2019	43812	13/12/2019				9046173	13/12/2019
96	9046174	13/12/2019	43812	13/12/2019				9046174	13/12/2019
97	9046175	13/12/2019	43812	13/12/2019				9046175	13/12/2019
98	9403473	13/12/2019	43812	13/12/2019				9403473	13/12/2019
99	8000346	14/12/2019	43813	14/12/2019				8000346	14/12/2019
100	8000347	14/12/2019	43813	14/12/2019				8000347	14/12/2019
101	8000348	14/12/2019	43813	14/12/2019				8000348	14/12/2019
102	2100000106	14/12/2019	43813	14/12/2019				2.1E+09	14/12/2019
103	2100000105	14/12/2019	43813	14/12/2019				2.1E+09	14/12/2019
104	9046187	14/12/2019	43813	14/12/2019				9046187	14/12/2019
105	9046186	14/12/2019	43813	14/12/2019				9046186	14/12/2019
106	9046185	14/12/2019	43813	14/12/2019				9046185	14/12/2019
107	33039603	14/12/2019	43813	14/12/2019				33039603	14/12/2019
108	2100061589	14/12/2019	43813	14/12/2019				2.1E+09	14/12/2019
109	2100061588	14/12/2019	43813	14/12/2019				2.1E+09	14/12/2019
110	2100061587	14/12/2019	43813	14/12/2019				2.1E+09	14/12/2019
111	38044501	14/12/2019	43813	14/12/2019				38044501	14/12/2019
112	38044502	14/12/2019	43813	14/12/2019				38044502	14/12/2019
113	38403282	14/12/2019	43813	14/12/2019				38403282	14/12/2019
114	33000187	14/12/2019	43813	14/12/2019				33000187	14/12/2019
115	38044503	14/12/2019	43813	14/12/2019				38044503	14/12/2019
116	33000186	14/12/2019	43813	14/12/2019				33000186	14/12/2019
117	38044387	14/12/2019	43813	14/12/2019				38044387	14/12/2019
118	33000032	14/12/2019	43813	14/12/2019				33000032	14/12/2019
119	36000089	14/12/2019	43813	14/12/2019				36000089	14/12/2019
120	7000351	14/12/2019	43813	14/12/2019				7000351	14/12/2019
121	38403262	16/12/2019	43815	16/12/2019				38403262	16/12/2019
122	38044563	16/12/2019	43815	16/12/2019				38044563	16/12/2019
123	38044564	16/12/2019	43815	16/12/2019				38044564	16/12/2019
124	38044565	16/12/2019	43815	16/12/2019				38044565	16/12/2019
125	33000262	16/12/2019	43815	16/12/2019				33000262	16/12/2019
126	33000261	02-07-2019/03-07-2019	03-07-2019	03/07/2019				33000261	03/07/2019
127	33000260	07-05-2019/08-05-2019	08-05-2019	08/05/2019				33000260	08/05/2019
128	12032417	07-05-2019 - 08-05-2019	08-05-2019	08/05/2019				12032417	08/05/2019
129	12032419	07-10-2020 12-10-2020	12-10-2020	12/10/2020				12032419	12/10/2020
Sheet1

Cell Formulas
Range	Formula
C2:C129	C2	=IF(LEN(B2)>5,RIGHT(B2,10),LEFT(B2,10))

Sub Dateserial_With_Dictionary()
    
    Dim i As Long
    Dim dt As Variant
    Dim date1 As Date
    Dim date2 As Date
    Dim c As Range
    Dim skey As String

        Dim arr As Variant
        arr = Range("A1").CurrentRegion.Value


    Dim dict As New Scripting.Dictionary
    dict.RemoveAll
    dict.CompareMode = TextCompare

    
    
    With dict
        For i = 2 To UBound(arr, 1)
            If Not IsError(arr(i, 1)) Then
                skey = arr(i, 1)
                If Not .Exists(skey) Then
                    If Len(arr(i, 2)) < 12 Then
                        dt = CLng(DateValue(Left(arr(i, 2), 10)))
                        date1 = Format(dt, "dd/mm/yyyy")
                        date2 = DateSerial(Right(date1, 4), Mid(date1, 4, 2), Left(date1, 2))
                        .Add skey, date2
                    Else
                        'Date1 = Format(CLng(DateValue(Left(ARR(i, cGrnData_Date), 10))), "dd/mm/yyyy")
                        'date2 = Format(CLng(DateValue(Right(ARR(i, cGrnData_Date), 10))), "dd/mm/yyyy")
                        date1 = CLng(DateValue(Left(arr(i, 2), 10)))
                        date2 = CLng(DateValue(Right(arr(i, 2), 10)))
                        dt = IIf(date1 > date2, date1, date2)
                        date1 = Format(dt, "dd/mm/yyyy")
                        date2 = DateSerial(Right(date1, 4), Mid(date1, 4, 2), Left(date1, 2))
                        '.Add sKey, Format(dt, "dd/mm/yyyy")
                        .Add skey, date2
                    End If
                End If
            End If
        Next i
        
        
        'Print Dictionary Data Into Output Column
        For Each c In Range("H2:H129")
            skey = c.Value
        If .Exists(skey) Then
            c.Offset(, 1).Value = .Item(skey)
        End If
        
        Next c
        
    
    End With
    
    MsgBox "Macro Successful"

end sub

End Sub


Thanks
mg

 

[Lian]

Actually, the right(colB,10) is always the larger date.
Here is a formula solution.

Book1
	A	B	C	D	E	F	G	H	I	J
1	invoice	Date	Left-10	Expected				Invoice	Output	Mismatch
2	38044047	07/12/2019	43806	07/12/2019				38044047	07/12/2019
3	38044048	04/06/2019	43620	09/12/2019				38044048	04/06/2019
4	38044049	05/06/2019	43621	09/12/2019				38044049	05/06/2019
5	38044050	06/06/2019	43622	09/12/2019				38044050	06/06/2019
6	38044065	07/06/2019	43623	09/12/2019				38044065	07/06/2019
7	38044084	08/06/2019	43624	09/12/2019				38044084	08/06/2019
8	38403245	09/06/2019	43625	09/12/2019				38403245	09/06/2019
9	38044051	10/06/2019	43626	09/12/2019				38044051	10/06/2019
10	38044198	11/06/2019	43627	09/12/2019				38044198	11/06/2019
11	38044052	12/06/2019	43628	09/12/2019				38044052	12/06/2019
12	38044053	13/06/2019	43629	09/12/2019				38044053	13/06/2019
13	9045690	14/06/2019	43630	09/12/2019				9045690	14/06/2019
14	9045691	15/06/2019	43631	09/12/2019				9045691	15/06/2019
15	9045706	16/06/2019	43632	09/12/2019				9045706	16/06/2019
16	9045707	17/06/2019	43633	10/12/2019				9045707	17/06/2019
17	12031653	18/06/2019	43634	10/12/2019				12031653	18/06/2019
18	12032002	19/06/2019	43635	10/12/2019				12032002	19/06/2019
19	12401082	20/06/2019	43636	10/12/2019				12401082	20/06/2019
20	12031784	10/12/2019	43809	10/12/2019				12031784	10/12/2019
21	12031985	10/12/2019	43809	10/12/2019				12031985	10/12/2019
22	12032018	10/12/2019	43809	10/12/2019				12032018	10/12/2019
23	12031986	10/12/2019	43809	10/12/2019				12031986	10/12/2019
24	12031999	10/12/2019	43809	10/12/2019				12031999	10/12/2019
25	40031027	10/12/2019	43809	10/12/2019				40031027	10/12/2019
26	40031052	10/12/2019	43809	10/12/2019				40031052	10/12/2019
27	40031053	10/12/2019	43809	10/12/2019				40031053	10/12/2019
28	2100060442	10/12/2019	43809	10/12/2019				2100060442	10/12/2019
29	2100060443	10/12/2019	43809	10/12/2019				2100060443	10/12/2019
30	6029850	10/12/2019	43809	10/12/2019				6029850	10/12/2019
31	6029876	10/12/2019	43809	10/12/2019				6029876	10/12/2019
32	6029851	10/12/2019	43809	10/12/2019				6029851	10/12/2019
33	32019673	10/12/2019	43809	10/12/2019				32019673	10/12/2019
34	32019922	10/12/2019	43809	10/12/2019				32019922	10/12/2019
35	14035727	10/12/2019	43809	10/12/2019				14035727	10/12/2019
36	14035728	11/12/2019	43810	11/12/2019				14035728	11/12/2019
37	2100060417	11/12/2019	43810	11/12/2019				2100060417	11/12/2019
38	2100060418	11/12/2019	43810	11/12/2019				2100060418	11/12/2019
39	13026092	11/12/2019	43810	11/12/2019				13026092	11/12/2019
40	38044094	11/12/2019	43810	11/12/2019				38044094	11/12/2019
41	38044095	11/12/2019	43810	11/12/2019				38044095	11/12/2019
42	38044096	11/12/2019	43810	11/12/2019				38044096	11/12/2019
43	38044097	11/12/2019	43810	11/12/2019				38044097	11/12/2019
44	38044113	11/12/2019	43810	11/12/2019				38044113	11/12/2019
45	38044098	11/12/2019	43810	11/12/2019				38044098	11/12/2019
46	38044114	11/12/2019	43810	11/12/2019				38044114	11/12/2019
47	38044099	11/12/2019	43810	11/12/2019				38044099	11/12/2019
48	38044101	11/12/2019	43810	11/12/2019				38044101	11/12/2019
49	12032240	11/12/2019	43810	11/12/2019				12032240	11/12/2019
50	12032249	12/12/2019	43811	12/12/2019				12032249	12/12/2019
51	6030007	12/12/2019	43811	12/12/2019				6030007	12/12/2019
52	6030008	12/12/2019	43811	12/12/2019				6030008	12/12/2019
53	6030009	12/12/2019	43811	12/12/2019				6030009	12/12/2019
54	13025883	12/12/2019	43811	12/12/2019				13025883	12/12/2019
55	38044028	12/12/2019	43811	12/12/2019				38044028	12/12/2019
56	33039235	12/12/2019	43811	12/12/2019				33039235	12/12/2019
57	9045970	12/12/2019	43811	12/12/2019				9045970	12/12/2019
58	9045969	12/12/2019	43811	12/12/2019				9045969	12/12/2019
59	9403436	12/12/2019	43811	12/12/2019				9403436	12/12/2019
60	8000092	12/12/2019	43811	12/12/2019				8000092	12/12/2019
61	8000091	12/12/2019	43811	12/12/2019				8000091	12/12/2019
62	32020139	12/12/2019	43811	12/12/2019				32020139	12/12/2019
63	14036008	12/12/2019	43811	12/12/2019				14036008	12/12/2019
64	38044388	12/12/2019	43811	12/12/2019				38044388	12/12/2019
65	38044389	12/12/2019	43811	12/12/2019				38044389	12/12/2019
66	38403263	12/12/2019	43811	12/12/2019				38403263	12/12/2019
67	6030157	12/12/2019	43811	12/12/2019				6030157	12/12/2019
68	6030156	12/12/2019	43811	12/12/2019				6030156	12/12/2019
69	6401448	12/12/2019	43811	12/12/2019				6401448	12/12/2019
70	7000129	12/12/2019	43811	12/12/2019				7000129	12/12/2019
71	7000130	12/12/2019	43811	12/12/2019				7000130	12/12/2019
72	13026249	12/12/2019	43811	12/12/2019				13026249	12/12/2019
73	33000108	12/12/2019	43811	12/12/2019				33000108	12/12/2019
74	33000089	12/12/2019	43811	12/12/2019				33000089	12/12/2019
75	12032307	13/12/2019	43812	13/12/2019				12032307	13/12/2019
76	12401098	13/12/2019	43812	13/12/2019				12401098	13/12/2019
77	36000021	13/12/2019	43812	13/12/2019				36000021	13/12/2019
78	40031547	13/12/2019	43812	13/12/2019				40031547	13/12/2019
79	24000037	13/12/2019	43812	13/12/2019				24000037	13/12/2019
80	2100000064	13/12/2019	43812	13/12/2019				2100000064	13/12/2019
81	2100000063	13/12/2019	43812	13/12/2019				2100000063	13/12/2019
82	14036062	13/12/2019	43812	13/12/2019				14036062	13/12/2019
83	14036063	13/12/2019	43812	13/12/2019				14036063	13/12/2019
84	14036064	13/12/2019	43812	13/12/2019				14036064	13/12/2019
85	2100061510	13/12/2019	43812	13/12/2019				2100061510	13/12/2019
86	2100061509	13/12/2019	43812	13/12/2019				2100061509	13/12/2019
87	2100061508	13/12/2019	43812	13/12/2019				2100061508	13/12/2019
88	13026262	13/12/2019	43812	13/12/2019				13026262	13/12/2019
89	38044463	13/12/2019	43812	13/12/2019				38044463	13/12/2019
90	38044461	13/12/2019	43812	13/12/2019				38044461	13/12/2019
91	33000155	13/12/2019	43812	13/12/2019				33000155	13/12/2019
92	38044462	13/12/2019	43812	13/12/2019				38044462	13/12/2019
93	38044464	13/12/2019	43812	13/12/2019				38044464	13/12/2019
94	33000154	13/12/2019	43812	13/12/2019				33000154	13/12/2019
95	9046173	13/12/2019	43812	13/12/2019				9046173	13/12/2019
96	9046174	13/12/2019	43812	13/12/2019				9046174	13/12/2019
97	9046175	13/12/2019	43812	13/12/2019				9046175	13/12/2019
98	9403473	13/12/2019	43812	13/12/2019				9403473	13/12/2019
99	8000346	14/12/2019	43813	14/12/2019				8000346	14/12/2019
100	8000347	14/12/2019	43813	14/12/2019				8000347	14/12/2019
101	8000348	14/12/2019	43813	14/12/2019				8000348	14/12/2019
102	2100000106	14/12/2019	43813	14/12/2019				2100000106	14/12/2019
103	2100000105	14/12/2019	43813	14/12/2019				2100000105	14/12/2019
104	9046187	14/12/2019	43813	14/12/2019				9046187	14/12/2019
105	9046186	14/12/2019	43813	14/12/2019				9046186	14/12/2019
106	9046185	14/12/2019	43813	14/12/2019				9046185	14/12/2019
107	33039603	14/12/2019	43813	14/12/2019				33039603	14/12/2019
108	2100061589	14/12/2019	43813	14/12/2019				2100061589	14/12/2019
109	2100061588	14/12/2019	43813	14/12/2019				2100061588	14/12/2019
110	2100061587	14/12/2019	43813	14/12/2019				2100061587	14/12/2019
111	38044501	14/12/2019	43813	14/12/2019				38044501	14/12/2019
112	38044502	14/12/2019	43813	14/12/2019				38044502	14/12/2019
113	38403282	14/12/2019	43813	14/12/2019				38403282	14/12/2019
114	33000187	14/12/2019	43813	14/12/2019				33000187	14/12/2019
115	38044503	14/12/2019	43813	14/12/2019				38044503	14/12/2019
116	33000186	14/12/2019	43813	14/12/2019				33000186	14/12/2019
117	38044387	14/12/2019	43813	14/12/2019				38044387	14/12/2019
118	33000032	14/12/2019	43813	14/12/2019				33000032	14/12/2019
119	36000089	14/12/2019	43813	14/12/2019				36000089	14/12/2019
120	7000351	14/12/2019	43813	14/12/2019				7000351	14/12/2019
121	38403262	16/12/2019	43815	16/12/2019				38403262	16/12/2019
122	38044563	16/12/2019	43815	16/12/2019				38044563	16/12/2019
123	38044564	16/12/2019	43815	16/12/2019				38044564	16/12/2019
124	38044565	16/12/2019	43815	16/12/2019				38044565	16/12/2019
125	33000262	16/12/2019	43815	16/12/2019				33000262	16/12/2019
126	33000261	02-07-2019/03-07-2019	03-07-2019	03/07/2019				33000261	03/07/2019
127	33000260	07-05-2019/08-05-2019	08-05-2019	08/05/2019				33000260	08/05/2019
128	12032417	07-05-2019 - 08-05-2019	08-05-2019	08/05/2019				12032417	08/05/2019
129	12032419	07-10-2020 12-10-2020	12-10-2020	12/10/2020				12032419	12/10/2020
Sheet3

Cell Formulas
Range	Formula
I2:I129	I2	=TEXT(DATE(RIGHT(RIGHT(VLOOKUP(H2,A:B,2,0),10),4),MID(RIGHT(VLOOKUP(H2,A:B,2,0),10),4,2),LEFT(RIGHT(VLOOKUP(H2,A:B,2,0),10),2)),"dd/mm/yyyy")

 

[Akuini]

I have below data, I want to store Date Columns(B) to Dictionary using Dateserial.

Hi, @Mallesh23
Why do you want to store the date in Dictionary using Dateserial?
Do you mean you want to display the result in col I as "yyyy-mm-dd"? if yes then you can do it manually by changing the cell format as needed via menu Format cell.

 

[Lian]

Try this with dictionary and also choose the maxium date.

Sub checkDate()

Dim d As Object
Dim arr As Variant, k As Variant, t As Variant
Dim i As Long
Dim sKey As String

With ActiveSheet
    .Columns("H:J").ClearContents
    .Range("H1:J1") = Array("Invoice", "Output", "Mismatch")

    arr = .Range("A1").CurrentRegion
  
    Set d = CreateObject("Scripting.Dictionary")
    For i = 2 To UBound(arr)
        sKey = arr(i, 1)
        d(sKey) = Format(CDate(Application.Max(CDate(Left(arr(i, 2), 10)), CDate(Right(arr(i, 2), 10)))), "dd/mm/yyyy")  
    Next
  
    k = d.keys
    t = d.items

    .Range("H2").Resize(d.Count, 1) = Application.Transpose(k)
    .Range("I2").Resize(d.Count, 1) = Application.Transpose(t)

    Set d = Nothing
End With

End Sub