Best way to convert text date with VBA

[tomleitch]

Hi all,

I have a date that is being imported into my spreadsheet and comes as a long text that I want to convert to dd/mm/yyyy

An example of how it is imported is this "Monday 3rd Jun, 2:10:40pm"

What I want to do is get rid of the time and convert to just dd/mm/yyyy.

Is there a way to do this?


Thanks in advance,
Tom

 

[tomleitch]

I've also just noticed that the original date doesn't contain year data.... is it possible to change this data into just dd/mm data for now? Thanks, Tom

 

[mse330]

I am sure this is not the "best way" to do it but I think it will do what you need

Sub ChangeDate()

Dim dDate As String, dDate2 As String
dDate = "Monday 3rd Jun, 2:10:40pm"
dDate = Mid(Split(dDate, ",")(0), InStr(Split(dDate, ",")(0), " ") + 1)

For x = 1 To Len(Split(dDate, " ")(0))
    If Mid(dDate, x, 1) Like "*[0-9]*" Then dDate2 = dDate2 & Mid(dDate, x, 1)
Next x

Debug.Print Format(dDate2 & "-" & Split(dDate, " ")(1), "dd\/mm")

End Sub

 

[tomleitch]

Thanks for that, much appreciated

 

[mse330]

Glad to help & thanks for the feedback