How to Sort by Date in Excel

One of the most helpful features in Excel is the ability to sort your data by date. Maybe you need to arrange your family members’ birth dates to send out cards, sort your weekly budget transactions for year-end, or track sales revenue by month.

There are several ways to arrange your entries according to date. You can put data in ascending or descending order and you can sort by day, week, month, or year. We'll walk you through all the different options below.

How to Sort Excel Columns by Ascending or Descending Date

It’s easy to sort an individual column into ascending or descending date order in Excel:

1. Select the data you want to sort.
2. From the menu bar (also called the ribbon) at the top of your screen, under the Home tab, select Sort & Filter.
3. Then choose Sort Oldest to Newest or Sort Newest to Oldest.

Alternatively, you can get the same result by selecting your data and using a right-click to select Sort. Then choose the order that works best for your data.

This works well if you are sorting a simple list of dates and plan to enter the corresponding data later. Sorting this way will always order your dates chronologically, by year first, then by month, then by day.

How to Sort Entire Tables by Ascending or Descending Date

When sorting dates in a table, it's helpful to add headings at the top of each column. Below, we'll show you how to use these headings as categories to sort your data.

When you sort data in a table, it's important to highlight all of it. If you only highlight one column, you'll only sort that column, leaving the corresponding columns in their original order. Then the dates won't match up with the correct data.

If you catch the error right away, you can click Undo to return to the original arrangement. Otherwise, you might have to revert to a previous version of your document or re-enter the data.

Fortunately, Excel usually catches this error in time to prevent it.

If you try to sort with only the dates selected, Excel should give you a warning and ask if you would like to expand your selection. When the error message pops up, select Expand the selection and click Sort.

Then hover over the data selection. Highlight the missing items by dragging the small, green square at the bottom left corner and try sorting again.

Select the headings and data you want to sort. Then click Home > Sort & Filter and select your sort order.

Note that this will only work if the date is in Column A. If you'd like to sort by date when the dates are listed in a different column, you'll need to use Custom Sort.

Using Excel's Custom Sort to Sort By Date

Custom Sort is another handy way to sort by date. If your data has headings, you can use them as categories to sort entries easily, even if they aren't in the first column.

In the example below, a simple sort like we've already shown you will sort the items in the Transaction column alphabetically, instead of putting your dates in order. Since the date is in the second column, we need to use a Custom Sort to tell Excel to sort the dates in Column B.

To do this:

1. Highlight the headings and the data below them.
2. In the Excel ribbon, click Home > Sort & Filter > Custom Sort. Next to Sort by, choose Date from the dropdown menu.
3. Check the box in the top-right corner to use your data headers as sorting categories. Select Oldest to Newest or Newest to Oldest from the Order dropdown menu.

Custom Sort also lets you choose multiple sorting categories. For example, you might use this function if you want to see transactions listed first by one column, then by another.

In the Custom Sort box, select Sort by > [First Column]. Then click Add Level > Then by > [Second Column]. Click OK to sort your table.

Using Excel Functions to Sort by Month

There are some cases where you might want to extract the month from the date as entered. One example is putting all your colleagues’ birthdays in order, so you can acknowledge everyone with a monthly birthday cake. For your purposes here, the month is important but the year is irrelevant.

There are many Excel functions and formulas that can help you do quick calculations or extract data from a cell. One such function is the MONTH function.

Use the MONTH function to pull each entry’s month digit from the full date. First, add a column with Month as the header. Click in the first empty cell under Month. In this case, it will be C2. Type =MONTH(B2), where B2 refers to the first entry in the Date column.

The MONTH function will output a digit from 1 to 12 into the corresponding cell. These digits represent the months in chronological date order, so January=1 and December=12. To enter the function quickly into every cell, hover over C2 until the green box appears in the bottom corner of the cell. Then, click and drag the box to the bottom of the column.

Now you can use Excel to sort dates by month, so you don’t miss anyone on your list. Highlight the whole table, including the headers, right-click, click Sort & Filter > Custom Sort > Sort by Month.

You can also use a function to sort data by year. Follow the instructions above replacing the MONTH function with =YEAR(A2). The result will be a list of years in order.

Using an Excel Formula to Sort by Month and Day

The MONTH function is quick and easy if you don't need the days in order, but let's say you want to celebrate each birthday individually. A formula can help here.

Add a column called Birthdays. In the empty cell (C2), next to the first date, type =TEXT(B2,"MMDD"), where B2 refers to the first date. This will return a list of dates, without the year, formatted as MMDD. For example, 12/07/1964 will become 1207.

Do a Custom Sort to put your birthday list in order. Since the birthdays are formatted as text, rather than dates, Excel will give you a warning. Select Sort anything that looks like a number, as a number > OK, and your list will be in the correct order.

What If Sort by Date Isn’t Working in Excel?

If your data doesn’t seem to be sorting correctly, check that you’ve entered it in a format Excel will recognize. If all the numbers are left-justified in their cells, you might have them stored as text. To fix the format, click on the Text drop-down menu in the Excel ribbon and select a date format.

Organize Your Excel Data by Date

The ability to sort by date in Excel is super useful and has a variety of applications, as we’ve seen here. There are many apps and useful spreadsheet templates out there to complete almost any task, from budgeting and expense tracking to wedding planning.

Templates are a great place to start, but the beauty of Excel is the ability to completely customize it to meet your needs.