Microsoft Excel presents various sort options. Sorting may differ in terms of our needs and conditions. What we need to know is the correct and proper use of the sorting options in Excel. Sorting dates may help us to manage our data more effectively and efficiently. In this article, we will see 6 effective ways on how to sort dates in chronological order in Excel. I hope it will be very helpful for you if you are looking for a simple and easy, yet effective way to sort dates in chronological order in Excel.
Download the Practice WorkBook
6 Effective Ways to Sort Dates in Chronological Order in Excel
In order to sort dates in chronological order in Excel, there are many simple and effective ways. I am going to discuss 6 of them here. For more clarification, I am going to use a dataset where I have arranged data in the Products, Order Date, Delivery Date, and Price columns.
1. Adopt Sort & Filter Option
Adopting Sort & Filter option is the simplest way to sort dates in chronological order. The whole process is described in the following section.
Steps:
- Select the dates that your want to sort in chronological order.
- Next, go to Home.
- From the ribbon, select Editing along with Sort & Filter.
- Now, choose your sorting pattern from the available options. I have picked Sort Oldest to Newest.
A warning box will appear.
- Mark the box having Expand the selection.
- Finally, click on Sort.
Now, we can see the sorted dates in chronological order on the selected cells.
Read More: Excel Sort By Date And Time
2. Apply MONTH Function
The MONTH Function can be another quick way to sort dates in chronological order. It helps to find the number of the month in the year. We can then use it to sort dates in chronological order.
Steps:
- Select a cell and input the following formula to have the month number.
=MONTH(D5)
- Next, press on ENTER to have the output.
- Now, use Fill Handle to AutoFill the rest cells.
- After that, go Home.
- From the ribbon, select Editing along with Sort & Filter.
- Now, choose your sorting pattern from the available options. I have picked Sort Largest to Smallest.
A warning box will appear.
- Followingly, Mark the box having to Expand the selection.
- Finally, click on Sort.
Thus, we can have the sorted dates in chronological order on the selected cells.
Read More: How to Sort Rows by Date in Excel (8 Methods)
3. Employ TEXT Function
In order to sort the dates in chronological order, we can also consider month and day. For this, the required functions are MONTH and DAY. And again, our dataset will be the same as the previous one and the additional column will be named Month and Day.
Steps:
- First of all, create a column (i.e. Month and Day) and input the following formula to have month and day values.
=TEXT(D5, "mm.dd")
- Press ENTER and AutoFill the rest cells.
- Afterward, click on Home.
- Go to Editing along with Sort & Filter from the ribbon.
- Now, choose your sorting pattern from the available options. I have picked Sort Z to A.
We can see our desired output on the screen.
Read More: How to Sort Dates in Excel by Month and Year (4 Easy Methods)
Similar Readings
- Excel VBA to Sort by Column Header Name (5 Easy Ways)
- How to Sort Range Using VBA in Excel (6 Examples)
- Excel VBA to Sort Alphabetically
- [Fixed!!] VBA Sort Not Working in Excel
- How to Sort Data by Row Not Column in Excel (2 Easy Methods)
4. Use YEAR Function
The YEAR Function is used to find the year from a date. We can also sort dates in chronological order based on year.
Steps:
- Use the following formula to have the year values.
=YEAR(D5)
- Then, use Sort & Filter under the Home tab to sort dates according to your preferred chronological order.
I have used the Sort Smallest to Largest order to sort dates.
Read More: How to Sort Dates in Excel by Year
5. Apply WEEKDAY Function
Another very easy way to sort dates in chronological order is to use the WEEKDAY function. The WEEKDAY Function is used to find the day’s number of a week. We can also sort dates in chronological order based on that.
Steps:
- Input the following formula to have the day’s number of a week.
=WEEKDAY(D5)
- After that, click on Sort & Filter under the Home tab to sort dates according to your preferred chronological order.
In this case, I have used the Sort Smallest to Largest order to sort dates.
Read More: [Fix] Excel Sort by Date Not Working (2 Causes with Solutions)
6. Combine IFERROR, INDEX, MATCH, COUNTIF & ROWS Functions
Adopting a combined formula with the IFERROR, INDEX, MATCH, COUNTIF, and ROWS functions, we can also sort dates in chronological order.
Steps:
- Select a cell and input the following formula in that cell..
=IFERROR(INDEX($D$5:$D$11, MATCH(ROWS($D$10:D10), COUNTIF($D$5:$D$11, "<="&$D$5:$D$11), 0)), "")
- Next, press on ENTER.
We can see the oldest date in that cell.
- Finally, AutoFill the remaining cells.
Read More: How to Sort Birthdays by Month and Day in Excel (5 Ways)
Practice Section
You can practice in the following section for more expertise.
Conclusion
At the end of this article, I like to add that I have tried to explain 6 effective ways on how to sort dates in chronological order in Excel. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below. You can visit our site for more articles about using Excel.
Related Articles
- Excel VBA to Custom Sort (5 Easy Examples)
- How to Auto Sort In Excel When Data Is Entered
- Sum Using OFFSET and MATCH in Excel (4 Ideal Examples)
- Sort Two Columns to Match in Excel (2 Simple Methods)
- How to Sort by Column in Excel to Keep Rows Together
- Sort IP Address in Excel (6 Methods)
- Excel VBA to Sort a ComboBox List Alphabetically