Windows 7 Forums
Welcome to Windows 7 Forums. Our forum is dedicated to helping you find support and solutions for any problems regarding your Windows 7 PC be it Dell, HP, Acer, Asus or a custom build. We also provide an extensive Windows 7 tutorial section that covers a wide range of tips and tricks.


Windows 7: Cells contaning Fri 16 Jul 10 and similar as text


25 Jul 2010   #1

Home Premium 64 bit
 
 
Cells contaning Fri 16 Jul 10 and similar as text

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?

My System SpecsSystem Spec
.

25 Jul 2010   #2

Windows 7 Enterprise x64
 
 

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...
My System SpecsSystem Spec
26 Jul 2010   #3

Home Premium 64 bit
 
 

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.
My System SpecsSystem Spec
.


Reply

 Cells contaning Fri 16 Jul 10 and similar as text




Thread Tools



Similar help and support threads for2: Cells contaning Fri 16 Jul 10 and similar as text
Thread Forum
Burger King admits..... burgers and Whoppers contaning horse meat Chillout Room
Excel: How do I make some cells remain the same when pulling it down? Microsoft Office
Locking certain cells from updating Microsoft Office
Solved How to compile a text file or similar regarding files?. General Discussion
Quickly fill blank cells in Excel. Microsoft Office
Delete empty cells in worksheet Microsoft Office
Excel un password cells Microsoft Office

Our Sites

Site Links

About Us

Find Us

Windows 7 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 7" and related materials are trademarks of Microsoft Corp.

Designer Media Ltd

All times are GMT -5. The time now is 07:44 AM.
Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App
  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33