Thread: Trouble sorting dates (with new attachment)

Of course, it was silly of me not have attached the Excel sheet. Sorry.
Thanks for trying to help.
ACA

I have Excel 2007, on Windows 7, and, thogh not a new user, I have trouble handling dates: sorting them, but before that, getting a good date format. See example in attached file, please:

I cannot sort column A by date (A-Z); I keep getting the result you see there: the last three are out of place. Notice that
- The formula =DATENUMBER() works for some of the cells correctly sorted, but gives an error for others of these, and for all of the wrongly sorted ones (see column B ; the language is Spanish).
- The cells in column A, when selected, appear on the formula bar as shown in column C: some with “/”, which cannot be changed to “-”.
- Something may be wrong with the way the dates read by the program (the document was originally in an earlier version of Excel, but otherwise it runs well on 2007).
- I’ve tried everything I know in the Regional Configuration for my formats (Spain).
- I’ve tried everything in the Cell Format options of Excel, trying several date formats, number format, text format, general format.

Thanks for any help.
A. Calle

Have you tried running =DateValue() against the questionable dates. I suspect that they may be texts. It would be easier for us to evaluate if you posted the spreadsheet in an excel format versus the word document. Unable to really see what the issue is.

Alan

it would be better if you posted an excel example. but you can probably fix the dates using text to columns

the problem is there is a code 160 space before some of them
use find replace
in find hold down the alt key and type 0160 using the NUMBERPAD not the top row
leave replace blank
replace all
after that use text to columns to fix the text ones
select column
data/text to columns
delimited
next
next
choose date from column data format
choose DMY (well thats how it appears to me in my settings you may be MDY)
click finish