Sorting Dates in Excel.

[simonstevens35]

Hi,
I am trying to sort out some dates in excel 2002. The cells are formatted as dd.mm.yy.

Its kind of hard to explain but:When i sort the dates, they appear as 01.04.03
01.08.03
01.05.03
02.04.03
02.10.03

However i need them to end up like:
01.04.03
02.04.03
01.05.03
01.08.03
02.10.03
So instead of Sorting just the day, i need excel to sort the month and year as well. How can this be achieved?
Thanks in advance,
Simonstevens35

 

[01Patrik]

It sounds to me that you aren't using real dates (excels datevalues) but just numbers with dots between (formated as text perhaps?).

If so, change the format and re-sort.

// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.

 

[simonstevens35]

Im reformatting the colum to all the various date formats and it still wont sort properly. When i format a cell, the entry doesn't change appearance despite a different format. Is this normal?

thanks

 

[mbarron]

As Parick stated you probably aren't using real dates, but text representing dates. You would either need to re-enter the data as true dates, and the sort or create a dummy coulmns that rearranges the day, month and year parts of your field - then sort on your dummy column. Merely changing the format of a text field to a date format won't change the actual information stored in the cell. It is still text.

Method two:
Create a dummy column using:
=mid(A1,4,2)&left(A1,2)&right(A1,2)
Sort on this new column

Mike

 

[jdhilljr]

Use this formula in a dummy row.

=DATE(CONCATENATE(20,RIGHT(A1,2)),LEFT(A1,IF(LEN(A1)=6,2,1)),LEFT(RIGHT(A1,4),2))

Highlight the column that has the date formatted as 01.01.04 and only that column.

Then select 'EDIT' 'REPLACE'
Find '.'
Replace leave blank
Click 'REPLACE ALL'

Copy and Paste Special the Value only in the cell and format as date.

Jim

 

[01Patrik]

Or simply use Data | text-to-columns - and choose "date" format for the column.

// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.