How to sort dates from Oldest to Newest in Excel

Question

I have code that brings data in a database to Excel   For it to be versatile the cells are formatted as  General   This worked   Now someone wants to sort dates  Oldest to Newest  but the only option that shows up is A to Z or Z to A   I followed all suggestions found in How to sort by dates excel    Going through all those steps I did see the proper sort appear  so I recreated the workbook to reproduce this but could not   Why doesn t formatting the column as a date display the  Oldest to Newest    What steps are required to get the sort options to display  Oldest to Newest    I press F2 and it does not work   I click on a cell in the original column that was general changed to date but it does not work   I click on the column header in the original column that was general changed to date but it does not work   I click on a cell in the new column that pasted values special but it does not work   I click on the column header in the new column that pasted values special but it does not work

User · Answer

After some frustration I tried the following which worked for me:

Convert cells to date format if not already done. Go to the Data tab and click sort. Click sort after choosing expand selection or continue with current selection. Sort by Date Sort on Values order Z to A for newest date first. Click OK. Choose "Sort anything that looks like a number, as a number".

=)

User · Answer

Saw this ages after posting  but if anyone has the same problem I had  my dates were all the format DD MM YYYY  SPSS output  even formatting them to date in Excel still only let me sort from A-Z so basically the 1st-31st irrelevant of month   Doing a find and replace of all     to     fixed this so that they are now in the format of DD MM YYYY  Not sure why it didn t work in the other format when the cell type counted as date

User · Answer

Here s how to sort unsorted dates   Drag down the column to select the dates you want to sort   Click Home tab   arrow under Sort  amp  Filter  and then click Sort Oldest to Newest  or Sort Newest to Oldest   NOTE   If the results aren t what you expected  the column might have dates that are stored as text instead of dates  Convert dates stored as text to dates

User · Answer

You need to convert all the values in the column to date in order to sort by date

User · Answer

Make sure you have no blank rows between the heading  e g   date   and the date values in the column below the heading   These rows may be hidden  so be sure to unhide them and delete them

User · Answer

Convert text to date format via the  Data  tab   Highlight the relevant section and then select from the top menu Data  Datat Tools   Text to Column  depending on your version    Choose the  Delimited  option   Toggle through the Delimiter options until the entry appears in the desired format  and select  Next    Under the Data format  select Date  DMY   Select  finish  and the issue should be resolved

User · Answer

None of the above worked for me  My dates were well-formatted and the date format was set properly   The key in my case was  my data has headers  checkbox  After I unchecked this option the ordering function just started to work fine   This function is available under the  Custom sort  option   ps  I can accept that the first some row was ordered in a wrong way because of this option but I have no clue about why at the middle of my range was ordered on the wrong way  Excel makes me crazy

User · Answer

In my case the format changing did not help  nothing changed in the date representation whatever cell format I was selecting  However  I found that the correct date entry for 18 11 1999 when F2 pressed was 18 11 1999 - whith spaces  no dots  When I performed  search-replace  and changed dots with space  the problem was solved

User · Answer

I had the same problem and tried all the suggestions above   My dates were formatted mm dd yyyy   In desperation I tried the following    Highlighted the entire column with the dates   Searched for 2017 Replace All with 2017 The problem was solved  for dates in 2017  repeat for other years     I hope this helps

User · Answer

Another possibility is a leading space before the date in the cells - this usually aligns the date on the left so once you know it s easy to spot  Removing the spaces moves the date to the right and sorting works correctly

User · Answer

I was just having the same problem  Here s what I found    I had copied my data from a website  loan payment information   pasted into Excel and then couldn t get it to sort appropriately by date and my calculation formulas wouldn t work  I copied the date columns and pasted as plain text in Word then turned on the formatting characters and found extra characters  namely the one that looks like a degree symbol  Same thing with my currency columns  So I used find and replace to get rid of the extra characters  then copy  amp  paste back in to Excel  Boom  Everything worked the way it should

User · Answer

Custom Format for using   is not recognised by Excel  hence that could be the reason it could not sort   Steps to mitigate  change the format to dd mm yyyy  sort as required   change the format to dd mm yyyy

User · Answer

Copied and pasted date column to Notepad and back

User · Answer

I figured it out   Follow these steps    Highlight the dates you want to filter Switch from  date  format to  number  format  You ll get a weird number  but that s Ok  That s when you sort from  smallest to largest  Now switch it back to  date  format   You re welcome

User · Answer

Sort of an old thread  but I had this same issue today so adding the solution for my problem which nobody has mentioned above   My date data was downloaded from a csv file but the date came with a Timezone at the end  e g  9 7 2018 9 43 42 AM PDT    Excel allows it to be formatted as a date column but apparently does not like the timezone  i e  PDT  at the end for sorting   I removed the timezone at the end  amp  then the sorting works   I did   1  Format as time  mm dd yy xx xx PM   2  Search for  M PDT   amp  replace all with  M   3  Then sort gives you  Oldest to Newest  sort instead of  A to Z    Note that all my datetimes were PDT so only one search  amp  replace  but obviously if you have other timezones  you would have to a separate search  amp  replace for each

User · Answer

I was having the same problem and realised one of my dates was incorrect  I had 31 11 2017  but there is no 31 11 17   When I adjusted to 30 11 17 it sorted correctly   Hope that helps

User · Answer

Tried and always successful  When you have 2 formats in the same column   A - one can be sorted as oldest to newest  real date such as 17 4 2017   B - the other as A to Z   not real date such as 15 3 2018 00 00 00     Select only B cells and replace   00 00 00  with  blank   so it now appears as  15 3 2018  Select only B cells and select -- data -- text to column --Delimited --Next -- unselect all -- Next -- Date DMY -- finish Select the whole cells A and B and sort as you like oldest to newest

User · Answer

I was having this problem due to the dates not being in a format Excel recognised  I manually converted them to DD mm yy using the find and replace tool  Excel was still not recognising the entries as dates  Turns out there was a space in the cell before the date  I deleted the spaces using find and replace and it solved the problem

[excel] How to sort dates from Oldest to Newest in Excel?

Examples related to excel

Examples related to sorting