date & Time string text as date format

[Jaz05]

Hi Guru's,
I've tried today at work to fix this and now I'm now brain dead, so I'm asking for help.
When I download a report from a supplier's website they have the date & time as text in a colum (1,000 + rows)
Text example: Jan 11 2013 15:30 or Oct 2 2012 8:50

I need these to be date.time format:
Date example: 11/01/2013 3:30:00 PM or 2/10/2012 8:50:00 AM

I'm running a VBA over the report for formating other columns, so I'm ok with either a VBA answer or an Excel Formula answer.

 

[MickG]

Try this:-
Dates in column "A"

[COLOR="Navy"]Sub[/COLOR] MG22Apr04
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dt
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Dt = Split(Dn, " ")
    [COLOR="Navy"]If[/COLOR] UBound(Dt) = 3 [COLOR="Navy"]Then[/COLOR]
        Dn.NumberFormat = "dd/mm/yyyy hh:mm:ss AM/PM"
        Dn = Dt(1) & " " & Dt(0) & " " & Dt(2) & " " & Dt(3) 
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Jaz05]

MickG...you are a gun.
No wonder Mr Excel.com is the place to go.

Thank you for prompt response. Works like a charm

 

[Jaz05]

MickG...you are a gun.
No wonder Mr Excel.com is the place to go.

Thank you for prompt response. Works like a charm