How to sort rows by date in excel

How to sort rows by date in excel. how to sort by date in excel 2010.

Sorting data by date seems like a simple task, and indeed Microsoft Excel offers several ways to sort data by date. However, sometimes Excel’s sorting tools do not work correctly, and this can be very frustrating.

Here are some ways to sort by date in Excel, as well as a trick to try if dates are not sorted correctly.

Sorting a Single Column of Dates in Excel

The easiest way to sort dates in Excel is to use the sort tool to display the dates in the order you want.

Select the date column by clicking the column heading. In the image below, we have selected a column by clicking the column heading A.

You can also just select specific cells in a column that have dates.

With your data selected, choose Sort & Filter from the main menu.

Now select Sort Oldest to Newest or Newest to Oldest. In the example below, we have sorted from newest to oldest. Note that Excel does not change the date format, but it sorts the dates correctly starting with the newest.

This method should work for the vast majority of use cases.

Sorting an Entire Worksheet by Date in Excel

If your table has more than one column of data, you need to preserve the relationship between the columns when sorting. Below you can see the dates in column A and the corresponding text in column B.

If you use the above method (ie, selecting only date cells or the entire date column, clicking the Sort & Filter button and choosing either Sort Old to New or Sort New to Old), Excel will display a sorting Warning and you will be asked whether you want to expand the selection to include both columns of data.

how to sort date in excel using formula

Make sure the Expand Selection check box is selected and click the Sort button. Excel will sort all the data in the table in the order you choose.

You can see that now all of our dates are sorted from oldest to newest, and the relationship between the dates in column A and the text in column B is preserved.

Sorting by Month, Year, or Month and Day in Excel

What if you want to sort in Excel by date by a specific part of the date, for example, month or year?

Sort dates by month in Excel

For example, in our table of people’s birthdays, you can find out who was born in each month, ignoring each person’s year of birth. You cannot use the normal Excel sort function because it always includes the year in its sorting algorithm.

To work around this, you can create a helper column that retrieves the month so that you can then sort on that column. To extract month from the date you can use the Excel MONTH function.

Create a new column
In the first cell of the new column, enter = MONTH (A1), where A1 is the cell from which you want to extract the month.

Repeat step 2 for each line. Excel will extract the month from each date, leaving a column with the month numbers.

Now you can sort the table by the month column. In this case, you will sort by column C. If you select the data in only column C, you will get the sorting warning from Excel again. Select to expand the selection and sort.

Sort dates by year in Excel

If you want to extract the year instead of the month, you can use the steps above by replacing the MONTH function with = YEAR (A1), where A1 is the date cell from which you want to extract the year.

Sort dates by month and day in Excel

Perhaps you want to create a sorted list of birthdays by month and day, ignoring the year. To extract month and day from a date column, we can use the TEXT function in Excel, which converts dates to text. We will use the mm. dd format. Follow the steps above, replacing the MONTH function with the following formula: = TEXT (A1, â€œmm. ddâ€).

You can now sort by Column C to get a list of all birthdays by month and day.

Troubleshooting Date Formats in Excel

Sometimes when you try to sort data in Excel by date, you may find that you cannot get Excel to recognize the data as a date. Imagine, for example, that someone gives you a spreadsheet with dates. You put the data in Excel, but using the methods above, it doesn’t sort correctly. Why does not it work?

Consider this list of dates:

Looks simple enough. However, when you select data, click the Sort & Filter button and choose to sort from oldest to newest, it doesn’t work! You might end up with something like this:

Note that the last date in the column is not sorted correctly. What the hell is going on?

Most likely, the data in cell A9 looks like a date, but it is not actually formatted as a date, so Excel will not be able to sort it correctly. Depending on which version of Excel you are using, even if you format the erroneous cell as a date by selecting the cell, right-clicking, choosing Format Cells, and choosing the Date option, this still won’t solve the problem.

How to fix dates not sorted correctly in Excel

Fortunately, this problem is easy to fix.

Cut a column of dates by selecting the cells and pressing Ctrl + x, choosing Cut from the Home menu, or right-clicking the cell (s). ) and choosing “Cut”.

Open Notepad or another text editor.
Paste dates into a text editor.

Return to Excel, select the column you want the dates to be in, right-click, and select Format Cells.
Select Date and click OK.

Now go back to your text editor and select and copy the list of dates.

Go back to Excel and paste the dates into the column you formatted in step 5.
You can now sort. Select the data columns, click the Sort & Filter button, and select Sort Oldest to Newest. Alto! Works!

Other Sorting and Filtering Techniques in Excel

Now that you’ve learned how to sort by date in Excel, you can explore other ways to sort and filter data. In these articles, you will learn the basics of sorting data by one and more columns, alphabetical order in Excel, and filtering data in Excel.

–