How to Sort Excel Sheet by Date (8 Suitable Examples)

While working with dates, months, or years in Excel you might need to sort the rows of the worksheet in chronological order based on this information. You might need to sort the rows from oldest to newest or vice-versa. In this tutorial, I will show you how to sort Excel sheet by date.

How to Sort Excel Sheet by Date (8 Suitable Examples)

Let’s assume a scenario where we have an Excel worksheet that contains information about the employees of a company. The worksheet has the Name, Joining Date, Birth Day, and the Weekday that an employee is going to be in charge of the shift. We will sort this Excel sheet by date using different methods. The image below shows the worksheet we are going to work with.

1. How to Sort Excel Sheet by Date in a Column with Chronological Order

We can use the Sort feature in Excel to chronologically sort a specific column that contains dates. For example, we will sort the cells in the Joining Date column from oldest to newest.

Step 1:

• First, we will select all the cells in the Joining Date column. Then we will select the Sort & Feature drop-down menu from the Editing section under the Home tab.
• A list with different sorting options will appear. Click on the Sort Oldest to Newest from that list.

Step 2:

• A new window titled Sort Warning will appear. It will let you expand the selection to include the rest of the columns or you can continue with the current selection.
• We will select continue with the current selection.
• Finally, click on the Sort.

• We will now see that the cells in the Joining Date column have been sorted from oldest to newest joining date.

Step 3:

• We can also select a cell on the Joining Date column and right-click on it. A new window will appear. We will select the Sort option from that window.
• Now, a new window with different sorting options will appear. This time we will select the Sort Newest to Oldest option to sort the Joining Dates from newest to oldest.

• We will now see that the cells in the Joining Date column have been sorted from newest to oldest joining date.

Read More: How to Sort by Date in Excel

2. Sort Entire Excel Worksheet by Dates

In the last example, we have seen how to sort Excel sheet by date in only one column while other columns remained unsorted. But in most cases, we will need to sort the entire worksheet based on the dates in a specific column. We can do so following the below steps. For example, we will sort the entire worksheet based on the Birthdays of the employees.

Step 1:

• First, we will select all the cells in the Birth Day Then we will select the Sort & Feature drop-down menu from the Editing section under the Home tab.
• Alternatively, we can also select a cell on the Birth Day column and right-click on it. A new window will appear. We will select the Sort option from that window.
• A list with different sorting options will appear. Click on the Sort Newest to Oldest from that list.

Step 2:

• A new window titled Sort Warning will appear. It will let you expand the selection to include the rest of the columns or you can continue with the current selection.
• We will select the Expand the selection option as we want to sort the entire worksheet.
• Finally, click on the Sort.

• We will now see that the entire worksheet has been sorted based on the Birthdays of the employees from youngest to oldest employee.

Read More: How to Sort Birthdays in Excel by Month and Day

3. How to Sort Excel Sheet by Month Using the MONTH Function

Sometimes you may need to sort the worksheet based on the month of a date instead of the year. Such a situation may arise when we try to group all the birthdays in the same month. In such cases, the default sort feature will not work as Excel always sorts the dates based on the years even if the dates are formatted to show only months or days. But there is a workaround to sort the dates by months that involves using an additional column and the Excel MONTH function. For example, we will group the Birthdays by months using this workaround.

Step 1:

• First, we will write the following formula in cell E5 under the Birth Month.

The MONTH function Excel takes a date as the only argument and returns the order of that month with respect to other months of the year. For example, a date in January month will be returned as 1 while a date in August will be returned as 8.

• Upon pressing ENTER, we will see that the MONTH function returns the number 8 as the date used as an argument (cell D5) is in August.

Step 2:

• We will now drag the fill handle of cell E5 to apply the formula to the rest of the cells.

• All the months of Birthdays of the employees are now shown as a numeric value in the Birth Month.

Step 3:

• We will now sort the Month values from Smallest to Largest so that we can group the birth dates according to their months. First, we will select all the cells in the Birth Month Then we will select the Sort & Feature drop-down menu from the Editing section under the Home tab.
• Alternatively, we can also select a cell on the Birth Month column and right-click on it. A new window will appear. We will select the Sort option from that window.
• A list with different sorting options will appear. Click on the Smallest to Largest from that list.

Step 4:

• A new window titled Sort Warning will appear. It will let you expand the selection to include the rest of the columns or you can continue with the current selection.
• We will select the Expand the selection option as we want to sort the entire worksheet.
• Finally, click on the Sort.

• We will now see that the entire worksheet has been sorted based on the ascending order of the Birth Months from smallest to largest.

Step 5:

• We will now group the birthdays using the birth months of the employee. To do that, we will click on the Conditional Formatting drop-down from the Styles section under the Home.
• A window with a list of different types of conditional formatting will appear. We will select Color Scales from that list.
• Another window with different color schemes will now appear. We will select a color scheme to group our cells in the Birth Month Column based on different colors.

• Finally, we will see that the Birth Months of the employees have been grouped based on different colors.

Read More: How to Sort Dates in Chronological Order in Excel

4. Sort Excel Sheet by Years Applying the YEAR Function

We can also sort the dates using the YEAR function of Excel. Like the MONTH function, it also takes a date value as the only argument. But instead of a month, it returns the year of the date. We can use this function to sort the Joining Years of the employees.

Step 1:

• First, we will write the following formula in cell E5 under the Joining Year.

The YEAR function in Excel takes a date as the only argument and returns the year of that date.

• Upon pressing ENTER, we will see that the YEAR function returns 2010 as the date used as an argument (cell D5) is in 2010.

Step 2:

• We will now drag the fill handle of cell E5 to apply the formula to the rest of the cells.

• All the Joining Years of the employees are now shown in the Joining Year column.

Step 3:

• We will now sort the Joining Years from Smallest to Largest.  First, we will select all the cells in the Joining Year. Then, we will select the Sort & Feature drop-down menu from the Editing section under the Home tab.
• A list with different sorting options will appear. Click on the Sort Smallest to Largest from that list.

Step 4:

• A new window titled Sort Warning will appear. It will let you expand the selection to include the rest of the columns or you can continue with the current selection.
• We will select the Expand the selection option as we want to sort the entire worksheet.
• Finally, click on the Sort.

• We will now see that the entire worksheet has been sorted based on the ascending order of the Joining Years from earliest to latest.

Step 5:

• We can also sort the dates by weekdays using the Excel WEEKDAY function. Write the following formula in cell E5 under the Sorted Weekday column.

The WEEKDAY function Excel takes a date as the only argument and returns the weekday of that date as its order among the 7 days of the week.

• We will now see that the WEEKDAY function returns 1 as the date in cell D5 is Sunday. Excel count weekday from Sunday. Hence it is considered as 1.

• We will follow the above steps to sort the rows according to the weekdays in the Sorted Weekday.

Read More: How to Sort Dates in Excel by Month and Year

5. Sorting Excel Sheet by Birthdays

In this example, I will show how to sort Excel sheet by date of birth.

While sorting the birthdays or any other anniversaries, we only need to sort the dates by their day and month values. The MONTH and YEAR functions above only can extract the month and year separately. You will need the Excel TEXT function to extract both at the same time.

Step 1:

• First, we will write the following formula in cell E5 under the Month-Day. 

• Upon pressing ENTER, we will see that the TEXT function returns the date in cell D5 in the dd format we have inserted as the second argument.

Step 2:

• Then, we will drag the fill handle of cell E5 to apply the formula to the rest of the cells.

• All the Birthdays of the employees are now shown in “dd” format in the Month-Day column.

Step 3:

• We will now sort the cells in the Month-Day column. For that, we will select the Sort & Feature drop-down menu from the Editing section under the Home
• Alternatively, we can also select a cell on the Month-Day column and right-click on it. A new window will appear. We will select the Sort option from that window.
• Now, a list with different sorting options will appear. Click on the Sort A to Z from that list.

Step 4:

• A new window titled Sort Warning will appear. It will let you expand the selection to include the rest of the columns or you can continue with the current selection.
• Then, we will select the Expand the selection option as we want to sort the entire worksheet.
• Finally, click on the Sort.

• We will now see that the entire worksheet has been sorted based on the ascending order of the dates in the Month-Day.

Read More: How to Sort Dates in Excel by Year

6. Perform Custom Sort to Sort Excel Sheet by Month or Weekday Names

So far, we have seen how to sort Excel sheet by date, birthdays, years, and months. Now we will see how to use custom sort list to sort the data in a worksheet according to the names of the month or week.

Step 1:

• First, we will select all the cells in our data range including the column headers.
• Then, we will select the Sort & Feature drop-down menu from the Editing section under the Home.
• Select Custom Sort from the list.

Step 2:

• First, we will check the My data has headers.
• Then, we will select the Joining Month column from the Sort by drop-down menu.
• Now, we will click on the Order drop-down and select Custom List.

Step 3:

• Now, we will select the list with all the month names of the year.
• Then, we will click OK.

• Now, we will see that the Order drop-down has an additional option containing the list of months we have just selected. We will select the list if it is not selected.
• Then, we will click on the OK.

• Finally, we will see that all the rows of the data range have been sorted based on the list of months.

7. Run a VBA Code to Sort Excel Sheet by Dates

The problem with sorting in Excel is that whenever a new row(s) is added to the existing rows, the information will not be re-sorted. You have to manually sort the information again whenever a new row is added or any existing information is edited. But here I will show how to sort Excel sheet by date by running a VBA macro.

Step 1:

• First, we will select Visual Basic from the Developer tab. We can also press ALT+F11 to open it.

• Then, from the Project Explorer on the left side of the window, double-click on the sheet where you want to apply the auto-sort macro.

Step 2:

• Now, insert the following code in the code window.

Private Sub Dynamic_Sort(ByVal Target As Range)
    On Error Resume Next
    Range("B4").Sort Key1:=Range("D5"), _
        Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom
End Sub

• Finally, if we go back to the worksheet and try to add a new row or edit the date in the Birth Day column, we will see that the worksheet is getting sorted automatically every time we do so.

8. Apply an Array Formula to Sort Excel Sheet by Date

In the last example, I will show you how to sort Excel sheet by date using an array formula.  We will rearrange the rows by sorting the Joining Dates from oldest to newest. Just do the following steps.

Step 1:

• First, we will write the below formula in cell E5.

• Now, we will see that the employee who joined last is in cell E5.

Step 2:

• Then, we will drag the fill handle of cell E5 to apply the formula to the rest of the cells.

• Finally, all the rows of the worksheet have been sorted based on Joining Dates from newest to oldest.

Quick Notes

• The formula in method 8 is an array formula. So, you have to press the CTRL+SHIFT+ENTER keys at the same time to enter the formula.
• If you do not have a Developer tab, you can make it visible in File > Option > Customize Ribbon.
• To open the VBA editor Press ALT + F11.
• You can press ALT + F8 to bring up the Macro window.

Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.

Conclusion

In this article, we have learned how to sort Excel sheet by date in different ways. I hope from now on you can sort Excel sheet by date easily. However, if you have any queries or recommendations about this article, please leave a comment below. Have a great day!

Related Articles

• How to Sort by Date and Time in Excel
• How to Sort by Month in Excel

<< Go Back to Sort by Date in Excel | Sort in Excel | Learn Excel