Convert text date format to date format

Question

I have a date as text in this format - "22nd July 2016". How do I convert it into date format using Excel-VBA.

Thanks in advance.

[Here.](http://www.google.com/search?q=vba+convert+text+to+date) — vacip, Jul 28 '16 at 11:20
21st July 2016, 3rd August 2016.. like this I will get the data. — Prem, Jul 28 '16 at 12:12

Gary's Student · Answer 1 · 2016-07-28T11:50:03.907

4

One way is to just strip-off the ordinal indicator:

Public Function dateOR(s As String) As Date
    ary = Split(s, " ")
    ary(0) = Left(ary(0), Len(ary(0)) - 2)
    dateOR = DateValue(Join(ary, " "))
End Function

and without VBA you can use the worksheet formula:

=DATEVALUE(SUBSTITUTE(A1,MID(A1,FIND(" ",A1)-2,2),""))

edited Jul 28 '16 at 11:50

answered Jul 28 '16 at 11:41

Gary's Student

95,722
10
59
99

score 0 · Accepted Answer · answered Jul 28 '16 at 11:47

Sub datestuff()
    myDate = "22nd July 2016"
    myDateArray = Split(myDate, " ")
    myDateArray(0) = Trim(Replace(Replace(Replace(Replace(myDateArray(0), "st", ""), "nd", ""), "rd", ""), "th", ""))
    myDate = DateValue(Join(myDateArray, " "))
    Debug.Print myDate ' outputs '22/07/2016'
End Sub

Seems to me the main issue is the day part; handle that and the rest will be simple enough?

Convert text date format to date format

2 Answers2

Linked