Date format

[Guest]

I have a large spreadsheet in Excel. Dates were entered as "August 1, 2001".
When I go to sort the list, Excel sorts by month. I selected all of the
date cells, format, and it shows all date cells as Date formated. How can I
get Excel to sort by date (by 08/01/2001; not alpha by month)? Or, how can I
take the August 1, 2001 and convert into 08/01/2001?

Thanks for any help.

 

[Guest]

Sounds like your dates were entered as character strings/labels, not true
dates. Try entering a formula to calc the difference between a couple of
them. If the formula returns #VALUE, they're certainly labels and will need
to be converted. Sorry, I don't have a handy formula for that, though others
here may. A pretty common problem. I tried sorting a list with a few dates
formatted to appear as you describe, but they sort in natural date order.

I'm betting you have labels, not dates.

 

[Guest]

Yes that's true. Does anyone have a formula for this problem?
Thanks.

 

[Ron Rosenfeld]

Yes that's true. Does anyone have a formula for this problem?
Thanks.

If they are text and are all in the same format you posted, then

=DATEVALUE(A1) will convert.

The DATEVALUE function can be finicky, though. It will convert

August 1, 2001

but will give an error on:

August 1,2001

Post back with your results. If DATEVALUE doesn't work, there are other
solutions.
--ron

 

[Guest]

If they were all entered in the format you described, then enter 1 in a dummy
cell and copy it. Select the range of dates you want to change and select
paste special and multiply. This will change the text dates to a serial
number (8/1/2001 equalls 37104). Finaly format the cells as dates.