按日期对数据进行排序似乎应该是一项简单的任务，事实上，Microsoft Excel提供了多种按日期对数据进行排序的方法。但是，有时 Excel 的排序工具无法正常工作，这可能会非常令人沮丧。 

以下是在Excel^(Excel)中按日期排序的几种方法，以及在日期排序不正确时尝试的技巧。

在Excel中对单列^{(Single Column)}日期^(Dates)进行排序 

在Excel^(Excel)中对日期进行排序的最简单方法是使用排序工具以您想要的顺序显示日期。 

1. 通过单击列标题选择日期列。在下图中，我们通过单击列标题A选择了该列。

1. 您也可以只选择列中包含日期的特定单元格。

1. 选择您的数据后，从主菜单中选择排序和筛选。^{(Sort & Filter)}

1. 现在选择Sort Oldest to Newest或Sort Newest to Oldest。在下面的示例中，我们从最新到最旧排序。请注意^(Notice)，Excel不会更改日期的格式，但会正确排序从最新开始的日期。

这种方法应该适用于绝大多数用例。

在Excel中按日期对^(Date)整个^(Entire)工作表进行排序

如果您的表包含多列数据，则您需要在排序时保留列之间的关系。您可以在下面看到 A 列中的日期和 B 列中的相应文本。 

如果您使用上述方法（即，仅选择日期单元格或整列日期，单击排序和筛选^{(Sort & Filter )}按钮，然后选择从最旧到最新^{(Sort Oldest to Newest)}排序或从最新到最旧排序^{(Sort Newest to Oldest)}），Excel将显示排序警告^{(Sort Warning)}并询问您是否要扩展选择以包括两列数据。

确保选中展开^{(Expand the selection)}选择并单击排序^(Sort)按钮。Excel将按照您选择的排序顺序对表中的所有数据进行排序。

可以看到，现在我们所有的日期都是从旧到新排序的，A 列中的日期和^{(Column A)}B^{(Column B)}列中的文本之间的关系被保留了下来。

在Excel中^(Excel)按月^(Month)、年^(Year)或月^(Month)和日^(Day)排序

如果您想按日期的特定部分（如月份或年份 ）在Excel中按日期排序怎么办？^(Excel)

在 Excel 中按月份对日期进行排序^{(Sorting Dates by Month in Excel)}

例如，在我们的人们生日示例表中，您可能想知道每个月谁出生，而忽略每个人的出生年份。您不能使用 Excel 的常规排序功能，因为它总是在其排序算法中包含年份。

为了解决这个问题，您可以创建一个提取月份的帮助列，以便您可以按该列排序。要从日期中提取月份，可以使用 Excel 的MONTH函数。

1. 创建一个新列
2. 在新列的第一个单元格中，输入=MONTH(A1)，其中 A1 是要从中提取月份的单元格。

1. 对每一行重复^(Repeat)步骤 2。Excel将从每个日期中提取月份，为您留下一列月份数字。

1. 现在您可以按带有月份编号的列对表格进行排序。在这种情况下，您将按 C 列排序。如果您只选择C ​​列^{(Column C)}中的数据，您将再次收到 Excel 的排序警告^{(Sort Warning)}。选择展开选择^{(Expand the selection)}并排序^(Sort)。

在 Excel 中按年份对日期进行排序^{(Sorting Dates by Year in Excel)}

如果要提取年份而不是月份，可以使用上述步骤，将MONTH函数替换为=YEAR(A1)，其中 A1 是包含要从中提取年份的日期的单元格。

在 Excel 中按月和日对日期进行排序^{(Sorting Dates by Month and Day in Excel)}

也许您想创建一个按月和日排序的生日列表，忽略年份。要从日期列中提取月份和日期，我们可以使用 Excel 的TEXT函数，它将日期转换为文本。我们将使用mm.dd格式。按照上述步骤，将MONTH函数替换为以下公式：=TEXT (A1, “mm.dd”)。

现在您可以按C 列^{(Column C)}排序，以按月和日获取每个人的生日列表。

Excel中的日期格式^{(Date Formats)}疑难解答

有时当您尝试在Excel中按日期排序时，您可能会发现无法让Excel将数据识别为日期。例如，想象一下^(Imagine)，有人给了您一个包含日期的电子表格。您已将数据放入Excel 中^(Excel)，但尝试上述方法时排序不正确。为什么它不起作用？

考虑以下日期列表：

它看起来很简单。但是，当您选择数据时，按Sort & Filter按钮，并选择从Oldest到Newest排序，它不起作用！你可能会得到这样的结果：

请注意^(Notice)，列中的最后一个日期没有正确排序。到底是怎么回事？ 

有可能即使单元格 A9 中的数据看起来^(looks)像日期，但它并没有真正格式化为日期，因此Excel无法正确排序。根据您使用的Excel版本，即使您通过选择单元格、右键单击并选择“设置单元格格^{(Format Cells)}式”并选择“日期^(Date)”选项将错误的单元格格式化为日期，仍然^{(still )}无法解决问题。

如何修复无法在 Excel 中正确排序的日期^{(How to Fix Dates That Won’t Sort Correctly in Excel)}

幸运的是，这个问题很容易解决。

1. ^{(Cut )}通过选择单元格并按Ctrl+x, 从主^{(Home )}菜单中选择Cut或右键单击单元格并选择Cut来剪切^(Cut)日期列。

1. 打开记事本^(Notepad)或其他纯文本编辑器。
2. 将日期粘贴^(Paste)到文本编辑器中。

1. 返回Excel，选择日期所在的列，右键单击并选择Format Cells。
2. 选择日期^(Date)并单击确定^(OK)。

1. 现在返回文本编辑器并选择并复制日期列表。

1. 返回 Excel并将日期粘贴^{(paste )}到您在步骤 5 中格式化的列中。
2. 现在你应该可以排序了。选择包含数据的列，按排序和筛选^{(Sort & Filter)}按钮，然后选择从最旧到最新排序^{(Sort Oldest to Newest)}。中提琴！有用！

Excel中的其他排序^(Sorting)和筛选技术

既然您已经了解了如何在Excel中按日期排序，您可能想要探索其他方法来对数据进行排序和筛选。这些文章将教你基本的单列和多列数据排序^{(basic one-column and multi-column data sorting)}，如何在 Excel 中按字母顺序排列^{(how to alphabetize in Excel)}，以及如何在 Excel 中过滤数据^{( how to filter data in Excel)}。 

How to Sort by Date in Excel

Sorting data by date seemѕ like it shоuld bе a simple task, and, indeed, Microsoft Excel offers a number of ways to sort data by date. However, sometimes Excel’s sorting tools don’t work correctly, and that can be very frustrating. 

Here are a few ways to sort by date in Excel, as well as a trick to try if dates aren’t sorting correctly.

Sorting a Single Column of Dates in Excel 

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

1. Select the column of dates by clicking on the column header. In the image below, we have selected the column by clicking on column header A.

1. You can also just select the specific cells in the column that have dates in them.

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

1. Now choose either Sort Oldest to Newest or Sort Newest to Oldest. In the example below, we’ve sorted from newest to oldest. Notice that Excel doesn’t change the format of the dates but does correctly sort the dates beginning with the newest.

This method should work in 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’ll want to preserve the relationships between columns when you sort. Below you can see dates in column A and corresponding text in column B. 

If you use the method above, (i.e., selecting only the date cells or the whole column of dates, clicking on the Sort & Filter button, and choosing to either Sort Oldest to Newest or Sort Newest to Oldest), Excel will display a Sort Warning and will ask you if you want to expand the selection to include both columns of data.

Make sure that Expand the selection is selected and click the Sort button. Excel will sort all the data in the table in the sort order you selected.

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

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

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

Sorting Dates by Month in Excel

In our example table of people’s birthdays, for example, you might want to know who was born in each month, ignoring the year each person was born. You can’t use Excel’s regular sort feature because it will always include the year in its sorting algorithm.

To get around this, you can create a helper column that extracts the month so you can then sort by that column. To extract a month from a date, you can use Excel’s MONTH function.

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

1. Repeat step 2 for each row. Excel will extract the month from each of the dates, leaving you with a column of month numbers.

1. Now you can sort the table by the column with month numbers. In this case, you’ll sort by column C. If you only select the data in Column C, you’ll get Excel’s Sort Warning again. Choose to Expand the selection and Sort.

Sorting Dates by Year in Excel

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

Sorting 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 the month and day from the column of dates, we can use Excel’s TEXT function, which converts dates into text. We’ll use the format mm.dd. Follow the steps above, replacing the MONTH function with this formula: =TEXT (A1, “mm.dd”).

Now you can sort by Column C to get a list of everyone’s birthdays by month and day.

Troubleshooting Date Formats in Excel

Sometimes when you try to sort by date in Excel, you might find that you can’t get Excel to recognize data as a date. Imagine, for example, someone gives you a spreadsheet that includes dates. You’ve put the data into Excel, but it isn’t sorting correctly when you try the methods above. Why isn’t it working?

Consider this list of dates:

It looks simple enough. However, when you select the data, press the Sort & Filter button, and opt to sort from Oldest to Newest, it doesn’t work! You might end up with something like this:

Notice that the last date in the column hasn’t been sorted correctly. What on earth is going on? 

Chances are that even though the data in cell A9 looks like a date, it isn’t really formatted as a date, so Excel can’t sort it correctly. Depending on which version of Excel you’re using, even if you format the errant cell as a date by selecting the cell, right-clicking and selecting Format Cells and choosing the Date option, that still won’t fix the problem.

How to Fix Dates That Won’t Sort Correctly in Excel

Fortunately, this problem is easy to fix.

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

1. Open Notepad or another plain text editor.
2. Paste the dates into the text editor.

1. Go back to Excel, select the column the dates used to be in, right-click and select Format Cells.
2. Choose Date and click OK.

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

1. Go back to Excel and paste the dates into the column you formatted in step 5.
2. Now you should be able to sort. Select the columns with data, press the Sort & Filter button, and choose Sort Oldest to Newest. Viola! It works!

Other Sorting and Filtering Techniques in Excel

Now that you’ve learned how to sort by date in Excel, you might want to explore other ways to sort and filter your data. These articles will teach you basic one-column and multi-column data sorting, how to alphabetize in Excel, and how to filter data in Excel. 

Related posts

• Excel中如何删除空行

• 如何使用 Excel 的朗读单元格功能

• 如何将 Excel 工作表插入 Word Doc

• 如何使用 Excel 的假设分析

• 如何在 Excel 中修复一行

• 如何在 Excel 中使用 PMT 函数

• 如何在安全模式下打开 Word 和 Excel

• 将 Excel 连接到 MySQL

• 如何在 Excel 中使用 Flash 填充

• 如何在 Excel 中编写 IF 公式/语句

• 在 Word 文档中查看和插入文档的最后修改日期

• 如何在 Excel 中跟踪依赖项

• 在 Excel 中复制公式时保留单元格引用

• 如何在 Excel 中删除网格线

• Google 表格与 Microsoft Excel – 有什么区别？

• 在 Excel 中将文本转换为数字的 5 种方法

• 何时在 Excel 中使用索引匹配而不是 VLOOKUP

• MS Excel 的高级 VBA 指南

• 在 Excel 中使用键盘更改行高和列宽

• 为什么应该在 Excel 中使用命名范围