Excel Sort by Date (Table of Contents)
Sort by Date in Excel
To sort the data by Date in Excel, we must have the dates in one single format. Go to the Data menu tab and under the Sort & Filter category, select the Sort option. Check the box of My Data has Headers by a tick. Then select the column which has the date and choose the Order by which we want to Sort it. It can be the Oldest to Newest or Vice-Versa. There is another way, to apply the filter in the headers. Then from the drop-down of Date column, select Newest to Older or Vice-Versa to sort the Date in excel.
Where to find Sort Option in Excel?
In excel sort option is available under Data tab.
If you want to sort dates in excel we can use either from oldest to newest or newest to oldest.
- Oldest to Newest: This will bring the old dates first and push the latest dates to the bottom.
- Newest to Oldest: This will bring the latest dates to the top and push the old dates to the bottom of the data.
Based on the user requirement we can select any one of the above options.
How to Sort by Dates in Excel?
It is very simple and easy. Let us understand the working of excel sort by date with some examples.
Example #1
Look at the below data. I have sales numbers from the year 2012 to 2017. Sales are made across dates in these years.
Now I want to sort the excel data from old dates to new dates. Follow the below steps to unleash this option.
- Select the entire data set. If you are sorting dates, it does not mean you need to select only the date column but you need to select the entire data set. If you are selecting only the dates column data will be interchanged. In this example, I am selecting the data from A1 to B23.
- Go to Data < Sort. The shortcut key to open the sort option is ALT + D + S. In excel,
- Sort option is available under the Data tab.
- Once you click on Sort option it will open the below dialogue box.
- Before selecting the Sort by option make sure the My data has headers checkbox is ticked. This will make the first row of the data selection as headers.
- Now click on the drop-down list of Sort by. Here you will see two headers of the selection, select Date option.
- Under the order section, select the way you want to sort the data. In this example, I am selecting as Oldest to Newest.
- Once all the options are selected click on the Ok button.
- It will sort all the data based on the dates from Oldest to Newest.
Each color represents each year dates. It has sorted the data from 30th June 2010 to the latest date of 27th Oct 2017.
Example #2
Take the same data from the above example for this example as well. The above example has sorted the data from DD-MM-YYYY format.
But here we will do different sorting. I want to sort the data based on the date and month. For example June month all together irrespective of the year.
- Insert one helper column called Date & Month.
- Apply TEXT formula as shown in the below image.
TEXT formula ignores the year part and returns only the Date and month name.
DD – This will represent day from the date.
MM – This will return month in numbers. For example, for June it will return 06.
MMM – This will return month in Text with 3 characters. For September it will return Sep.
- Select the entire data including the newly inserted column and press the shortcut key to open the sort option. (I am leaving the shortcut key to you because I have already mentioned the shortcut key in the previous example).
- From the drop-down select the newly inserted column Date & Month.
- Now look the Order section. It is not showing date formatting of Oldest to Newest or Newest to Oldest. It is showing as A to Z. Because the newly inserted row is not in the date format rather it is in text format. Select A to Z.
- If you click on OK it will sort instantly rather it will open up the one more dialogue box. Select below option from that dialogue box and then click Ok.
- It will sort the data based on the criteria we have given.
Now, look at the above image it has sorted based on the date and the month irrespective of the year. It has put all Jan together, all Feb together and so on.
Things to Remember about Sort by Date in Excel
- It is important to select My data has headers.
- You can create your custom list as well.
- The custom list includes your order.
- We sort text, numbers, and dates.
- To sort dates, dates column should be formatted as a date.
Recommended Articles
This has been a guide to Sort by Date in Excel. Here we discuss how to sort by dates in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
