How to 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.

Here, we will be discussing how to sort by Date in Excel whether it may be Years, Months, Days, or weeks.

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.

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(D3) “ 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.

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(D3,”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.

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.

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

In conclusion, mastering the process of sorting by date in Excel can significantly enhance your data analysis and organizational skills. By utilizing features like the Sort function, custom sort options, and even advanced techniques like creating helper columns, you can optimize your spreadsheets for better readability and more insightful data interpretation. This skill not only boosts your productivity but also ensures that your data is always presented in a clear and logical sequence.

Why can’t I sort by date in Excel?

You might not be able to sort by date in Excel if the dates are formatted as text instead of date. Excel needs to recognize the data as a date to sort it correctly. Make sure your date cells are formatted as dates (you can check and change this in the format cells option).

How to filter Excel by date?

To filter by date in Excel:

1. Click on the Data tab at the top.
2. Ensure your date column has a header.
3. Click “Filter” in the toolbar; this adds drop-down arrows in the header row.
4. Click the drop-down arrow in your date column, and you’ll see date filtering options like “Before,” “After,” and specific date ranges you can select.

Why is Excel sorting dates from A to Z?

If Excel is sorting your dates from A to Z, it’s likely treating them as text rather than dates. To fix this, you should check the formatting of your date column and make sure it’s set to a date format. Once Excel recognizes the entries as dates, it will sort them chronologically instead of alphabetically.

How to sort a date in Excel with multiple columns?

To sort by date across multiple columns:

• Select all the data you want to sort.
• Go to the Data tab and click “Sort.”
• In the Sort dialog, you can add levels (use “Add Level”) to sort by. Choose your main date column for the first level, and then add other columns as secondary criteria.
• Make sure to set the correct order for each level (oldest to newest or newest to oldest) based on your needs.