Sorting Dates in Excel

[George1111]

It seems that in Australia (date format dd/mm/yyyy) Excel cannot easily sort a sheet based on a column containing a date)

In fact, even if your REGIONAL SETTINGS are set to English Australia, Excel does not offer a standard date format of dd/mm/yyyy.

Data was imported into the column, already formatted as 31/03/2001 for example.

Under "Custom" there is a format dd/mm/yyyy but excel does not realise this is a date because it doesn't do any more than what a text format does - Ie, It SORTS AS THOUGH THE FIELD IS A TEXT FIELD, NOT A DATE FIELD

So my problem is - How do you sort a column with this date format ??

Did Microsoft just forget this format ?

Or am I just missing something so basic, it must be staring me in the face?

I've tried for a few hours now - I give up !

Thanks again Microsoft for the wonderful help in your help files - full of nothing as usual.

Does anyone know the answer

Appreciate your help

 

[xlbo]

Actually, I'm willing to bet that your "dates" are not dates at all but text. Always remember that formatting DOES NOT CHANGE THE DATA - all it does is apply a mask. Therefore when you FORMAT the dates as dd/mm/yyyy, you are not changing the fact that they are still text

To convert to date

enter a 1 in a blank cell
copy it
select all your "dates"
Edit>Pastespecial>
tick values and multiply
Format all the cells as dd/mm/yyyy again and then you should be able to sort normally

BTW - excel DOES recognise dd/mm/yyyy as a valid date - the only issue is that your dates have been imported as text - check the import process - If you are using the wizard to import the text file, there will be an option to set the data type of a field - this can be set to dd/mm/yyyy and should resolve the import issue

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[George1111]

You're right about the dates being text.
The sheet was created by a generic program under VB automatically.
I therefore don't know if a column is going to be a date or not.

Your suggestion works, but if you highlight the column heading rather than every row affected, I get 65565 resulting entries.

Many thanks for your interest

George

 

[xlbo]

Your suggestion works, but if you highlight the column heading rather than every row affected, I get 65565 resulting entries

sorry - I don't understand......is there a problem here ???
There are 65536 rows in excel so I am not at all surprised that if you select the entire column you get 65535 "resulting entries" - BTW what do you mean by "resulting entries" ??
IMHO, NEVER select an entire column - you are just giving yourself memory overheads and potential file size problems as if you format an entire column, excel thinks that all 65536 rows are being used and therefore keeps space available in memory and therefore bloats file size

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[PepeLeMokko]

Another trick to distinguish real dates from text is that by default text is left aligned and dates, as numbers , are right aligned

 

[xlbo]

.....unless the column is autofitted ;-)

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244