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

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.

This question is related to excel sorting

The answer is

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.

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

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.

I had the same problem and tried all the suggestions above. My dates were formatted mm/dd/yyyy. In desperation I tried the following:

  1. Highlighted the entire column with the dates.
  2. Searched for 2017
  3. Replace All with 2017
  4. The problem was solved (for dates in 2017, repeat for other years).

I hope this helps.

I figured it out!

Follow these steps:

  1. Highlight the dates you want to filter
  2. Switch from "date" format to "number" format. You'll get a weird number, but that's Ok.
  3. That's when you sort from "smallest to largest"
  4. Now switch it back to "date" format

You're welcome!

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 & 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.

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 & paste back in to Excel. Boom! Everything worked the way it should.

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.

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

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...

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.

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 & then the sorting works.

I did: (1) Format as time (mm/dd/yy xx:xx PM) (2) Search for "M PDT" & 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 & replace, but obviously if you have other timezones, you would have to a separate search & replace for each.

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".


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.

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.

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.

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 )

  1. Select only B cells and replace " 00.00.00" with "blank", so it now appears as "15/3/2018"

  2. Select only B cells and select -- data -- text to column --Delimited --Next -- unselect all -- Next -- Date DMY -- finish

  3. Select the whole cells A and B and sort as you like oldest to newest

Copied and pasted date column to Notepad and back.

Questions with excel tag:

Python: Pandas pd.read_excel giving ImportError: Install xlrd >= 0.9.0 for Excel support Converting unix time into date-time via excel How to increment a letter N times per iteration and store in an array? 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data) How to import an Excel file into SQL Server? Copy filtered data to another sheet using VBA Better way to find last used row Could pandas use column as index? Check if a value is in an array or not with Excel VBA How to sort dates from Oldest to Newest in Excel? Creating an Array from a Range in VBA Excel: macro to export worksheet as CSV file without leaving my current Excel sheet VBA: Convert Text to Number EPPlus - Read Excel Table How to label scatterplot points by name? What's the difference between "end" and "exit sub" in VBA? Rename Excel Sheet with VBA Macro Extract Data from PDF and Add to Worksheet Quicker way to get all unique values of a column in VBA? Multiple conditions in an IF statement in Excel VBA How to find and replace with regex in excel Unprotect workbook without password Excel is not updating cells, options > formula > workbook calculation set to automatic Find row number of matching value If "0" then leave the cell blank Clear contents and formatting of an Excel cell with a single command Remove Duplicates from range of cells in excel vba Delete worksheet in Excel using VBA Get list of Excel files in a folder using VBA Excel doesn't update value unless I hit Enter Declare a variable as Decimal Parse XLSX with Node and create json Detect if a Form Control option button is selected in VBA Get length of array? Object of class stdClass could not be converted to string - laravel Java - Writing strings to a CSV file Quickest way to clear all sheet contents VBA VBA: Counting rows in a table (list object) Excel VBA If cell.Value =... then VBA Excel - Insert row below with same format including borders and frames excel - if cell is not blank, then do IF statement filter out multiple criteria using excel vba Referencing value in a closed Excel workbook using INDIRECT? Use Excel VBA to click on a button in Internet Explorer, when the button has no "name" associated IndexError: too many indices for array File name without extension name VBA (Excel) Conditional Formatting based on Adjacent Cell Value Easy way to export multiple data.frame to multiple Excel worksheets Using ExcelDataReader to read Excel data starting from a particular cell What are the RGB codes for the Conditional Formatting 'Styles' in Excel?

Questions with sorting tag:

Sort Array of object by object field in Angular 6 Sorting a list with stream.sorted() in Java How to sort dates from Oldest to Newest in Excel? how to sort pandas dataframe from one column Reverse a comparator in Java 8 Find the unique values in a column and then sort them pandas groupby sort within groups pandas groupby sort descending order Efficiently sorting a numpy array in descending order? Swift: Sort array of objects alphabetically Sort Dictionary by keys python, sort descending dataframe with pandas Swift how to sort array of custom objects by property value Swift Beta performance: sorting arrays Finding median of list in Python Java 8 stream reverse order using lodash .groupBy. how to add your own keys for grouped output? lodash multi-column sortBy descending What is the difference between `sorted(list)` vs `list.sort()`? How to sort a HashSet? What is the purpose of shuffling and sorting phase in the reducer in Map Reduce Programming? Removing Duplicate Values from ArrayList VBA Excel sort range by specific column How to sort Counter by value? - python Python 3 sort a dict by its values How to sort List<Integer>? How to sort multidimensional array by column? How can I sort a std::map first by value, then by key? Sort ObservableCollection<string> through C# How to sort an array of objects in Java? Mergesort with Python How do operator.itemgetter() and sort() work? How to sort an ArrayList in Java C++ String array sorting Quicksort with Python sort json object in javascript How to sort pandas data frame using values from several columns? Python - How to sort a list of lists by the fourth element in each list? Insertion sort vs Bubble Sort Algorithms How to sort a dataFrame in python pandas by two or more columns? orderBy multiple fields in Angular How to use Collections.sort() in Java? How to sort an ArrayList? How to sort 2 dimensional array by column value? how to re-format datetime string in php? Insertion Sort vs. Selection Sort java Arrays.sort 2d array Pandas sort by group aggregate and column Simple bubble sort c# How do I sort a list of datetime or date objects?