The dataset below has data in the Products, Order Date, Delivery Date, and Price columns.
Method 1 – Adopting the Sort & Filter Option
Steps:
- Select the dates that you want to sort in chronological order.
- Go to the Home tab.
- From the ribbon, select Editing along with Sort & Filter.
- 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.
- Click on Sort.
We can see the sorted dates in chronological order in the selected cells.
Read More: How to Sort by Date in Excel
Method 2 – Applying the MONTH Function
Steps:
- Select a cell and enter the following formula:
=MONTH(D5)
- Press ENTER.
- Use Fill Handle to AutoFill the rest cells.
- Go to the Home tab.
- From the ribbon, select Editing along with Sort & Filter.
- Choose your sorting pattern from the available options. I have picked Sort Largest to Smallest.
A warning box will appear.
- Check the box Expand the selection.
- Click on Sort.
We have the sorted dates in chronological order on the selected cells.
Read More: How to Sort Dates in Excel by Year
Method 3 – Employing the TEXT Function
Steps:
- Create a column (i.e. Month and Day) and input the following formula:
=TEXT(D5, "mm.dd")- Press ENTER and AutoFill the rest cells.
- Click on Home.
- Go to Editing along with Sort & Filter from the ribbon.
- Choose your sorting pattern from the available options. I have picked Sort Z to A.
We can see our desired output on the screen.
Method 4 – Using the YEAR Function
Steps:
- Select a cell and enter the following formula:
=YEAR(D5)
- 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 Month and Year
Method 5 – Applying the WEEKDAY Function
Steps:
- Select a cell and enter the following formula:
=WEEKDAY(D5)
- Click on 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.
Method 6 – Combining IFERROR, INDEX, MATCH, COUNTIF & ROWS Functions
Steps:
- Select a cell and enter the following formula:
=IFERROR(INDEX($D$5:$D$11, MATCH(ROWS($D$10:D10), COUNTIF($D$5:$D$11, "<="&$D$5:$D$11), 0)), "")
- Press ENTER.
We can see the oldest date in that cell.
- AutoFill the remaining cells.
Read More: How to Sort by Date and Time in Excel
Practice Section
You can practice in the following section.
Download the Practice Workbook
Related Articles
<< Go Back to Sort by Date in Excel | Sort in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Sorting the dates using your 6th method with the IFERROR, INDEX, MATCH, COUNTIF & ROWS Functions is intriguing. However I don’t understand why, in your example, the Rows function starts at D10. The column for that table starts at D5 and ends at D11.
Can you explain your 6th method in alot more detail please? Will this work in Excel versions that are earlier than 365? Will it also sort dates generated by formula in a dynamic table, ie there are empty rows as well.
Hello CJ,
Thanks for your comment. To go into the details of your queries, let me break down the formula for you used in method 6 in a simpler form first.
The whole formula was:
=IFERROR(INDEX($D$5:$D$11, MATCH(ROWS($D$10:D10), COUNTIF($D$5:$D$11, “<=”&$D$5:$D$11), 0)), “”)
Here, the ROWS function counts the number of rows in the defined array.
ROWS($D$10:D10) → 1
The COUNTIF function compares the values in the given range and denotes them with a number based on the position in the smallest to largest order.
COUNTIF($D$5:$D$11, “<=”&$D$5:$D$11) → {4;6;1;5;2;3;7}
The MATCH function compares the values returned by the ROWS & COUNTIF functions and returns the index number of the position of the exact match.
MATCH(1, COUNTIF($D$5:$D$11, “<=”&$D$5:$D$11), 0))
MATCH(1, {4;6;1;5;2;3;7})→ 3
The INDEX function returns the third date value from the defined range in general form.
INDEX($D$5:$D$11, MATCH(ROWS($D$10:D10), COUNTIF($D$5:$D$11, “<=”&$D$5:$D$11), 0))
INDEX($D$5:$D$11, 3) → 43811
If there is an error in finding a date, the IFERROR function will return a blank cell as an output.
While applying the ROWS function, I have set a reference point from D10 and also finished the array on D10. That returns the number of row count 1. However, it is not mandatory that you have to set the reference point from D10. You can start from any cell between D5 to D11 but the starting and ending cell reference should be the same in that array. You can apply “ROWS($D$5:D5)” and it will return 1 too which is the same output.
If I am not wrong, the IFERROR function was introduced in the 2007 Excel version and the INDEX, MATCH, COUNTIF, & ROWS functions are available in the earliest Excel versions too. So, I hope it will work perfectly from the 2007 and the later Excel versions.
This formula can be applied to a dynamic table. It will automatically sort dates within the given range.
I hope you have the answers that you were looking for.
Regards,
Naimul Hasan Arif