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

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.