CarrieBoss said:
I got a spreadsheet emailed to me with a date stored in
a cell (not a date format - it is stored as text) in the
format e.g. Apr 02 2011. I need it stored/changed to the
format 2011 Apr 02 because I want to sort from the lowest
date to the highest. Is there an easy way to do this change
globally.
You can do it column-by-column. If you have many columns, you can record a
macro, make some changes, then use the macro to change the other columns.
(We can help you with that.)
First, select a vertical range of cells (or an entire column) and start the
Text To Columns operation. Exactly how depends on the version of Excel,
which you did not mention.
In the first dialog box, select "Delimited". Then click on Next.
In the second dialog box, select a non-existent delimiter -- probably Tab,
which is the default. Then click on Next.
In the final dialog box, select Date and MDY. Then click Finish.
That creates a numeric date instead of text. You can format it any way you
wish. For example, select the same cells, right-click and click on Format
Cells, then the Number tab. Click on Custom and enter the following in the
Type field: yyyy mmm dd.
That gives you the format you mention above.
You can now use the Sort operation to sort the associated columns in lowest
to highest by date.
If you need more information, we might need more specifics.
Alternatively, you can upload an example Excel file (devoid of any private
data) that demonstrates the problem to a file-sharing website and post the
"shared", "public" or "view-only" link (aka URL;
http://...) in a response
here. The following is a list of some free file-sharing websites; or use
your own.
Box.Net:
http://www.box.net/files
Windows Live Skydrive:
http://skydrive.live.com
MediaFire:
http://www.mediafire.com
FileFactory:
http://www.filefactory.com
FileSavr:
http://www.filesavr.com
RapidShare:
http://www.rapidshare.com