Sorting data by date seems to be a simple task, and indeed, Microsoft Excel provides 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 are not sorted correctly.
Sort a single column of dates in Excel
The easiest way to sort dates in Excel is to use the sort function to display dates in the order you want.
- Select the column of dates by clicking the column header. In the image below, we have selected the column by clicking on column header A†
- You can also just select the specific cells in the column that contain dates.
- Choose with your details selected: Sort and filter from the main menu.
- Now choose one of the two Sort from old to new or Sort from newest to oldest† In the example below, we have sorted from newest to oldest. Note that Excel does not resize the dates, but sorts the dates correctly, starting with the newest.
This method should work in the vast majority of use cases.
Sort an entire worksheet by date in Excel
If your table has more than one column of data, you want to preserve relationships between columns when sorting. Below you see data in column A and corresponding text in column B.
If you use the above method (ie select only the date cells or the entire column of dates, click the Sort and filter button, and choose either Sort from old to new or Sort from newest to oldest), Excel displays a sort warning and asks if you want to extend the selection to include both columns of data.
know for sure Expand the selection is selected and click the Kind knob. Excel sorts all 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.
Sort 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, such as month or year?
Sort dates by month in Excel
For example, in our example table of people’s birthdays, you want to know who was born in each month, ignoring each person’s year of birth. You cannot use Excel’s normal sort function because it will always include the year in the sort 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.
- Create a new column
- In the first cell of the new column, type =MONTH(A1) where A1 is the cell from which you want to extract the month.
- Repeat step 2 for each row. Excel extracts the month from each of the dates, leaving you with a column of month numbers.
- Now you can sort the table by the month number column. In this case, you’re sorting by column C. If you select only the data in column C, you’ll get Excel’s sort warning again. choose to Expand the selection and Kind†
Sort dates by year in Excel
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 containing the date from which you want to extract the year.
Sort dates by month and day in Excel
You may 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 to text. We use the format mm.dd† Follow the steps above and replace 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.
Troubleshoot date format issues in Excel
When you try to sort by date in Excel, sometimes you find that Excel doesn’t recognize data as date. For example, imagine someone gives you a spreadsheet with dates. You put the data in Excel, but it doesn’t sort correctly when you try the above methods. Why doesn’t it work?
Consider this list of dates:
It looks simple enough. However, when you select the data, hit the Sort & Filter button and choose to sort from Oldest to Newest, it doesn’t work! You could end up with something like this:
Note that the last date in the column is not sorted correctly. What the hell is going on?
Chances are, although the data in cell A9 looks like like a date, it’s not really formatted as a date, so Excel can’t sort it correctly. Depending on the version of Excel you are using, even if you format the erroneous cell as a date by selecting the cell, right clicking and selecting Format cells and choosing the Date option, that still will not solve the problem.
Fix dates not sorted correctly in Excel
Fortunately, this problem is easy to solve.
- cut the column of dates by selecting the cells and pressing Ctrl+x, Selecting cut of the House menu, or right-click on the cell(s) and choose cut†
- Open Notepad or any other plain text editor.
- To stick the dates in the text editor.
- Go back to Excel, select the column that contained the dates, right click and select Format cells†
- To elect Date and click Okay†
- Now go back to the text editor and select and copy the list of dates.
- Go back to Excel and To stick the dates in the column you formatted in step 5.
- Now you should be able to sort. Select the columns of data, press the Sort and filter button, and choose Sort from old to new† Viola! It functions!
Other sorting and filtering techniques in Excel
Now that you’ve learned how to sort by date in Excel, you may want to explore other ways to sort and filter your data. In this articles, you will learn how to sort basic data in one column and in multiple columns, how to alphabetize in Excel and how to filter data in excel†