vba - how to use substitute for date conversion

Mallesh23

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



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
1Transaction Date
209/04/20
309/04/20
409/06/20
501/02/18
601/02/18
701/02/18
801/03/18
901/03/18
Sheet1



Thanks
mg
 

Attachments

  • Snapdate Date Column.PNG
    Snapdate Date Column.PNG
    5.9 KB · Views: 0

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,631
Office Version
  1. 365
Platform
  1. Windows
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))
 

Mallesh23

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

Mallesh23

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


Thanks
mg
 

Watch MrExcel Video

Forum statistics

Threads
1,119,228
Messages
5,576,856
Members
412,750
Latest member
sammurraybooks
Top