How to Sort by Date in Excel (9 Suitable Ways)

Get FREE Advanced Excel Exercises with Solutions!

Today we will be showing how to sort by date in Excel. While working in Excel, we often have to work with dates. From insertion to manipulation and everything in between. We are given a set of data where we are to sort the data according to the dates, in either ascending or descending order.

Today, we will be showing how you can fix that.


How to Sort by Date in Excel (9 Easy Methods)

The following dataset has the Employee ID, Employee Name, and Joining Date columns. Further, using this dataset, we will go through 9 easy methods to sort by date in Excel. Our objective today will be to sort the employees according to their Joining Dates in ascending order. Here, we used Excel 365. You can use any available Excel version.

Datastet for Sorting by Date in Excel


1. Applying Sort & Filter Feature to Sort by Date in Excel

In this method, we will sort by date using the Sort & Filter feature from the Excel Toolbar.

Steps:

  • First of all, we will select the whole data set by selecting cells B4:D19.
  • After that, we will go to the Home tab.
  • Then, from the Editing group >> select the Sort & Filter option.
  • Furthermore,  select the Custom Sort option.

Using Custom Sort Feature to Sort by Date in Excel

At this point, a Custom Sort dialogue box will appear.

  • Then, under the Column option in Sort by, choose the column according to which you want to sort.

Here, we choose Joining Date as we want to sort by date.

  • Along with that, under the Sort On option, we choose Cell Values.
  • Moreover, under the Order option, we choose Oldest to Newest.

This will sort the Date in ascending order. Here, you can choose any order according to your preference.

  • Then click OK.

As a result, you will find the whole data set sorted according to the joining dates in ascending order.


2. Using SORT Function to Sort by Date in Excel

In this method, we will use the SORT function to sort by date in Excel. We have already sorted the whole data set once in the previous section. However,  what if one does not want to change the original data set, and only wants to create a copy of a sorted data set in a different location? In this case, the SORT function is useful.

Here, one thing must be remembered the SORT function is only available in Excel 365 and higher versions of Excel.

Steps:

  • First of all, we will type the following formula in cell F4.
=SORT(B5:D20,3,1,FALSE)

Applying SORT Function to Sort by Date in Excel

Formula Breakdown

  • SORT(B5:D20,3,1,FALSE) →The SORT function sorts the values of a range of cells.
  • B5:D20 → is the array.
  • 3 → is sort_index.
  • 1 → indicates ascending order.
  • FALSE → indicates row-wise.
  • After that, press ENTER.

Therefore, we have sorted according to the joining dates in ascending order.


3. Use of SORTBY Function to Sort by Date in Excel

In this method, we will use the SORTBY function to sort by date in Excel. This function is helpful when we do not want to sort the whole data set, but only the Employee IDs and Employee Names according to the Joining Dates.

Here, one thing must be remembered the SORTBY function is only available in Excel 365 and higher versions of Excel.

Steps:

  • In the beginning, we will type the following formula in cell F5.
=SORTBY(B5:C20,D5:D20,1)

Use of SORTBY Function to Sort by Date in Excel

Formula Breakdown

  • SORTBY(B5:C20,D5:D20,1) → the SORTBY function sorts the values in a range of cells based on values in a specific array.
  • B5:C20 → is the array.
  • D5:D20 → is the by_array.
  • 1 → indicates ascending order.
  • Afterward, press ENTER.

Hence, will get the Employee IDs and Employee Names sorted in ascending order, according to the Joining Dates.


4.  Using SORTBY and MONTH Functions to Sort by Months

In this method, we will use the SORTBY and MONTH functions to sort by dates in Excel.  This method is helpful when we do not want to sort according to whole dates rather we want to sort by months.

Steps:

  • In the beginning, we will type the following formula in cell F5.
=SORTBY(B5:D20,MONTH(D5:D20),1)

Using SORTBY and MONTH Functions to Sort by Date in Excel

Formula Breakdown

  • SORTBY(B5:D20,MONTH(D5:D20),1) → the SORTBY function sorts the values in a range of cells based on values in a specific array.
  • B5:D20 → is the array.
  • MONTH(D5:D20) → the MONTH function returns the month number of the joining dates.
  • 1 → indicates ascending order.
  • SORTBY(B5:D20,MONTH(D5:D20),1) → sorts the data set B5:D20 according to the months of the dates D5:D20, in ascending order.
  • After that, press ENTER.

Therefore, you will find the data set sorted by the months of the joining dates of the employees in ascending order.


5. Applying SORTBY and DAY Functions to Sort by Day in Excel

In this method, we will use the combination of the SORTBY and the DAY function to sort by date in Excel. This method is helpful to sort the data set according to the day section of the Joining Dates.

Steps:

  • First of all, we will type the following formula in cell F5.
=SORTBY(B5:D20,DAY(D5:D20),1)

Applying SORTBY and DATE Funcion to Sort by Date in Excel

Formula Breakdown

  • SORTBY(B5:D20,DAY(D5:D20),1) → the SORTBY function sorts the values in a range of cells based on values in a specific array.
  • B5:D20 → is the array.
  • DAY(D5:D20) → the DAY function returns the date of a range of cells.
  • SORTBY(B5:D20,DAY(D5:D20),1) → sorts the data set B5:D20 according to the days of the dates D5:D20, in ascending order.
  • After that, press ENTER.

Therefore, you can see the result in cells F5:H19.


6. Using SORTBY and YEAR Functions to Sort by Years in Excel

In this method, we will use the combination of SORTBY and YEAR functions to sort by dates in Excel. This method is useful for sorting the data set according to only the years of the Joining Dates.

Steps:

  • In the beginning, we will type the following formula in cell F5.
=SORTBY(B5:D20,YEAR(D5:D20),1)

Using SORTBY and YEAR Functions to Sort by Date in Excel

Formula Breakdown

  • SORTBY(B5:D20,YEAR(D5:D20),1) → the SORTBY function sorts the values in a range of cells based on values in a specific array.
  • B5:D20 → is the array.
  • YEAR(D5:D20) → the YEAR function returns the year based on a date.
  • SORTBY(B5:D20,YEAR(D5:D20),1) → sorts the data set B5:D20 according to the year of the dates D5:D20, in ascending order.
  • At this point, press ENTER.

Therefore, we will get the data set sorted according to the years in ascending order.


7. Using SORTBY, DAY, and MONTH Functions in Excel

In this method, we will use the combination of SORTBY, DAY, and MONTH functions to sort by date in Excel.  For this method, we have modified the dataset. Therefore, the dataset now has Student ID, Student Name, and Birthday columns. This method is helpful to sort the data set according to days and months of the birthdays of the students, in ascending order. For example, if three birthdays are 10-Aug-2008, 5-Mar-2010, and 12-Dec-2009, the serial will be 5-Mar-2010, 10-Aug-2008, and 12-Dec-2009.

Steps:

  • To begin with, we will type the following formula in cell F5.
=SORTBY(B5:D20,MONTH(D5:D20)+(DAY(D5:D20)/100))

Formula Breakdown

  • SORTBY(B5:D20,MONTH(D5:D20)+(DAY(D5:D20)/100)) → the SORTBY function sorts the values in a range of cells based on values in a specific array.
  • B5:D20 → is the array.
  • MONTH(D5:D20) → the MONTH function returns the month number of the joining dates
  • DAY(D5:D20) → the DAY function returns the date of a range of cells.
  • MONTH(D4:D19)+(DAY(D4:D19)/100) → becomes the by_array of the formula.
  • SORTBY(B4:D19,MONTH(D4:D19)+(DAY(D4:D19)/100)) → sorts the data set B5:D20 according to only the months and days of the dates D5:D20, in ascending order.
  • At this moment, press ENTER.

Therefore, we will get the data set sorted by the ascending birthdays.


8. Applying INDEX, MATCH, ROW, and RANK Functions

In this method, we will use the combination of the INDEX, MATCH, ROW, and RANK functions to sort by date in Excel. This method is helpful if you do not have an Office 365 subscription,

We have almost the same as the first data set, but a new column Salary has been inserted between the Employee Name and Joining Date.

Applying INDEX, MATCH, ROW, and RANK Functions to Sort by Date in Excel

Now, what if we want to sort not all the columns, only the Employee ID, Employee Name, and Joining Date, according to the Joining Date?

You can not execute this using the SORT or SORTBY function of Excel.

Using the SORT and SORTBY functions, you can only sort the adjacent columns, not the non-adjacent ones.

That means you can sort the Employee Name, Salary, and Joining Date. Or, the Employee ID, Employee Name, and Salary. Or, the Name and Salary.

But not the Employee ID, Employee Name, and Joining Date.

You can use a combination of the INDEX, MATCH, ROW, and RANK functions of Excel to sort the columns that are not adjacent.

  • To sort Employee ID, Employee Name, and Joining Date according to the Joining Date, in ascending order,  we will type the following formula in cell G5.
=INDEX(B5:E20,MATCH(ROW(A1:A16),RANK(E5:E20,E5:E20,1),0),{1,2,4})

[This is an Array Formula. So do not forget to press CTRL+SHIFT+ENTER unless you are in Office 365.]

Formula Breakdown

  • ROW(A1:A16) → the ROW function returns an array with the numbers from 1 to 16, like {1, 2, 3, …, 16}. We have taken from 1 to 16 because each of my columns has 16 values (B5:B20).
  • RANK(E5:E20,E5:E20,1) → the RANK function returns an array containing the rank of each of the cells in column E5:E20 in ascending order. To get in descending order, use 0 in place of 1.
  • MATCH(ROW(A1:A16),RANK(E5:E20,E5:E20,1),0) → the MATCH function returns the position of each of the numbers in the array {1, 2, 3, …, 16} in the array returned by the RANK
  • INDEX(B4:E19,MATCH(ROW(A1:A16),RANK(E4:E19,E4:E19,1),0),{1,2,4}) → the INDEX function returns the columns 1,2 and 4 (ID, name, and joining dates) from the range B5:E20 in ascending order.
  • After that, press ENTER.

You will find the Employee ID, Employee Name, and Joining Date sorted in ascending order, according to the Joining Date.


9. Applying VBA to Sort by Date in Excel

In this method, we will use VBA code to sort by date in Excel. This is an easy and handy method.

Steps:

  • First of all, we will press ALT+F11 to bring out the VBA editor window.

Applying VBA to Sort by Date in Excel

At this moment, a VBA editor window will appear.

  • Then, we will double-click on Sheet11 (VBA).

  • Then, we will type the following code in the editor window.
Private Sub Worksheet_Change(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

Applying VBA to Sort by Date in Excel

Code Breakdown

  • We declare a Private Sub since we want the code to run in a specific sheet.
  • We take the starting cell B4 of our dataset in the first Range.
  • We take the starting date cell D5 of our dataset in the second Range.

After that, we close the VBA editor window and go back to our worksheet.

Therefore, we will see that the dataset has been sorted by date.


How to Fix the Issues of Sort by Date in Excel

While working with dates in Excel, you can face the following problem.

Excel Not Recognizing a Date as a Date

This is the most common problem. Sometimes we insert a date in a cell in Excel using our conventional way (mm-dd-yyyy or dd-mm-yyyy) but Excel does not recognize it as a date.

Solution:

To solve this problem follow the instructions given below.

For example, if you want to insert 2-May-1996 in some cells, use DATE(1996,5,2), not 05-02-96.  And how to know whether Excel has recognized a date as a date or not?

  • If the date is aligned right in the cell by default, Excel has recognized it as a date.
  • But if it is aligned left, Excel has not.

In the image below, Excel recognizes the first one as a date, but not the second one.


Practice Section

You can download the above Excel file to practice the explained method.


Download Practice Workbook

You can download the Excel file from the link below and practice while reading this article.


Conclusion

Here, we tried to show you 9 easy methods to sort by date in Excel. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below. Please visit our website ExcelDemy to explore more.


How to Sort by Date in Excel: Knowledge Hub


<< Go Back to Sort in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo