vba - how to use substitute for date conversion

[Mallesh23]

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.

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

 

[jasonb75]

The Substitute function creates text so that will never work, the easiest way is with

=H2+0

as long as the dates are in the same format as your regional settings, converting mm/dd/yyyy dates to dd/mm/yyyy (for example) is better done with text to columns.

If you just want the max date then try =MAX(INDEX(H2:H9+0,0))

 

[Peter_SSs]

its working, how to use substitute function to achieve the same result.

Why, if you already have a working solution?

 

[Mallesh23]

Hi Jason,

Thanks for your help, this formula is giving correct result.
Can you help me how to store max date into vba variable. below formula
=MAX(INDEX(H2:H9+0,0))


Thanks
mg

 

[jasonb75]

Can you help me how to store max date into vba variable.

By using your existing code instead of asking questions that have already been answered.

 

[Mallesh23]

Hi Jason,


Thanks for your help your previous solution has worked, I was trying to find any other solution are there.
Just for learning purpose. thanks


Thanks
mg