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.
'Below is the Code
Thanks
mg
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
VBA 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