format cells for date not working...please help

U

uptwospeed

I have imported a text document, everything worked. I have dates i
cells in a format like 23-5-2004. I need to change them to 5/23/2004.
go to format the cells to this date format and nothing happens. I hav
tried to format a single cell this way, and nothing. I have als
unchecked the protection box. It will kill me to do 5000 dates one b
one. Any help would be great. Thank you in advance
 
G

Gord Dibben

I'm guessing the 23-5-2004 is Text, not a real date.

Try selecting the dates and Data>Text to Columns>Next>Next "column data
format">Date>MDY>Finish.

You may have to format as mm/dd/yyyy

Gord Dibben Excel MVP
 
D

Domenic

Hi,

Try the following:

1) Select your dates
2) Data > Text to Columns
3) Next > Next
4) Select DMY for you your Date format
5) Click OK/Finish

Hope this helps!
 
U

uptwospeed

Thank you so much! IT WORKED.:) I do have one other question...
I need to sort by date but the year is not important. When I sort i
auto does it by year. How can I sort by month and day only and no
have the year affect the sort? Thanks again
 
D

Domenic

One way would be to use a helper column. So assuming that your dates
are in Column A, enter this formula in B1 and copy it down as far as you
need to:

=TEXT(A1,"mm/dd")

Then, sort based on Column B. Now you can get rid of your helper column.

Hope this helps!
 
Top