How to Sort by Date in Excel

Publish date: 2024-04-06

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.

In Excel, there are several ways to sort your entries by 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 on how to sort by date in Excel.

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:

  • Select the data you want to sort.
  • From the menu bar (also called the ribbon) at the top of your screen, under the Home tab, select Sort & Filter.
  • 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 list of dates that does not need to correspond with other data. Sorting this way will always order your dates chronologically, by year first, then by month, then by day.

    If you’re using Excel spreadsheet templates to organize your life or have tables of data, you may need to use other methods to sort by date in Excel instead, which we’ll go through next.

    How to Sort Entire Tables by Ascending or Descending Date in Excel

    When sorting dates in a table, it's helpful to add headings at the top of each column. For example, if you’re making a basic Gantt chart in Excel, you’ll have a list of tasks that correspond to a list of dates. Below, we'll show you how to use these headings as categories to sort your data.

    The first step is to ensure that you're selecting your data correctly. 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. This time, remember to re-highlight the entire table of data and not just one column.

    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 the first column of the table (in this example, it’s Column A). In Excel, 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 in Excel. If your data has headings, you can use them as categories to sort entries easily, even if they aren't in the first column.

    Here, a simple sort will sort the items in the Transaction column alphabetically, instead of placing the 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:

  • Highlight the headings and the data below them.
  • In the Excel ribbon, click Home > Sort & Filter > Custom Sort.
  • Next to Sort by, choose Date from the 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 or Year

    There are some cases where you might want to extract the month from the date 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 essential Excel functions and formulas that can help you do quick calculations or extract data from a cell. One such function is the MONTH function. Here's how to use the MONTH function to pull each entry’s month digit from the full date:

  • Add a column with Month as the header.
  • Click in the first empty cell under Month. In this case, it will be C2.
  • In 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, select the cell that contains the formula. In this case, it's C2. Then, click and drag the tiny green box that appears in the bottom corner of the cell, all the way 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. Then, right-click and select Sort & Filter > Custom Sort > Sort by Month.

    You can also use a function to sort the dates by year. Follow the instructions above, replacing the MONTH function with =YEAR(B2). The result will be a list of years in chronological 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. Here's how to use the TEXT function, so you can sort your dates by month and day afterward:

  • 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.
  • Again, go Home > Sort & Filter > Custom Sort to put your birthday list in order.
  • Since 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.

    • As an additional tip, you can use these different ways to strikethrough in Excel afterwards so that you can clearly view whose birthday has been celebrated in the spreadsheet.

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

    If you applied the methods discussed above, but Excel isn’t sorting by date 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 dropdown menu in the Excel ribbon and select a date format.

    Easy Ways to Sort by Date in Excel

    Sorting dates in the correct order may feel mundane, yet it’s a crucial task that must be done accurately. Therefore, leave it to Excel’s sorting feature to sort your dates effectively. 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. Now that you understand how easy it is to sort by date in Excel, you can move on to using templates with greater confidence.

    ncG1vNJzZmivp6x7rq3KnqysnZ%2Bbe6S7zGiqqKqkYq%2B6ecOaq55lmaN6psTCnqNo