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.

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.

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

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

Then click **OK**.

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

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)`

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

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

**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_co**l 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
It sorts the range of cells`SORT(B4:D19,3,1,FALSE)`

.**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)`

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

**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`

It sorts the range of cells`,1)`

.**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)`

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

**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. It returns the month number of the joining dates.`MONTH(D4:D19)`

- The
**sort_order**is 1, because we are sorting in ascending order. To sort in descending order use -1. - So the formula
sorts the data set`SORTBY(B4:D19,MONTH(D4:D19),1)`

**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)`

**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)`

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.

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))`

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

**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
sorts the data set`SORTBY(B4:D19,MONTH(D4:D19)+(DAY(D4:D19)/100))`

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

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 **INDEXâ€“MATCH,Â 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**.]

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

**Explanation of the Formula**

returns an array with the numbers from 1 to 16, like`ROW(A1:A16)`

**{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.

returns an array containing the rank of each of the cells in the column`RANK(E4:E19,E4:E19,1)`

**E4:E19**Â in ascending order. To get in descending order, use**0**in place of**1**.returns the position of each of the numbers in the array`MATCH(ROW(A1:A16),RANK(E4:E19,E4:E19,1),0)`

**{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,
returns the columns 1,2 and 4 (ID, name, and joining dates) from the range`INDEX(B4:E19,MATCH(ROW(A1:A16),RANK(E4:E19,E4:E19,1),0),{1,2,4})`

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

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

**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

**Excel Sort Dates in Chronological Order (6 Effective Ways)****How to Sort by Last Name in Excel (4 Methods)****Sort Multiple Columns in Excel (5 Quick Approaches)****How to Auto Sort Multiple Columns in Excel (3 Ways)****Sort Two Columns in Excel to Match (Both Exact and Partial Match)****How to Sort Data by Color in Excel (4 Criteria)****Sort Alphabetically in Excel with Multiple Columns (4 Methods)**