Excel Sort Dates in Chronological Order (6 Effective Ways)

Sorted dates

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 different ways to sort dates in chronological order in Excel.

Download the Practice WorkBook

6 Ways to Sort Dates in Chronological Order in Excel

1. Sort Dates in Chronological Order Using Sort & Filter Option

We see here that we have some products with their name, order date, and delivery dates, and price. Now we will sort order dates using the Sort & Filter option.

Sort Dates in Chronological Order Using Sort & Filter Option

Step 1: Select the Order Date

Select the order Dates

Step 2: Go to the Home tab and select the Sort & Filter option under the Editing section. Then select the Sort Oldest to Newest option

Go to the Home tab and select the Sort & Filter option under the Editing section

Step 3: A warning will pop up. Just click on the Sort button

Warning

Step 4: Now all the order dates will be sorted

Sorted dates

2. Sort Dates in Chronological Order by Month

Now let’s see how we can sort dates in chronological order by month. For this, we will consider the Delivery Date column. Additionally, we will use Excel’s MONTH function. Now we will have one more column named Month which will contain the months of the delivery dates.

Step 1: Enter the formula in cell E4

=MONTH(D4)

Enter formula using month function

Step 2: Copy down the formula up to E10

Copy down the formula up to E10

Step 3: Now sort them by using Sort & Filter function.

using sort and filter

Step 4: All the data will be sorted based on the month (Smallest to Largest)

Sorting result

3. Sort Dates in Chronological Order by Month and Day

Now we will sort the dates based on 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.

Step 1: Enter the formula in cell E4 and copy it down up to E11

=TEXT(D4, "mm.dd")

Enter formula using MONTH and DAY

Step 2: Now sort it using the Sort & Filter option like the previous one

Sorted data

4. Sort Dates in Chronological Order by Year

Up to now, we have seen sorting by month and day, now let’s see Sort Dates in Chronological Order by Year. Again, the dataset will be the same as method 1 with one more column named Year. We will consider the year of the delivery date.

Step 1: Enter the formula in cell E4 and copy it down up to E11

=YEAR(D4)

Enter formula using Year

Step 2: Now sort it using the Sort & Filter option like the previous one

Sorted result

5. Sort Dates in Chronological Order by Days of the Week

Here our concern will be based on days of the week. For this, we will need Excel’s WEEKDAY function which returns the number of days of the week from any given date. Again the dataset will be the same as method 1 with one more column named Days of Week. We will consider the year of the delivery date.

Step 1: Enter the formula in cell E4 and copy it down up to E11

=WEEKDAY(D4)

Enter formula using Weekday

Step 2: Now sort it using the Sort & Filter option like the previous one

Sorted data

6. Auto Sort Dates in Chronological Order Using Formula

Let’s see how we can sort dates using a formula. For this, we will consider the Delivery Date and we will print the sorted dates in the next column.

Step 1: Enter the formula in cell E4

=IFERROR(INDEX($D$4:$D$10, MATCH(ROWS($D$4:D4), COUNTIF($D$4:$D$10, "<="&$D$4:$D$10), 0)), "")

Formula Explanation

Here three functions are used which are IFERROR, INDEX, MATCH, COUNTIF. COUNTIF($D$4:$D$10, “<=”&$D$4:$D$10) this portion counts by comparing the condition. MATCH(ROWS($D$4:D4) this part match the rows. Lastly, INDEX finds the index number from Delivery Dates and lastly, IFERROR is for ignoring the error.

 Enter the formula in cell E4

Step 2: Copy down the formula up to E10

sorted result

Things to Remember

  1. Make sure when you will use DAY, MONTH, YEAR functions the field format should be in General format.
  2. Make sure your dates are in the correct format.

Conclusion

These are the ways to sort dates in chronological order. I have shown all the methods with their respective examples but there can be many other iterations. I have also discussed the fundamentals of the used functions. If you have any other method of achieving this then please feel free to share it with us.

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo