How to sort by dates excel

Question

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

User · Answer

Select the whole column Right click -  Format cells    -  Number -  Category  Date -  OK 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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

The hashes are just because your column width is not enough to display the  number    About the sorting  you should review how you system region and language is configured  For the US region  Excel date input should be  5 17 2012  not  17 05 2012   this 17-may-12    Regards

User · Answer

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

User · Answer

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

[excel] How to sort by dates excel?

Examples related to excel