How to Sort Excel Sheet by Date (8 Methods)

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.


Download Practice Workbook

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


8 Suitable Methods of How to Sort Excel Sheet by Date

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.

How to Sort Excel Sheet by Date


1.  Sort Dates in a Column by 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 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.

Sort Dates in a Column by Chronological Order

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 continue with the current selection
  • Finally, click on the Sort.

● A new window titled Sort Warning

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

Joining Date column have been sorted

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.

select a cell on the Joining Date column and right-click on it

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

How to Sort Excel Sheet by Date

Read More: Excel Sort Dates in Chronological Order (6 Effective Ways)


2. Sort Entire Excel Worksheet by Dates

In the last example, we have sorted only one column based on the dates it contains 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.

Sort Entire Excel Worksheet by Dates

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.

● A new window titled Sort Warning

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

entire worksheet has been sorted based on the Birthdays of the employees

Read More: Excel Sort By Date And Time [4 Smart Ways]


3. Use the MONTH Function to Sort Excel Worksheet by Months

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.
=MONTH(D5)

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 the date in August will be returned as 8.

Sort Excel Worksheet by Months

  • 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.

MONTH function returns the number 8

Step 2:

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

drag the fill handle 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.

Birthdays of the employees are now shown as a numeric value

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.

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.

● A new window titled Sort Warning will appear

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

How to Sort Excel Sheet by Date

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.

click on the Conditional Formatting drop-down from the Styles

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

Birth Months of the employees have been grouped based on different colors

Read More: How to Sort by Month in Excel (4 Methods)


4. Apply the YEAR Function to Sort Excel Sheet by Years

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.
=YEAR(C5)

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

EXCEL YEAR Formula

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

Sort Excel Worksheet by Years

Step 2:

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

drag the fill handle 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.

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.

Sort Excel Worksheet by Years

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.

● A new window titled Sort Warning will appear

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

How to Sort Excel Sheet by Date

Step 5:

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

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.

Excel WEEKDAY Function

  • 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.

WEEKDAY function returns 1 as the date in cell D5 is Sunday

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

Sorted Weekday column

Read More: How to Sort Dates in Excel by Year (4 Easy Ways)


Similar Readings:


5. Sort Birthdays in Excel Worksheet

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. 
=TEXT(D5, "mm.dd")
Formula Breakdown:

The TEXT function excel takes a value (D5) as the first argument and a text format (“mm.dd”) as the second argument. It will return the text or the first augment in the text format that we have given it as the second argument.

Sort Birthdays in Excel Worksheet

  • 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.

Excel Text Function

Step 2:

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

drag the fill handle 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.

the Birthdays of the employees are now shown in “mm.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.

How to Sort Excel Sheet by Date

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.

● A new window titled Sort Warning

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

entire worksheet has been sorted based on the ascending order of the dates in the Month-Day column

Related Content: How to Sort and Filter Data in Excel (A Complete Guideline)


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

We can use a custom sort list to sort the data in a worksheet according to the names of the month or week. For example, we will sort the We have to do the following.

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.

Sort Excel Worksheet Using Custom Sort

Step 2:

  • First, we will check the My data has headers.
  • Then, we will now 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.

How to Sort Excel Sheet by Date

Step 3:

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

select the list with all the month names of the year

  • 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.

Order drop-down has an additional option containing the list of months

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

How to Sort Excel Sheet by Date

Read More: [Solved!] Excel Sort Not Working (2 Solutions) 


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 you can the VBA macro to sort the Excel sheet by date.

Step 1:

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

select Visual Basic from the Developer Tab

  • 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.

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

How to Sort Excel Sheet by Date

  • 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.

Related Content: VBA to Sort Table in Excel (4 Methods)


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

You can also use the Excel formulas instead of the sort feature to sort the Excel sheet by date.  For example, 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.
=IFERROR(INDEX($C$5:$C$14, MATCH(ROWS($C$5:C5), COUNTIF($C$5:$C$14, "<="&$C$5:$C$14), 0)), "")
Note: It is an array formula. So, you have to press CTRL+SHIFT+ENTER keys at the same time to enter the formula.

Sort Excel Worksheet Using Formula

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

How to Sort Excel Sheet by Date

Step 2:

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

drag the fill handle 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.

How to Sort Excel Sheet by Date

Read More: How to Sort Array with Excel VBA (Both Ascending and Descending Order)


Things to Remember

  • The formula in method 8 is an array formula. So, you have to press 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.

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 do leave a comment below. Have a great day!!!


Related Articles

ASM Arman

ASM Arman

Hi there! I am ASM Arman. I Completed B.Sc. in Naval Architecture and Marine Engineering. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations. Have a great day!!!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo