How to Sort by Date in Excel

Plus, a trick to try if it’s not working

Sorting data by date seems like it should be a simple task, and, indeed,Microsoft Exceloffers a number of ways to sort data by date. However, sometimes Excel’s sorting tools don’t work correctly, and that can be very frustrating.

Here are a few ways to sort by date in Excel, as well as a trick to try if dates aren’t sorting correctly.

Sorting a Single Column of Dates in Excel

The easiest way to sort dates in Excel is to use the sorting tool to display dates in the order you want.

This method should work in the vast majority of use cases.

Sorting an Entire Worksheet by Date in Excel

If your table has more than one column of data, you’ll want to preserve the relationships between columns when you sort. Below you can see dates in column A and corresponding text in column B.

If you use the method above, (i.e., selecting only the date cells or the whole column of dates, clicking on theSort & Filterbutton, and choosing to eitherSort Oldest to NewestorSort Newest to Oldest), Excel will display a Sort Warning and will ask you if you want to expand the selection to include both columns of data.

Make sure thatExpand the selectionis selected and click theSortbutton. Excel will sort all the data in the table in the sort order you selected.

You can see that now all our dates are sorted from oldest to newest, and the relationship between the dates in Column A and the text in Column B has been preserved.

Sorting by Month, Year, or Month and Day in Excel

What if you want to sort by date in Excel by a specific part of the date like month or year?

In our example table of people’s birthdays, for example, you might want to know who was born in each month, ignoring the year each person was born. You can’t use Excel’s regular sort feature because it will always include the year in its sorting algorithm.

To get around this, you can create a helper column that extracts the month so you can then sort by that column. To extract a month from a date, you can use Excel’s MONTH function.

If you want to extract the year instead of the month, you can use the steps above, replacing the MONTH function with=YEAR(A1)where A1 is the cell with the date from which you want to extract the year.

Perhaps you want to create a sorted list of birthdays by month and day, ignoring the year. To extract the month and day from the column of dates, we can use Excel’s TEXT function, which converts dates into text. We’ll use the formatmm.dd. Follow the steps above, replacing the MONTH function with this formula:=TEXT (A1, “mm.dd”).

Now you can sort by Column C to get a list of everyone’s birthdays by month and day.

Troubleshooting Date Formats in Excel

Sometimes when you try to sort by date in Excel, you might find that you can’t get Excel to recognize data as a date. Imagine, for example, someone gives you a spreadsheet that includes dates. You’ve put the data into Excel, but it isn’t sorting correctly when you try the methods above. Why isn’t it working?

Consider this list of dates:

It looks simple enough. However, when you select the data, press the Sort & Filter button, and opt to sort from Oldest to Newest, it doesn’t work! You might end up with something like this:

Notice that the last date in the column hasn’t been sorted correctly. What on earth is going on?

Chances are that even though the data in cell A9lookslike a date, it isn’t really formatted as a date, so Excel can’t sort it correctly. Depending on which version of Excel you’re using, even if you format the errant cell as a date by selecting the cell, right-clicking and selectingFormat Cellsand choosing theDateoption, thatstillwon’t fix the problem.

Fortunately, this problem is easy to fix.

Other Sorting and Filtering Techniques in Excel

Now that you’ve learned how to sort by date in Excel, you might want to explore other ways to sort and filter your data. These articles will teach youbasic one-column and multi-column data sorting,how to alphabetize in Excel, andhow to filter data in Excel.

Maggie Marystone is a freelance technology writer, human rights worker, and storyteller based in Chicago.Read Maggie’s Full Bio

Welcome to Help Desk Geek- a blog full of tech tips from trusted tech experts. We have thousands of articles and guides to help you troubleshoot any issue. Our articles have been read over 150 million times since we launched in 2008.

HomeAbout UsEditorial StandardsContact UsTerms of Use

How to Sort by Date in Excel#

Sorting a Single Column of Dates in Excel#

Sorting an Entire Worksheet by Date in Excel#

Sorting by Month, Year, or Month and Day in Excel#

Troubleshooting Date Formats in Excel#

Other Sorting and Filtering Techniques in Excel#