vba help - date issue

[Mallesh23]

Hi Team,

I am splitting Column A Data, and updating it in Column B.
Column A data is in DD/MM/YYYY Format, while pasting values are getting pasted in mm/dd/yyyy format.

How to solve it.


Expected output is in Column D. ... Macro is giving Column B output.

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

'Below is the Code

Option Explicit
Sub test()

    Dim ar As Variant
    Dim i As Long
    Dim maxdate As Date
    Dim arrdate As Long
    Dim d As Long
    
    For i = 2 To 10
   arrdate = 0
        If Cells(i, 1).Value <> "" And Len(Cells(i, 1).Value) > 5 Then
            ar = Split(Cells(i, 1).Value, "/")
                For d = LBound(ar) To UBound(ar)
                If CDate(ar(d)) > arrdate Then
                    arrdate = CDate(ar(d))
                End If
                  
                Next
                 maxdate = arrdate
                Cells(i, 2).Value = Format(maxdate, "dd/mm/yyyy")
        End If
  
    Next i

end Sub

Thanks
mg

 

[Mallesh23]

Hi Fluff,

Used below code giving correct output. Millions of thanks for your help ! ?

Columns(2).NumberFormat = "m/d/yyyy"
'Columns(2).NumberFormat = "dd/mm/yyyy"


Thanks
mg

 

[Fluff]