Thread: data sorting dates

Can anybody help with a data sort query. I have a list of dates (obtained froma Sage) in format dd/mm/yyyy. I need to sort them in date order but NOT INCLUDING THE YEAR. I have tried formatting to remove year but it wont change. I have about 1000 dates on the list.

I do not understand the question .

If you want to remove the year then in what way will you sort.. suppose if there are two data with same month and date , then how will you sort it ?

However, i tried to copy the value of A1:A34- dateswith dd/mm/yyyy format to C1:C34 , then i used format and set it to dd/mm , then i sorted it ... it sorted it asc and desc..
maybe iam understanding it wrong, let me know

You will not be able to sort the data you have the way you would like to. You will first have to convert it to text, using:

=TEXT(A1,"mm/dd")

Copy that formula down, select past special > values, then sort. The above will allow you to sort by month then day, so if you want to sort by actual day (regardless of the month), then use "dd/mm", rather than "mm/dd".

Thanks for the advise, it worked, BUT, when I sort the dates they are sorting by month and all the 31st of the different months first followed by 1st of months etc etc.

I need the list to sort all the 1st Jan, followed by 2nd Jan through to 31st December. Can you help?

Many thanks
Carol

Hello Carol, it works OK for me. If you get a "sort warning" choose "sort numbers and numbers stored as text separately"

I have tried that but it still lumps all the dates together opposed to sorting in dates and month!

Don't know why that wouldn't work. Another option....

use this formula in a helper column, e.g. column B

=DATE(0,MONTH(A1),DAY(A1))

copied down

Select the data in both columns and sort by column B ascending

Daddylonglegs, YOU ARE A STAR. It worked
Many thanks saved me masses of time