Cells contaning Fri 16 Jul 10 and similar as text

[rundwald]

I have a s/sheet ( Excel2007), whose source data was taken from a link. The date column is in the format as per title. In that form it will not sort itself or associated columns in to date order. Has anyone a way to make the data in the column sort itself as I want?

Also with the example given, how with text to column function does one make just two columns, one with just the weekday and the other with day, month, year?

 

[clunkfish]

I only know a somewhat clunky way of doing this (not surprising, given my name ):

1. Highlight the column containing the date string that you want to fix
2. Go to Editing > Find and Select > Replace... then select the Replace tab
3. In the "Find what:" box type Mon followed by a space
4. Leave the "Replace with:" box empty
5. Click on "Replace All"
6. Repeat this for Tue , Wed, Thu , Fri , Sat, and Sun (each one followed by a space)
7. Now all your dates will have changed from Fri 16 Jul 10 format to 16-Jul-10 format and you can sort them by date

To put the right days of the week in another column:

1. In the first cell where you want the day of the week, type =WEEKDAY(, then highlight the cell with the date, then close the bracket and hit Enter
2. That will put a number between 1 (which is Sunday) and 7 (Saturday) in the cell
3. Drag down to populate all the cells you need days of the week for
4. Use Find and Replace again on that column, replacing 1 with Sunday, 2 with Monday and so on

That will achieve your aim, though as I say it is not exactly elegant...

 

[rundwald]

Thanks.

I was thinking along the same lines as a last resort, but did not know that removal of the weekday could reformat the rest of the cell into a date form.

@all: is there a way by which text to columns delimited by a space could separate weekday in to one column and day, month, year in to one other ot the usual three?

Further edit

Extracting the weekday certainly allows the column containing the dd mm yy dates to be sorted, but no matter what date format is selected , what one sees on screen remains the same. Moreover if one needs to use the Weekday formula the #Value warning is returned with info that the year string has only two digits.