Sorting By Date

[fitkhan]

Hi, We use a different date format that is dd/mm/yyyy so when i try to sort the column A to Z the results are not what i want.

For example the below dates are sorted A to Z

7/10/1991
8/9/1991
9/5/1991
3/6/1992
3/10/1992
8/11/1992
9/9/1992
2/2/1993
9/1/1993
9/3/1993

I want results like these

9/5/1991
8/9/1991
7/10/1991
3/6/1992
9/9/1992
3/10/1992
8/11/1992
9/1/1993
2/2/1993
9/3/1993

Thanks a lot

[shukla.ankur281190]

Select the data then press Alt->S->S you will get a sort window change the order reference "oldest to newest" then click on OK.

[Aardigspook]

Press Ctrl-F1 to open the 'Format Cells' window and check what your dates are formatted as. It appears that they're formatted using an American format. Change the format to a UK/International format (either select from the list or go to 'Custom' and type d/mm/yyyy. Dates should then sort correctly.
One 'gotcha' to be aware of - all the dates you give above could be either d/mm or mm/d, as there aren't any numbers >12. A date which looks like 9/2/1992 but is in American format will change to 2/9/1992 when you change the format (i.e. Excel will keep it as 2nd Sep). That's because Excel stores dates internally as numbers ('1' = 1 Jan 1900) - what you see is just display formatting. So if you want 9/2/1992 to mean 9th Feb, you may need to copy all the dates to Notepad (or similar), change the regional/date settings on your computer/Excel, then paste the numbers back in again.
I hope that all makes sense!

[fitkhan]

Thanks alot Aardigspook & shukla.ankur281190
Problem solved now.....
I changed the display date format and now everything is working great.
Thanks a lot for your help

[Aardigspook]

You're welcome.

Edit: and thanks for the rep.