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

Custom Sort Option in Excel Toolbar

Today I will be showing how to sort a data set in Excel by date.

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.

Read more: Excel Sort By Date And Time [4 Smart Ways] and How to Sort Dates in Excel by Year (4 Easy Ways)

Today I will be showing how you can fix that.


Download Practice Workbook


How to Sort by Date in Excel

Here we’ve got a data set with the IDs, names, and joining dates of some employees of a company named Mars Group.

Data Set to Sort by Date in Excel

Our objective today will be to sort the employees according to their joining dates in ascending order.


1. Sort by Date Using Sort & Filter from Excel Toolbar

First of all, we will sort them using the Sort & Filter section from Excel Toolbar.

Select the whole data set and go to Home>Sort & Filter Option in Excel Toolbar.

Sort & Filter Option in Excel Toolbar

Click on the drop-down menu. You will get some options. Select Custom Sort.

Custom Sort from Sort & Filter

You will be presented with the Custom Sort dialogue box.

Read more: How to Create Custom Sort List in Excel

Under the Column option in Sort by, choose the column according to which you want to sort. For the sake of this example, choose Joining Date.

Then under the Sort On option, choose Cell Values.

And under the Order option, choose either the ascending or descending order. For the sake of this example, choose Sort Ascending.

Custom Sort Dialogue Box

Then click OK.

You will find the whole data set sorted according to the joining dates in ascending order like this:

Data Set Sorted by Sort & Filter

And sure, you can sort them in descending order as well, if you wish.

Follow the same procedure.


2. Sort by Date Using the Sort Function 

We have already sorted the whole data set once in the previous section.

But 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?

You can use this using the SORT function of Excel.

Select a cell and insert this formula:

=SORT(B4:D19,3,1,FALSE)

Sort by Date Using the SORT Function

And you will get a sorted copy of the data set.

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

Sorted by Date Using the SORT Function

Explanation of the Formula

  • The SORT function takes four arguments: array, sort_index, sort_order and by_col.
  • The array is the range of cells that you want to sort. Here we want to sort the whole data set, so our array is B4:D19.
  • Sort_index is the number of the row or column according to which you want to sort, in the array.

Here we want to sort according to the joining dates, which is the 3rd column in our array.

Thus our sort_index is 3.

  • Sort_order determines whether you want to sort in ascending or descending order. 1 for ascending order, and -1 for descending order.

Here we want to sort in ascending order. So we used 1. You use your one.

  • By_col tells whether you want to sort row-wise or column-wise. TRUE if you want column-wise, FALSE if you want row-wise.

Here we want to sort the data row-wise.

So you use FALSE.

  • Therefore, the complete formula becomes SORT(B4:D19,3,1,FALSE). It sorts the range of cells B4:D19 according to column number 3 in ascending order.

3. Sort by Date Using the SORTBY Function

Now you may question:

What if I want to sort not the whole data set, only the IDs and Names, but according to the joining dates?

You can not accomplish this using the SORT function.

But you can execute this using the SORTBY function of Excel.

Select a cell and insert this formula:

=SORTBY(B4:C19,D4:D19,1)

Sort by Date Using the SORTBY Function of Excel

You will get the Employee IDs and Employee Names sorted in ascending order, according to the joining dates.

Sorted by Date Using the SORTBY Function of Excel

Explanation of the Formula

  • The SORTBY function takes three arguments: array, by_array and sort_order.
  • The array is the range of the cells which you want to sort. We want to sort the two columns, Employee ID and Employee Name.

So our array is B4:C19.

  • By_array is the column according to which you want to sort. It may be within or outside the array.

We want to sort according to the joining dates. So our by_array is D4:D19.

  • Sort_order is the order in which you want to sort. 1 for ascending order, -1 for descending order.

We want to sort in ascending order. So we used 1. You use your one.

  • Therefore the complete formula becomes SORTBY(B4:C19,D4:D19,1). It sorts the range of cells B4:C19 according to the column D4:D19 in ascending order.

You could have sorted the whole data set using the SORTBY function too.


4. SORT by Months Using the SORTBY and MONTH Function

Till now, we have only sorted according to whole dates.

We can also sort the data set specifically by months using the SORTBY and MONTH functions of Excel.

To sort the data set according to the months of the joining dates, select a new cell and insert this formula:

=SORTBY(B4:D19,MONTH(D4:D19),1)

SORT by Months Using the SORTBY and MONTH Function

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

Sorted by Months Using the SORTBY and MONTH Function

Explanation of the Formula

  • Here the array to be sorted is the range B4:D19.
  • The by_array argument is the array returned by the formula MONTH(D4:D19). It returns the month number of the joining dates.
  • The sort_order is 1, because we are sorting in ascending order. To sort in descending order use -1.
  • So the formula SORTBY(B4:D19,MONTH(D4:D19),1) sorts the data set B4:D19 according to the months of the dates D4:D19, in ascending order.

5. Sort by Days Using the SORTBY and DAY Functions

If you wish, you can use the combination of the SORTBY and the DAY function to sort the data set according to the day section of the joining dates.

So here is how we will write formula:

=SORTBY(B4:D19,DAY(D4:D19),1)

Sort by Days Using the SORTBY and DAY Functions


6. SORT by Years Using the SORTBY and YEAR Function

You can also sort the data set according to only the years of the joining dates. The formula will be:

=SORTBY(B4:D19,YEAR(D4:D19),1)

SORT by Years Using the SORTBY and YEAR Function

We will get the data set sorted according to the years in ascending order.


7. Sort by Days and Months Using the SORTBY, DAY, and MONTH Functions

This time we have a new data set.

We have the IDs, names, and birthdays of some students of a school called Sunflower Kindergarten.

Data Set with Birthdays

Now we will 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.

To accomplish this, select a new cell and insert this formula:

=SORTBY(B4:D19,MONTH(D4:D19)+(DAY(D4:D19)/100))

Sort by Days and Months Using the SORTBY, DAY and MONTH Functions

We will get the data set sorted by the ascending birthdays.

Sorted by Days and Months Using the SORTBY, DAY and MONTH Functions

Explanation of the formula

  • Here the array to be sorted is B4:D19.
  • The by_array argument is the array returned by the formula MONTH(D4:D19)+(DAY(D4:D19)/100).

It divides the days by 100 and then adds those with the months.

For example, 2-Jan becomes 1(January)+(2/100)=1.02

16-Jan becomes 1.16

25-Feb becomes 2.25

9-Dec becomes 12.09

In this way, the day that comes first in the year will have the smallest value even if two dates have the same month.

And the day that comes last in the year will have the largest value even if two dates have the same month

  • The sort_order is 1 because we are sorting in ascending order. To sort in descending order use -1.
  • So the formula SORTBY(B4:D19,MONTH(D4:D19)+(DAY(D4:D19)/100)) sorts the data set B4:D19 according to only the months and days of the dates D4:D19, in ascending order.

8. Sort by Date Using the INDEX-MATCH, ROW, and RANK Functions

Now, look at this new data set.

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.

New Data Set to Sort by Date

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 names, salaries, and joining dates. Or the IDs, names, and salaries. Or the names and salaries.

But not the IDs, names, and joining dates.

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

Read more: Sort Column by Value in Excel

To sort IDs, names, and joining dates according to the joining dates, in ascending order,  select a new cell and insert this formula:

=INDEX(B4:E19,MATCH(ROW(A1:A16),RANK(E4:E19,E4:E19,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.]

Sort by Date Using the INDEX-MATCH and RANK Functions

You will find the Employee IDs, Employee Names, and Joining Dates sorted in ascending order, according to the joining dates.

Sorted by Date Using the INDEX-MATCH and RANK Functions

Explanation of the Formula

  • ROW(A1:A16) returns an array with the numbers from 1 to 16, like {1, 2, 3, …, 16}. I have taken from 1 to 16 because each of my columns has 16 values (B4:B19).

You use your one.

  • RANK(E4:E19,E4:E19,1) returns an array containing the rank of each of the cells in the column E4:E19 in ascending order. To get in descending order, use 0 in place of 1.
  • MATCH(ROW(A1:A16),RANK(E4:E19,E4:E19,1),0) returns the position of each of the numbers in the array {1, 2, 3, …, 16} in the array returned by the RANK function.

That means, it first returns the row number of the 1st date in the original data set, in ascending order.

Then it returns the row number of the 2nd date.

Then the 3rd date.

Thus returns up to the 16th date.

  • Finally, INDEX(B4:E19,MATCH(ROW(A1:A16),RANK(E4:E19,E4:E19,1),0),{1,2,4}) returns the columns 1,2 and 4 (ID, name, and joining dates) from the range B4:E19 in ascending order.

To sort any other columns, adjust the formula accordingly.

Note: The SORT and SORTBY functions are available only in Office 365. So, if you do not have an Office 365 subscription, you have to use this procedure.


Troubleshoot Problems You May Face to Sort by Date in Excel

While working with dates in Excel, we face some common problems.


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

To solve this problem, use the DATE function of Excel rather than inserting the date manually.

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?

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

Excel Recognizing a Date or Not


2. Error with the Formulas Having Dates

This is also a common problem. When we insert a date directly inside a formula, Excel shows an error.

To solve this problem, do not use a date directly inside a formula in a conventional way. Use the DATE function to insert a date within a formula.

For example, do not use =MONTH(05-02-96).

Use =MONTH(DATE(1996,5,2))


3. Getting Confused While Seeing a Date in General Format

Sometimes Excel recognizes a date as a date, but we get confused seeing it in general format (Which is set by default).

For example, the date 2-May-1996 will show 35187 by default (If you do not change the format correctly).

To solve this issue, select the cell and go to Home>Number Format in Excel Toolbar. Then select Short Date.

How to Change the Format of a Date


Conclusion

Using these methods, you can sort any data set in Excel according to dates, in both ascending and descending order. Do you know any more methods? Or do you have any questions? Feel free to ask us.


Further Readings:

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo