Convert text date to date

Question

I currently have multiple records which are formatted like so:

      24th April 2014

However, I want it to be formatted like so:

      24/04/2014 or 24/04/14 (either way is fine, does not really matter)

I am using excel 2013 by the way.

Thanks for any feedback.

Answer 1

Select the cells you wish to convert and run macro ReDate

Sub ReDate()
    Dim r As Range
    For Each r In Selection
        ary = Split(r.Value, " ")
        r.Clear
        r.Value = DateSerial(ary(2), Monthh(CStr(ary(1))), Numbrs(CStr(ary(0))))
    Next r
End Sub

Public Function Monthh(s As String) As Long
    Dim mnth(1 To 12) As String
    mnth(1) = "January"
    mnth(2) = "February"
    mnth(3) = "March"
    mnth(4) = "April"
    mnth(5) = "May"
    mnth(6) = "June"
    mnth(7) = "July"
    mnth(8) = "August"
    mnth(9) = "September"
    mnth(10) = "October"
    mnth(11) = "November"
    mnth(12) = "December"
    Monthh = Application.WorksheetFunction.Match(s, mnth, 0)
End Function

Public Function Numbrs(sIn As String) As Long
    Dim L As Long, v As String, i As Long
    Dim CH As String
    L = Len(sIn)
    v = ""
    For i = 1 To L
        CH = Mid(sIn, i, 1)
        If CH Like "[0-9]" Then
            v = v & CH
        End If
    Next i
    Numbrs = CLng(v)
End Function

Answer 2

I think a DATEVALUE Function should be good for this problem.

=DATEVALUE(LEFT(A1,FIND(" ",A1)-3)&RIGHT(A1,LEN(A1)-FIND(" ",A1)+1))

Let's assume the date text is stored in column A. And we know the main problem is about the existence of "st" or "nd" or "th" or "rd". If they are not existed, Excel could automatically recognize this datevalue. So I try to create a text of the date with those "th" using the text function:

LEFT(A1,FIND(" ",A1)-3)&RIGHT(A1,LEN(A1)-FIND(" ",A1)+1)

and then use the DATEVALUE function to convert them into datevalue, and it's done I think.

Is it right? Hope this could help!

Answer 3

If you are able to remove the "th" from the date, Excel will be able to automatically format it. If you have a lot of data, a simple vba script will do the trick.