vba help - find max date

[Mallesh23]

Hi Team,

Need vba help to split and find max date.

Column A is input Column , expected outputis Column B.

Book1
	A	B
1	Date	Max Date
2	30-01-2020/02-09-2019/16-09-2019	30/01/2020
3	04-09-2019/18-09-2019/14-10-2019/23-01-2020	23/01/2020
4	18-09-2019/30-09-2019/05-11-2019/23-01-2020	23/01/2020
5	16-09-2019/25-09-2019/04-11-2019	04/11/2019
6	01-10-2019/23-01-2020/09-10-2019/04-11-2019	23/01/2020
7	01-10-2019/28-10-2019/30-01-2020/11-11-2019	30/01/2020
8	03-02-2020/01-10-2019/30-01-2020	03/02/2020
Sheet1

Thanks
mg

 

[Peter_SSs]

Do you really need vba? Could you you use a formula like this?

If not, what is the maximum number of dates you would expect to have in a cell?

Mallesh23_3.xlsm
	A	B
1	Date	Max Date
2	30-01-2020/02-09-2019/16-09-2019	30/01/2020
3	04-09-2019/18-09-2019/14-10-2019/23-01-2020	23/01/2020
4	18-09-2019/30-09-2019/05-11-2019/23-01-2020	23/01/2020
5	16-09-2019/25-09-2019/04-11-2019	04/11/2019
6	01-10-2019/23-01-2020/09-10-2019/04-11-2019	23/01/2020
7	01-10-2019/28-10-2019/30-01-2020/11-11-2019	30/01/2020
8	03-02-2020/01-10-2019/30-01-2020	03/02/2020
Max Date

Cell Formulas
Range	Formula
B2:B8	B2	=MAX(MID(A2&REPT("/01-01-1901",5),{1,12,23,34,45,56},10)+0)

 

[Mallesh23]

Hi Peter,

Thanks for your help, it is giving correct output.

But I need help in vba as my input data is in different workbook.



Thanks
mg

 

[Peter_SSs]

But I need help in vba

Sub Max_Date()
  With Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = "=MAX(MID(RC[-1]&REPT(""/01-01-1901"",5),{1,12,23,34,45,56},10)+0)"
    .Value = .Value
  End With
End Sub

 

[Mallesh23]

Hi Peter,

Thanks once again for your help , I have data of more than 1 lakh rows.

There are also blanks and dash in cells, macro to skip those cells.

any alternate solution .... via loop. array/ dictionary.... speed is concerned.


I am thinking split the data with "/" seperator, find max date out of it.



Thanks
mg

 

[Dossfm0q]

test.xlsx
	A	B	C	D
1	Date	Max Date	Max Date	Your,s
2	01-30-2020/02-09-2019/09-16-2019	01/30/2020	1/30/2020	1/30/2020
3	09-04-2019/09-18-2019/10-14-2019/01-23-2020	01/23/2020	1/23/2020	1/23/2020
4	09-18-2019/09-30-2019/11-05-2019/01-23-2020	01/23/2020	1/23/2020	1/23/2020
5	09-16-2019/09-25-2019/11-04-2019	11/04/2019	11/4/2019	11/4/2019
6	10-01-2019/01-23-2020/10-09-2019/11-04-2019	01/23/2020	1/23/2020	1/23/2020
7	10-01-2019/10-28-2019/01-30-2020/11-11-2019	01/30/2020	1/30/2020	1/30/2020
8	02-03-2020/10-01-2019/01-30-2020	02/03/2020	2/3/2020	2/3/2020
Sheet2

Cell Formulas
Range	Formula
B2:B8	B2	=TEXT(MAX(1*MID("/"&A2&"/",FIND("|",SUBSTITUTE("/"&A2&"/","/","|",TRANSPOSE(ROW(INDIRECT("1:"&LEN("/"&A2&"/")-LEN(SUBSTITUTE("/"&A2&"/","/",""))-1)))),TRANSPOSE(ROW(INDIRECT("1:"&LEN("/"&A2&"/")-LEN(SUBSTITUTE("/"&A2&"/","/",""))-1))))+1,10)),"mm/dd/yyyy")
C2:C8	C2	=LARGE(1*MID("/"&A2&"/",FIND("|",SUBSTITUTE("/"&A2&"/","/","|",TRANSPOSE(ROW(INDIRECT("1:"&LEN("/"&A2&"/")-LEN(SUBSTITUTE("/"&A2&"/","/",""))-1)))),TRANSPOSE(ROW(INDIRECT("1:"&LEN("/"&A2&"/")-LEN(SUBSTITUTE("/"&A2&"/","/",""))-1))))+1,10),1)
Press CTRL+SHIFT+ENTER to enter array formulas.

As my system:

test.xlsx
	A	B
10	Chang to >>>>>>>>>>>	My System Date
11	Date	Date
12	30-01-2020/02-09-2019/16-09-2019	01-30-2020/02-09-2019/09-16-2019
13	04-09-2019/18-09-2019/14-10-2019/23-01-2020	09-04-2019/09-18-2019/10-14-2019/01-23-2020
14	18-09-2019/30-09-2019/05-11-2019/23-01-2020	09-18-2019/09-30-2019/11-05-2019/01-23-2020
15	16-09-2019/25-09-2019/04-11-2019	09-16-2019/09-25-2019/11-04-2019
16	01-10-2019/23-01-2020/09-10-2019/04-11-2019	10-01-2019/01-23-2020/10-09-2019/11-04-2019
17	01-10-2019/28-10-2019/30-01-2020/11-11-2019	10-01-2019/10-28-2019/01-30-2020/11-11-2019
18	03-02-2020/01-10-2019/30-01-2020	02-03-2020/10-01-2019/01-30-2020
Sheet2

 

[Dossfm0q]

test.xlsx
	A	B
1	Date	Max Date
2	30-01-2020/02-09-2019/16-09-2019	30/01/2020
3	04-09-2019/18-09-2019/14-10-2019/23-01-2020	23/01/2020
4	18-09-2019/30-09-2019/05-11-2019/23-01-2020	23/01/2020
5	16-09-2019/25-09-2019/04-11-2019	04/11/2019
6	01-10-2019/23-01-2020/09-10-2019/04-11-2019	23/01/2020
7	01-10-2019/28-10-2019/30-01-2020/11-11-2019	30/01/2020
8	03-02-2020/01-10-2019/30-01-2020	03/02/2020
Sheet2

Cell Formulas
Range	Formula
B2:B8	B2	=TEXT(MAX(1*MID("/"&A2&"/",FIND("|",SUBSTITUTE("/"&A2&"/","/","|",TRANSPOSE(ROW(INDIRECT("1:"&LEN("/"&A2&"/")-LEN(SUBSTITUTE("/"&A2&"/","/",""))-1)))),TRANSPOSE(ROW(INDIRECT("1:"&LEN("/"&A2&"/")-LEN(SUBSTITUTE("/"&A2&"/","/",""))-1))))+1,10)),"dd/mm/yyyy")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Mallesh23]

Hi DossFM0Q,

thanks for your help, I need help in vba can you assist.
using loop or split function something like that, Because data is in different workbook.


Thanks
mg

​

 

[Peter_SSs]

There are also blanks and dash in cells, macro to skip those cells.

So (considering you have 700+ posts) why not include such cells is your sample data and expected results?

 

[Mallesh23]

Hi Peter,

Sorry my mistakes for not giving correct requirement, next time I will provide details.

Below is a Data with Column A is input Data, and Column B is Expected Output.

Book2
	A	B
1	Date	Max Date
2	30-01-2020/02-09-2019/16-09-2019	30/01/2020
3	04-09-2019/18-09-2019/14-10-2019/23-01-2020	23/01/2020
4	18-09-2019/30-09-2019/05-11-2019/23-01-2020	23/01/2020
5	-
6	01-10-2019/23-01-2020/09-10-2019/04-11-2019	23/01/2020
7	01-10-2019/28-10-2019/30-01-2020/11-11-2019	30/01/2020
8
9	03-02-2020/01-10-2019/30-01-2020	03/02/2020
10	16-09-2019/25-09-2019/04-11-2019	04/11/2019
Sheet2

Cell Formulas
Range	Formula
B9:B10,B6:B7,B2:B4	B2	=MAX(MID(A2&REPT("/01-01-1901",5),{1,12,23,34,45,56},10)+0)

Below is my attempted code,

Sub test()

    Dim ar As Variant
    Dim i As Long
    Dim maxdate As Date

    For i = 2 To 10
        If Cells(i, 1).Value <> "" And Len(Cells(i, 1).Value) > 5 Then
            ar = Split(Cells(i, 1).Value, "/")
                    MsgBox UBound(ar)
            'Compare and find max date
                maxdate = ar(0) Or ar(1) Or ar(3)
                Cells(i, 2).Value = maxdate
        End If
   
    Next i

End Sub

Thanks
mg