Date Conversion Script

[lightningmtv]

Hello Everyone
Im working on an excel sheet where all the dates are formatted the following: "1st Jan 2010" the only problem is that excel does not see this as a date.
Is it possible with a script to change this too a for excel readable date format?
Dates are in row I, J, K and L

Thanks in advance.

 

[kevin9999]

Presumably you mean Columns I, J, K & L? No idea where your data starts - the following suggestion assumes it starts on row 2. Try it on a copy of your actual sheet.
In future, please look at XL2BB - Excel Range to BBCode to post a copy of your sheet (so we don't have to guess what it looks like )

Option Explicit
Sub OrdToCard()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")   '<~~ *** Change to actual sheet name ***
    
    With ws.Range("I2:L" & ws.Range("I:L").Find("*", , xlFormulas, , xlByRows, xlPrevious).Row)
        .Value2 = Evaluate("TEXT(DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" & .Address & ",""st "",""-""),""nd "",""-""),""rd "",""-""),""th "",""-"")),""d mmmm yyyy"")")
    End With
    
End Sub