VBA help - store dates in dictionary using dateserial

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
874
Office Version
  1. 2010
Platform
  1. Windows
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
ABCDEFGHIJ
1InvoiceDateLeft-10ExpectedInvoiceOutputMismatch
23804404707/12/20194380607/12/20193804404707/12/2019
33804404804/06/20194362009/12/20193804404804/06/2019
43804404905/06/20194362109/12/20193804404905/06/2019
53804405006/06/20194362209/12/20193804405006/06/2019
63804406507/06/20194362309/12/20193804406507/06/2019
73804408408/06/20194362409/12/20193804408408/06/2019
83840324509/06/20194362509/12/20193840324509/06/2019
93804405110/06/20194362609/12/20193804405110/06/2019
103804419811/06/20194362709/12/20193804419811/06/2019
113804405212/06/20194362809/12/20193804405212/06/2019
123804405313/06/20194362909/12/20193804405313/06/2019
13904569014/06/20194363009/12/2019904569014/06/2019
14904569115/06/20194363109/12/2019904569115/06/2019
15904570616/06/20194363209/12/2019904570616/06/2019
16904570717/06/20194363310/12/2019904570717/06/2019
171203165318/06/20194363410/12/20191203165318/06/2019
181203200219/06/20194363510/12/20191203200219/06/2019
191240108220/06/20194363610/12/20191240108220/06/2019
201203178410/12/20194380910/12/20191203178410/12/2019
211203198510/12/20194380910/12/20191203198510/12/2019
221203201810/12/20194380910/12/20191203201810/12/2019
231203198610/12/20194380910/12/20191203198610/12/2019
241203199910/12/20194380910/12/20191203199910/12/2019
254003102710/12/20194380910/12/20194003102710/12/2019
264003105210/12/20194380910/12/20194003105210/12/2019
274003105310/12/20194380910/12/20194003105310/12/2019
28210006044210/12/20194380910/12/20192.1E+0910/12/2019
29210006044310/12/20194380910/12/20192.1E+0910/12/2019
30602985010/12/20194380910/12/2019602985010/12/2019
31602987610/12/20194380910/12/2019602987610/12/2019
32602985110/12/20194380910/12/2019602985110/12/2019
333201967310/12/20194380910/12/20193201967310/12/2019
343201992210/12/20194380910/12/20193201992210/12/2019
351403572710/12/20194380910/12/20191403572710/12/2019
361403572811/12/20194381011/12/20191403572811/12/2019
37210006041711/12/20194381011/12/20192.1E+0911/12/2019
38210006041811/12/20194381011/12/20192.1E+0911/12/2019
391302609211/12/20194381011/12/20191302609211/12/2019
403804409411/12/20194381011/12/20193804409411/12/2019
413804409511/12/20194381011/12/20193804409511/12/2019
423804409611/12/20194381011/12/20193804409611/12/2019
433804409711/12/20194381011/12/20193804409711/12/2019
443804411311/12/20194381011/12/20193804411311/12/2019
453804409811/12/20194381011/12/20193804409811/12/2019
463804411411/12/20194381011/12/20193804411411/12/2019
473804409911/12/20194381011/12/20193804409911/12/2019
483804410111/12/20194381011/12/20193804410111/12/2019
491203224011/12/20194381011/12/20191203224011/12/2019
501203224912/12/20194381112/12/20191203224912/12/2019
51603000712/12/20194381112/12/2019603000712/12/2019
52603000812/12/20194381112/12/2019603000812/12/2019
53603000912/12/20194381112/12/2019603000912/12/2019
541302588312/12/20194381112/12/20191302588312/12/2019
553804402812/12/20194381112/12/20193804402812/12/2019
563303923512/12/20194381112/12/20193303923512/12/2019
57904597012/12/20194381112/12/2019904597012/12/2019
58904596912/12/20194381112/12/2019904596912/12/2019
59940343612/12/20194381112/12/2019940343612/12/2019
60800009212/12/20194381112/12/2019800009212/12/2019
61800009112/12/20194381112/12/2019800009112/12/2019
623202013912/12/20194381112/12/20193202013912/12/2019
631403600812/12/20194381112/12/20191403600812/12/2019
643804438812/12/20194381112/12/20193804438812/12/2019
653804438912/12/20194381112/12/20193804438912/12/2019
663840326312/12/20194381112/12/20193840326312/12/2019
67603015712/12/20194381112/12/2019603015712/12/2019
68603015612/12/20194381112/12/2019603015612/12/2019
69640144812/12/20194381112/12/2019640144812/12/2019
70700012912/12/20194381112/12/2019700012912/12/2019
71700013012/12/20194381112/12/2019700013012/12/2019
721302624912/12/20194381112/12/20191302624912/12/2019
733300010812/12/20194381112/12/20193300010812/12/2019
743300008912/12/20194381112/12/20193300008912/12/2019
751203230713/12/20194381213/12/20191203230713/12/2019
761240109813/12/20194381213/12/20191240109813/12/2019
773600002113/12/20194381213/12/20193600002113/12/2019
784003154713/12/20194381213/12/20194003154713/12/2019
792400003713/12/20194381213/12/20192400003713/12/2019
80210000006413/12/20194381213/12/20192.1E+0913/12/2019
81210000006313/12/20194381213/12/20192.1E+0913/12/2019
821403606213/12/20194381213/12/20191403606213/12/2019
831403606313/12/20194381213/12/20191403606313/12/2019
841403606413/12/20194381213/12/20191403606413/12/2019
85210006151013/12/20194381213/12/20192.1E+0913/12/2019
86210006150913/12/20194381213/12/20192.1E+0913/12/2019
87210006150813/12/20194381213/12/20192.1E+0913/12/2019
881302626213/12/20194381213/12/20191302626213/12/2019
893804446313/12/20194381213/12/20193804446313/12/2019
903804446113/12/20194381213/12/20193804446113/12/2019
913300015513/12/20194381213/12/20193300015513/12/2019
923804446213/12/20194381213/12/20193804446213/12/2019
933804446413/12/20194381213/12/20193804446413/12/2019
943300015413/12/20194381213/12/20193300015413/12/2019
95904617313/12/20194381213/12/2019904617313/12/2019
96904617413/12/20194381213/12/2019904617413/12/2019
97904617513/12/20194381213/12/2019904617513/12/2019
98940347313/12/20194381213/12/2019940347313/12/2019
99800034614/12/20194381314/12/2019800034614/12/2019
100800034714/12/20194381314/12/2019800034714/12/2019
101800034814/12/20194381314/12/2019800034814/12/2019
102210000010614/12/20194381314/12/20192.1E+0914/12/2019
103210000010514/12/20194381314/12/20192.1E+0914/12/2019
104904618714/12/20194381314/12/2019904618714/12/2019
105904618614/12/20194381314/12/2019904618614/12/2019
106904618514/12/20194381314/12/2019904618514/12/2019
1073303960314/12/20194381314/12/20193303960314/12/2019
108210006158914/12/20194381314/12/20192.1E+0914/12/2019
109210006158814/12/20194381314/12/20192.1E+0914/12/2019
110210006158714/12/20194381314/12/20192.1E+0914/12/2019
1113804450114/12/20194381314/12/20193804450114/12/2019
1123804450214/12/20194381314/12/20193804450214/12/2019
1133840328214/12/20194381314/12/20193840328214/12/2019
1143300018714/12/20194381314/12/20193300018714/12/2019
1153804450314/12/20194381314/12/20193804450314/12/2019
1163300018614/12/20194381314/12/20193300018614/12/2019
1173804438714/12/20194381314/12/20193804438714/12/2019
1183300003214/12/20194381314/12/20193300003214/12/2019
1193600008914/12/20194381314/12/20193600008914/12/2019
120700035114/12/20194381314/12/2019700035114/12/2019
1213840326216/12/20194381516/12/20193840326216/12/2019
1223804456316/12/20194381516/12/20193804456316/12/2019
1233804456416/12/20194381516/12/20193804456416/12/2019
1243804456516/12/20194381516/12/20193804456516/12/2019
1253300026216/12/20194381516/12/20193300026216/12/2019
1263300026102-07-2019/03-07-201903-07-201903/07/20193300026103/07/2019
1273300026007-05-2019/08-05-201908-05-201908/05/20193300026008/05/2019
1281203241707-05-2019 - 08-05-201908-05-201908/05/20191203241708/05/2019
1291203241907-10-2020 12-10-202012-10-202012/10/20201203241912/10/2020
Sheet1
Cell Formulas
RangeFormula
C2:C129C2=IF(LEN(B2)>5,RIGHT(B2,10),LEFT(B2,10))







VBA Code:
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Lian

New Member
Joined
Jan 26, 2021
Messages
34
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Actually, the right(colB,10) is always the larger date.
Here is a formula solution.

Book1
ABCDEFGHIJ
1invoiceDateLeft-10ExpectedInvoiceOutputMismatch
23804404707/12/20194380607/12/20193804404707/12/2019
33804404804/06/20194362009/12/20193804404804/06/2019
43804404905/06/20194362109/12/20193804404905/06/2019
53804405006/06/20194362209/12/20193804405006/06/2019
63804406507/06/20194362309/12/20193804406507/06/2019
73804408408/06/20194362409/12/20193804408408/06/2019
83840324509/06/20194362509/12/20193840324509/06/2019
93804405110/06/20194362609/12/20193804405110/06/2019
103804419811/06/20194362709/12/20193804419811/06/2019
113804405212/06/20194362809/12/20193804405212/06/2019
123804405313/06/20194362909/12/20193804405313/06/2019
13904569014/06/20194363009/12/2019904569014/06/2019
14904569115/06/20194363109/12/2019904569115/06/2019
15904570616/06/20194363209/12/2019904570616/06/2019
16904570717/06/20194363310/12/2019904570717/06/2019
171203165318/06/20194363410/12/20191203165318/06/2019
181203200219/06/20194363510/12/20191203200219/06/2019
191240108220/06/20194363610/12/20191240108220/06/2019
201203178410/12/20194380910/12/20191203178410/12/2019
211203198510/12/20194380910/12/20191203198510/12/2019
221203201810/12/20194380910/12/20191203201810/12/2019
231203198610/12/20194380910/12/20191203198610/12/2019
241203199910/12/20194380910/12/20191203199910/12/2019
254003102710/12/20194380910/12/20194003102710/12/2019
264003105210/12/20194380910/12/20194003105210/12/2019
274003105310/12/20194380910/12/20194003105310/12/2019
28210006044210/12/20194380910/12/2019210006044210/12/2019
29210006044310/12/20194380910/12/2019210006044310/12/2019
30602985010/12/20194380910/12/2019602985010/12/2019
31602987610/12/20194380910/12/2019602987610/12/2019
32602985110/12/20194380910/12/2019602985110/12/2019
333201967310/12/20194380910/12/20193201967310/12/2019
343201992210/12/20194380910/12/20193201992210/12/2019
351403572710/12/20194380910/12/20191403572710/12/2019
361403572811/12/20194381011/12/20191403572811/12/2019
37210006041711/12/20194381011/12/2019210006041711/12/2019
38210006041811/12/20194381011/12/2019210006041811/12/2019
391302609211/12/20194381011/12/20191302609211/12/2019
403804409411/12/20194381011/12/20193804409411/12/2019
413804409511/12/20194381011/12/20193804409511/12/2019
423804409611/12/20194381011/12/20193804409611/12/2019
433804409711/12/20194381011/12/20193804409711/12/2019
443804411311/12/20194381011/12/20193804411311/12/2019
453804409811/12/20194381011/12/20193804409811/12/2019
463804411411/12/20194381011/12/20193804411411/12/2019
473804409911/12/20194381011/12/20193804409911/12/2019
483804410111/12/20194381011/12/20193804410111/12/2019
491203224011/12/20194381011/12/20191203224011/12/2019
501203224912/12/20194381112/12/20191203224912/12/2019
51603000712/12/20194381112/12/2019603000712/12/2019
52603000812/12/20194381112/12/2019603000812/12/2019
53603000912/12/20194381112/12/2019603000912/12/2019
541302588312/12/20194381112/12/20191302588312/12/2019
553804402812/12/20194381112/12/20193804402812/12/2019
563303923512/12/20194381112/12/20193303923512/12/2019
57904597012/12/20194381112/12/2019904597012/12/2019
58904596912/12/20194381112/12/2019904596912/12/2019
59940343612/12/20194381112/12/2019940343612/12/2019
60800009212/12/20194381112/12/2019800009212/12/2019
61800009112/12/20194381112/12/2019800009112/12/2019
623202013912/12/20194381112/12/20193202013912/12/2019
631403600812/12/20194381112/12/20191403600812/12/2019
643804438812/12/20194381112/12/20193804438812/12/2019
653804438912/12/20194381112/12/20193804438912/12/2019
663840326312/12/20194381112/12/20193840326312/12/2019
67603015712/12/20194381112/12/2019603015712/12/2019
68603015612/12/20194381112/12/2019603015612/12/2019
69640144812/12/20194381112/12/2019640144812/12/2019
70700012912/12/20194381112/12/2019700012912/12/2019
71700013012/12/20194381112/12/2019700013012/12/2019
721302624912/12/20194381112/12/20191302624912/12/2019
733300010812/12/20194381112/12/20193300010812/12/2019
743300008912/12/20194381112/12/20193300008912/12/2019
751203230713/12/20194381213/12/20191203230713/12/2019
761240109813/12/20194381213/12/20191240109813/12/2019
773600002113/12/20194381213/12/20193600002113/12/2019
784003154713/12/20194381213/12/20194003154713/12/2019
792400003713/12/20194381213/12/20192400003713/12/2019
80210000006413/12/20194381213/12/2019210000006413/12/2019
81210000006313/12/20194381213/12/2019210000006313/12/2019
821403606213/12/20194381213/12/20191403606213/12/2019
831403606313/12/20194381213/12/20191403606313/12/2019
841403606413/12/20194381213/12/20191403606413/12/2019
85210006151013/12/20194381213/12/2019210006151013/12/2019
86210006150913/12/20194381213/12/2019210006150913/12/2019
87210006150813/12/20194381213/12/2019210006150813/12/2019
881302626213/12/20194381213/12/20191302626213/12/2019
893804446313/12/20194381213/12/20193804446313/12/2019
903804446113/12/20194381213/12/20193804446113/12/2019
913300015513/12/20194381213/12/20193300015513/12/2019
923804446213/12/20194381213/12/20193804446213/12/2019
933804446413/12/20194381213/12/20193804446413/12/2019
943300015413/12/20194381213/12/20193300015413/12/2019
95904617313/12/20194381213/12/2019904617313/12/2019
96904617413/12/20194381213/12/2019904617413/12/2019
97904617513/12/20194381213/12/2019904617513/12/2019
98940347313/12/20194381213/12/2019940347313/12/2019
99800034614/12/20194381314/12/2019800034614/12/2019
100800034714/12/20194381314/12/2019800034714/12/2019
101800034814/12/20194381314/12/2019800034814/12/2019
102210000010614/12/20194381314/12/2019210000010614/12/2019
103210000010514/12/20194381314/12/2019210000010514/12/2019
104904618714/12/20194381314/12/2019904618714/12/2019
105904618614/12/20194381314/12/2019904618614/12/2019
106904618514/12/20194381314/12/2019904618514/12/2019
1073303960314/12/20194381314/12/20193303960314/12/2019
108210006158914/12/20194381314/12/2019210006158914/12/2019
109210006158814/12/20194381314/12/2019210006158814/12/2019
110210006158714/12/20194381314/12/2019210006158714/12/2019
1113804450114/12/20194381314/12/20193804450114/12/2019
1123804450214/12/20194381314/12/20193804450214/12/2019
1133840328214/12/20194381314/12/20193840328214/12/2019
1143300018714/12/20194381314/12/20193300018714/12/2019
1153804450314/12/20194381314/12/20193804450314/12/2019
1163300018614/12/20194381314/12/20193300018614/12/2019
1173804438714/12/20194381314/12/20193804438714/12/2019
1183300003214/12/20194381314/12/20193300003214/12/2019
1193600008914/12/20194381314/12/20193600008914/12/2019
120700035114/12/20194381314/12/2019700035114/12/2019
1213840326216/12/20194381516/12/20193840326216/12/2019
1223804456316/12/20194381516/12/20193804456316/12/2019
1233804456416/12/20194381516/12/20193804456416/12/2019
1243804456516/12/20194381516/12/20193804456516/12/2019
1253300026216/12/20194381516/12/20193300026216/12/2019
1263300026102-07-2019/03-07-201903-07-201903/07/20193300026103/07/2019
1273300026007-05-2019/08-05-201908-05-201908/05/20193300026008/05/2019
1281203241707-05-2019 - 08-05-201908-05-201908/05/20191203241708/05/2019
1291203241907-10-2020 12-10-202012-10-202012/10/20201203241912/10/2020
Sheet3
Cell Formulas
RangeFormula
I2:I129I2=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

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 26, 2021
Messages
34
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Try this with dictionary and also choose the maxium date.

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,193
Messages
5,640,771
Members
417,166
Latest member
Funwayo

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
Top