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
rundwald

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
clunkfish

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
rundwald

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
Thread Forum
Character length in a cell or cells
Hi guys, just wanted to ask if any of you can help me out in excel. Basically what I wanna do is, if I copied a text or a sentence and I would want to paste it on a cell, i'd like the cell to remove any characters if it is over than 40 excluding spaces. Any kind of help/advice would be...
Microsoft Office
Format cells in excel 2007
I have a spreadsheet made in excel 2007. In one of the cells is a number with three zeros in front of it and one zero at the end. However it always drops the zeros and shows only the other numbers. I select the cell and click on "format cells", but there is nothing I see that will show all the...
Microsoft Office
Burger King admits..... burgers and Whoppers contaning horse meat
Mail Online
Chillout Room
Locking certain cells from updating
All, I created a five year pro forma financial model for my business. The spreadsheet makes extensive use of worksheet formulas and macros. I also added a sheet to track actuals (numbers extracted, by hand, from QuickBooks monthly). Certain formulas in the spreadsheet are based on values...
Microsoft Office
How to compile a text file or similar regarding files?.
I have so many films and music, how do i compile a list of them into a text file or some other format for catalogue use? , i hate using catalogue software.
General Discussion
Excel un password cells
Hi I have a excel sheet that some cells are password protected i have no idea what the password is any ideas how i can find the password or any software that will find it, so i can change the cell contents. Thanks
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 14:10.

Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App