[excel] How to sort by dates excel?

This is the most ridiculous thing, but I'm having trouble sorting by date in excel. I have a column of dates that I put in for something, in the following format

14/6/2012
15/12/2012
16/2/2012
17/9/2012
17/10/2012
17/11/2012

And so on like that. However when I try and sort them, it only sorts based on the first number, as I've shown above. When I highlight the data, at the top of window in excel, it doesn't say general anymore it says date so I assume excel knows that they're dates. However if I go to sort and filter to try and choose a few different levels to sort on, when I try to sort by the date it says sort A to Z, which seems odd. Then when I copied and pasted the date column to a new worksheet to play around with it to try and figure out what was wrong, several of the entries turned into hashes, just ######, but only a handful of entries did that and all of them are grouped together.

Does anyone know what could be wrong? This is literally driving me insane, it should be the simplest thing.

If you dont want to format a separate column with you normal dates pasted to it -- do the following -- add a column to the extreme left of your data and reverve your date ie if the date you had already entered was for example 11.5.16 enter int he new lefthand column 160511 ( notice that there are numbers only and no full stops . When you now sort there will be no mix ups as you have encountered.i have used this method for over 30 years and it never lets me down. And as you have placed the date by year, month and day you neednt include that column if you want or need tu print out your complete list.

1. Select the whole column
2. Right click -> Format cells... -> Number -> Category: Date -> OK
3. Data -> Text to Columns -> select Delimited -> Next -> in your case selection of Delimiters doesn't matter -> Next -> select Date: DMY -> Finish

Now you should be able to sort by this column either Oldest to Newest or Newest to Oldest

Here are the steps to convert the entire column to date format values.

Add a column to the right of the date column. Right click the new column and select Format. Set the format to date.

Highlight the entire old date column and copy it. Highlight the top cell of the new column and select Paste Special, and only paste values.

You can then remove the old column.

For example 8/12/1976. Copy your date column. Highlight the copied column and click Data> Text to Columns> Delimited> Next. In the delimiters column check "Other" and input / and then click Next and Finish. You'll have 3 columns and the first column will be 1/8. Highlight it and click the comma in the Number section and it will give you the month as 8.00, so then reduce it by clicking the comma in Home/Numbers and you'll now have 8 in the first column, 18 in the second column and 1976 in the third column. In the first empty cell to the right use the concatenate function and leave out the year column. If your month is column A, day is column B and year is column C, it will look like this: =concatenate(A2,"/",B2) and hit enter. It will look like 8/18, however, when you click on the cell you'll see the concatenate formula. Highlight the cell(s), then copy and paste special values. Now you can sort by date. It's really quick when you get the hang of it.

The problem in here is ms excel not recognizing the things which we entered as date, though is appears as date in menu bar. Select cell and type "=istext(cell ad)" then you can see it "TRUE" hence still your date ms excel thinks as a text that is why it aligns to left automatically. So we should change the type of text. There are many types of changing methods. but I think this too easy and lets do it now.

SELECT THE CELLS -> Go DATA in menu bar -> Then select "Text to Columns" object -> Select "Delimited" in first window then click next -> remove all ticks in the second window and hit next button -> last window select the "Date" and select your prefer date format then hit the finish button.(Then dates look like Wednesday, March 14, 2012) Then you can using previously used formula use and check whether our date is recognized in excel. Then you can Sort as you want. Thank you

It's actually really easy. Highlight the DATE column and make sure that its set as date in Excel. Highlight everything you want to change, Then go to [DATA]>[SORT]>[COLUMN] and set sorting by date. Hope it helps.