Hi Team,
I am using replace option to convert string date to Date format.
its working, how to use substitute function to achieve the same result.
attached are Column snapshot.
Thanks
mg
I am using replace option to convert string date to Date format.
its working, how to use substitute function to achieve the same result.
attached are Column snapshot.
VBA Code:
Sub ConvertToDate()
Dim dt As Date
Dim rng As Range
Set rng = Range("H2", Range("H" & Rows.Count).End(xlUp))
With rng
.Replace What:="/", Replacement:="/", LookAt:=xlPart
.NumberFormat = "DD/MM/YYYY"
End With
'rng.TextToColumns Destination:=Range("h2")
dt = CDate(Application.Max(rng))
MsgBox dt
End Sub
Book2 | |||
---|---|---|---|
H | |||
1 | Transaction Date | ||
2 | 09/04/20 | ||
3 | 09/04/20 | ||
4 | 09/06/20 | ||
5 | 01/02/18 | ||
6 | 01/02/18 | ||
7 | 01/02/18 | ||
8 | 01/03/18 | ||
9 | 01/03/18 | ||
Sheet1 |
Thanks
mg