Best way to convert text date with VBA

tomleitch

Board Regular
Joined
Jan 3, 2012
Messages
189
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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
 
Upvote 0
I am sure this is not the "best way" to do it but I think it will do what you need

Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top