Sort by Date in Excel

Excel provides various types of sorting formats e.g. “sort A to Z”, “Sort Z to A”, “Sort by color” and “Sort by date” etc. Sorting in Excel is nothing but arranging data into some meaningful order to make it easier to understand, analyze, or visualize.

How to Sort by Date in Excel?

Chronological or reverse chronological order is the easiest way to sort data by date. This sorts the data in ascending or descending order. In Excel, you must have the dates in one single format to sort the data by date otherwise they do not always work.

To sort the dates in chronological order follow the below steps:

Step 1:  Select the dates which you want to sort in ascending or descending order.

Step 2: Click Home tab -> Click on the arrow under Sort & Filter, and then click Sort Oldest to Newest.

As a result, data is sorted from oldest to newest.

Step 3:  If you want to sort dates from Newest to Oldest, then Click Home tab -> Click on the arrow under Sort & Filter, and then click Sort Newest to Oldest.

As a result, data is sorted by newest to oldest.

How to Sort Data by Month in Excel?

If you want to sort dates by month, ignoring the year, then you have to add a helper column, extract the month number, and sort by that column because the default Excel sort feature always considers the year.

You can use the "MONTH" function to get a month from the date.

Syntax:

" =MONTH (serial_number) "

Now follow the below steps to sort by month in Excel:

Step 1: Extract the month number from the date column.

In the above example, the month number is extracted from the "date of birth" column by using the " =MONTH(D2) " formula:

Step 2: Sort the table by the Month column, select the month range which you want to sort. Then Click Home tab -> Click on the arrow under Sort & Filter, and then click Sort Smallest to Largest.

As a result, data is sorted by month smallest to largest.

How to Sort Data by Month and Day in Excel

Excel TEXT functions are assisting for it. It converts a date into a text string in the specified format. For our function, the "mm-dd" format code will work.

Syntax:

" =Text(Value, format_text) "

Now follow the below steps to sort the by Month and day in Excel: 

Step 1: Extract the month and day from the date column.

In the above example, the "month and date" is extracted from the "date of birth" column by using the =TEXT(D2,"mm-dd") formula:

Step 2: Sort the table by the Month and Day column, select the Month and Day column range which you want to sort. Then Click Home tab -> Click on the arrow under Sort & Filter, and then click Sort Z to A.

As a result, data is now sorted by "month and day", largest to smallest.

How to Sort Data by Year in Excel?

When you want to sort data by year, the easiest way is to arrange dates in chronological order with Excel's ascending sort (oldest to newest) option.

This will sort dates by year, then by month, and then day as shown in the screenshot below.

How to Sort by Days of the Week in Excel

Embarking the journey of sorting data by days of the week in Excel opens up a realm of insightful organization. This method empowers you to rearrange information in a sequence that mirrors the flow of the week, a vital perspective for various analytic pursuits.

The path to weekday-based sorting demands the creation of a helper column, mirroring previous strategies. In this iteration, however, the helper column takes on the form of the WEEKDAY formula. This formula delivers a numerical representation corresponding to the day of the week.

For a Week from Sunday(1) to Saturday(7), the formula used is:

" =WEEKDAY(A2) "

Unleashing the Weekday Sorting

With the helper column meticulously curated, the act of sorting unfurls with precision. Each data point aligns itself with its respective day of the week, offering a snapshot of your information's temporal rhythm.

As you go through the mechanics of weekday-based sorting, remember that Excel's versatility thrives on these strategies. By unveiling patterns hidden within your data's temporal context, you harness the power of organization to elevate.