Sorting DD/MM/YYYY into DD/MM

[Little Lou]

Hi there.

I have around 1900 dates that I need to sort into order but I need the data to be displayed as DD/MM in chronological order eg 01/01, 02/01 etc.

At the moment the dates are DD/MM/YYYY in one column. Have tried sorting them into date order but have found that they're sorted by YYYY.

Is there a way of sorting them DD/MM instead?????

[contaminated]

Assuming all your 1900 dates stored in column A, eg A1:A2000
In B1 try
Descending order

Please Login or Register  to view this content.

Accending order

Please Login or Register  to view this content.

I'd rather to use formula to do that. If u want accending order SMALL(), LARGE() if descending.

[martindwilson]

not sure what you mean
if you sort
25/10/08
25/10/07
24/10/08
24/10/07
they get sorted into
24/10/07
25/10/07
24/10/08
25/10/08
which is chronological order!
how ever if you want in col A
24/10/07
24/10/08
25/10/07
25/10/08
then first sort by date then in a spare column put
=DATE(1,MONTH(A1),DAY(A1))
drag down and use that col to sort by

[Little Lou]

Thank you Martin that's been a great help

'Chronological' might not be the right word????

I wanted all the 1st Jan's to be together, followed by the 2nd Jan's and so on.

The formula you quoted worked, after filling the column with the formula I sorted Column B and got the result I needed.

The only problem I have is that some of the dates are pre 1900 so I'm getting a #VALUE! error message for these dates - are you able to advise on these???????

[martindwilson]

thats because dates before 1900 arent recognised as dates try
=IF(ISTEXT(A1),DATE(1,MID(A1,4,2),LEFT(A1,2)),DATE(1,MONTH(A1),DAY(A1)))

[Little Lou]

Thank you, that did the trick !