vba help - find max date

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
853
Office Version
  1. 2010
Platform
  1. Windows
Hi Team,

Need vba help to split and find max date.

Column A is input Column , expected outputis Column B.

Book1
AB
1DateMax Date
230-01-2020/02-09-2019/16-09-201930/01/2020
304-09-2019/18-09-2019/14-10-2019/23-01-202023/01/2020
418-09-2019/30-09-2019/05-11-2019/23-01-202023/01/2020
516-09-2019/25-09-2019/04-11-201904/11/2019
601-10-2019/23-01-2020/09-10-2019/04-11-201923/01/2020
701-10-2019/28-10-2019/30-01-2020/11-11-201930/01/2020
803-02-2020/01-10-2019/30-01-202003/02/2020
Sheet1



Thanks
mg
 

Some videos you may like

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.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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
AB
1DateMax Date
230-01-2020/02-09-2019/16-09-201930/01/2020
304-09-2019/18-09-2019/14-10-2019/23-01-202023/01/2020
418-09-2019/30-09-2019/05-11-2019/23-01-202023/01/2020
516-09-2019/25-09-2019/04-11-201904/11/2019
601-10-2019/23-01-2020/09-10-2019/04-11-201923/01/2020
701-10-2019/28-10-2019/30-01-2020/11-11-201930/01/2020
803-02-2020/01-10-2019/30-01-202003/02/2020
Max Date
Cell Formulas
RangeFormula
B2:B8B2=MAX(MID(A2&REPT("/01-01-1901",5),{1,12,23,34,45,56},10)+0)
 

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
853
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
But I need help in vba
VBA Code:
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

Well-known Member
Joined
Feb 4, 2009
Messages
853
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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
 
Last edited:

Dossfm0q

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows
test.xlsx
ABCD
1DateMax DateMax DateYour,s
201-30-2020/02-09-2019/09-16-201901/30/20201/30/20201/30/2020
309-04-2019/09-18-2019/10-14-2019/01-23-202001/23/20201/23/20201/23/2020
409-18-2019/09-30-2019/11-05-2019/01-23-202001/23/20201/23/20201/23/2020
509-16-2019/09-25-2019/11-04-201911/04/201911/4/201911/4/2019
610-01-2019/01-23-2020/10-09-2019/11-04-201901/23/20201/23/20201/23/2020
710-01-2019/10-28-2019/01-30-2020/11-11-201901/30/20201/30/20201/30/2020
802-03-2020/10-01-2019/01-30-202002/03/20202/3/20202/3/2020
Sheet2
Cell Formulas
RangeFormula
B2:B8B2=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:C8C2=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
AB
10Chang to >>>>>>>>>>>My System Date
11DateDate
1230-01-2020/02-09-2019/16-09-201901-30-2020/02-09-2019/09-16-2019
1304-09-2019/18-09-2019/14-10-2019/23-01-202009-04-2019/09-18-2019/10-14-2019/01-23-2020
1418-09-2019/30-09-2019/05-11-2019/23-01-202009-18-2019/09-30-2019/11-05-2019/01-23-2020
1516-09-2019/25-09-2019/04-11-201909-16-2019/09-25-2019/11-04-2019
1601-10-2019/23-01-2020/09-10-2019/04-11-201910-01-2019/01-23-2020/10-09-2019/11-04-2019
1701-10-2019/28-10-2019/30-01-2020/11-11-201910-01-2019/10-28-2019/01-30-2020/11-11-2019
1803-02-2020/01-10-2019/30-01-202002-03-2020/10-01-2019/01-30-2020
Sheet2
 
Last edited:

Dossfm0q

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

test.xlsx
AB
1DateMax Date
230-01-2020/02-09-2019/16-09-201930/01/2020
304-09-2019/18-09-2019/14-10-2019/23-01-202023/01/2020
418-09-2019/30-09-2019/05-11-2019/23-01-202023/01/2020
516-09-2019/25-09-2019/04-11-201904/11/2019
601-10-2019/23-01-2020/09-10-2019/04-11-201923/01/2020
701-10-2019/28-10-2019/30-01-2020/11-11-201930/01/2020
803-02-2020/01-10-2019/30-01-202003/02/2020
Sheet2
Cell Formulas
RangeFormula
B2:B8B2=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

Well-known Member
Joined
Feb 4, 2009
Messages
853
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 4, 2009
Messages
853
Office Version
  1. 2010
Platform
  1. Windows
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
AB
1DateMax Date
230-01-2020/02-09-2019/16-09-201930/01/2020
304-09-2019/18-09-2019/14-10-2019/23-01-202023/01/2020
418-09-2019/30-09-2019/05-11-2019/23-01-202023/01/2020
5-
601-10-2019/23-01-2020/09-10-2019/04-11-201923/01/2020
701-10-2019/28-10-2019/30-01-2020/11-11-201930/01/2020
8
903-02-2020/01-10-2019/30-01-202003/02/2020
1016-09-2019/25-09-2019/04-11-201904/11/2019
Sheet2
Cell Formulas
RangeFormula
B9:B10,B6:B7,B2:B4B2=MAX(MID(A2&REPT("/01-01-1901",5),{1,12,23,34,45,56},10)+0)



Below is my attempted code,

VBA 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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,566
Messages
5,625,539
Members
416,116
Latest member
Joemamasuka

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