Md. Abdullah Al Murad
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.
Read more: Excel Sort By Date And TimeÂ
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.
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.
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.
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.
Read more: How to Sort Dates in Excel by 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.
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.
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.
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.
Further Readings
- How to Sort by Date in Excel (8 Suitable Ways)
- Sort Column by Value in Excel (5 Methods)
- Sorting Columns in Excel While Keeping Rows Together
- Auto Sort When Data is Entered in Excel (3 Methods)
- How To Sort Alphabetically In Excel And Keep Rows Together
- How to Arrange Numbers in Ascending Order in Excel using Formula
- How to Use Advanced Sorting Options in Excel
