EXCEL text to date format

[tav1035]

Is there a way to convert this text to a date. It gets imported into excel as text and is not able to use the format function in excel. I can't seem to find a formula to use.

Here is the date currently-
Wed Sep 21 00:00:00 PDT 2011

I would like it to be-
9/21/2011

Thanks,
tav

 

[SkipVought]

hi,

1. build a table of month abbreviations.

2. use the MID() function to return the month, day and year

3. use the DATE() function to use the year, month and day to return a DATE.

Build your solution one step at a time, from the inside out. You could even use a separate cell for each year, month, day value and then DATE().

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

to lookup the month abbreviation in the table you will build, you will need the MATCH() function, as MATCH will return the numeric month value.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tav1035]

This is a download from our front end maintenance software. Everytime the data changes we hit a link that exports the data to excel. We sort it and then we save it as an excel file. We have 20 different sites with 5 or so people at each site downloading the freshes data all the time. I usually just send out a macro solution for them to run after they download.
I really just need to be able to sort the column in asc order and remove the time stamp. Right now it won't sort.
I was thinking a formula if sept then 9, if oct then 10 or something like that.

Thanks,
tav

 

[SkipVought]

Function StripDate(s As String) As Date
    Dim a, mo As Integer
    
    a = Split(s, " ")
    
    Select Case a(1)
        Case "Jan": mo = 1
        Case "Feb": mo = 2
        Case "Mar": mo = 3
        Case "Apr": mo = 4
        Case "May": mo = 5
        Case "Jun": mo = 6
        Case "Jul": mo = 7
        Case "Aug": mo = 8
        Case "Sep": mo = 9
        Case "Oct": mo = 10
        Case "Nov": mo = 11
        Case "Dec": mo = 12
    End Select
    
    StripDate = DateSerial(a(5), mo, a(2))
End Function

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}