I have a 2-column spreadsheet which contains Dates and Sales figures as follows:
我有一个包含日期和销售数据的两栏电子表格，如下所示:
When doing Right click -> Format Cells the values in the Dates column properly appear as Date (in the M/D/YYYY format). This applies to all the Dates cells with the exception of the header (I checked with Ctrl+Shirt+Down).
当右键单击->格式单元格时，日期列中的值将正确地显示为日期(以M/D/YYYY格式)。这适用于所有日期单元格，除了标题(我使用Ctrl+Shirt+Down进行了检查)。
However when I create a pivot table from the 2 columns, the Dates are recognized as text and are sorted accordingly (i.e. 1st sorted by month, then by day, then by year) which messes up my data:
然而，当我从两个列中创建一个pivot表时，日期被识别为文本，并按相应的顺序进行排序(即按月排序，然后逐日排序，然后按年排序)，这使我的数据变得混乱:
I create pivot tables with this type of data on a regular basis and never had this issue before, and I really don't see what's wrong there.
我经常使用这种类型的数据创建数据透视表，以前从来没有遇到过这个问题，我真的不知道哪里出了问题。
How can I force the date values to be recognized as such when creating pivot tables?
在创建pivot表时，如何强制将日期值识别为这样?
PS: I have uploaded the pivot_table_date_porder_issue.xlsx file which exhibits the problem for whoever wants to see it.
PS:我上传了pivot_table_date_porder_issue。xlsx文件，它向任何想看的人显示问题。
6 个解决方案
#1
9
Your problem is that excel does not recognize your text strings of "mm/dd/yyyy" as date objects in it's internal memory. Therefore when you create pivottable it doesn't consider these strings to be dates.
你的问题是，excel不识别你的“mm/dd/yyyy”的文本字符串作为对象的内部内存。因此，当您创建数据透视表时，它不认为这些字符串是日期。
You'll need to first convert your dates to actual date values before creating the pivottable. This is a good resource for that: http://office.microsoft.com/en-us/excel-help/convert-dates-stored-as-text-to-dates-HP001162867.aspx
在创建数据透视表之前，需要先将日期转换为实际的日期值。这是一个很好的资源:http://office.microsoft.com/en-us/excel-help/convert-dates-stored as-text- hp001162867.aspx
In your spreadsheet I created a second date column in B with the formula =DATEVALUE(A2). Creating a pivot table with this new date column and Count of Sales then sorts correctly in the pivot table (option becomes Sort Oldest to Newest instead of Sort A to Z).
在您的电子表格中，我使用公式=DATEVALUE(A2)在B中创建了第二个日期列。使用这个新的日期列创建一个pivot表并计算销售额，然后在pivot表中进行正确的排序(选项将排序从最老的到最新的，而不是从a到Z)。
#2
2
April 20, 2017
2017年4月20日
I've read all the previously posted answers, and they require a lot of extra work. The quick and simple solution I have found is as follows:
我已经阅读了所有之前发布的答案，它们需要很多额外的工作。我找到的简单快捷的解决办法如下:
1) Un-group the date field in the pivot table. 2) Go to the Pivot Field List UI. 3) Re-arrange your fields so that the Date field is listed FIRST in the ROWS section. 4) Under the Design menu, select Report Layout / Show in Tabular Form.
1)取消数据透视表中的日期字段。2)转到Pivot字段列表UI。3)重新排列字段，以便在row部分中首先列出日期字段。4)在设计菜单下，选择报表布局/表格显示。
By default, Excel sorts by the first field in a pivot table. You may not want the Date field to be first, but it's a compromise that will save you time and much work.
默认情况下，Excel在数据透视表中的第一个字段排序。你可能不想把日期字段放在首位，但这是一种妥协，可以节省你的时间和工作量。
#3
1
If you want to use a column with 24/11/15 (for 24th November 2015) in your Pivot that will sort correctly, you can make sure it is properly formatted by doing the following - highlight the column, go to Data – Text to Columns – click Next twice, then select “Date” and use the default of DMY (or select as applicable to your data) and click ok
如果你想使用一个列24/11/15(2015年11月24日)在你的主能正确排序,您可以确保它是正确格式化的通过以下——突出列,去数据-文本列单击Next两次,然后选择“日期”,使用默认的DMY(或选择适用于您的数据)并单击ok
When you pivot now you should see it sorting properly as we have properly formatted that column to be a date field so Excel can work with it
现在，当您使用pivot时，您应该会看到它进行了正确的排序，因为我们已经正确地将该列格式化为一个日期字段，以便Excel能够使用它
#4
0
Try creating a new pivot table, and not just refreshing.
尝试创建一个新的pivot表，而不仅仅是刷新。
I had a case where I forgot to add in a few dates. After adding them in I updated the pivot table range and hit refresh. They appeared at the end of the pivot table, out of order. I then tried to simply create a new pivot table and the dates where all in order.
我有一个案子，我忘了加上几个日期。在添加它们之后，我更新了pivot表范围并点击refresh。他们出现在透视表的末端，出了顺序。然后我尝试简单地创建一个新的pivot表，并按顺序列出日期。
#5
0
You need to select the entire column where you have the dates, so click the "text to columns" button, and select delimited > uncheck all the boxes and go until you click the button finish.
您需要选择具有日期的整个列，因此单击“文本到列”按钮，并选择分隔的>取消选中所有框，直到单击按钮finish。
This will make the cell format and then the values will be readed as date.
这将使单元格格式，然后值将作为日期读取。
Hope it will helped.
希望它能帮助。
#6
0
Go into options. You most likely have 'Manual Sort" turned on. You need to go and change to radio button to "ascending > date". You can also right click the row/column, "more sorting options". It took me forever to find this solution...
进入选项。你很可能打开了“手动排序”。您需要切换到单选按钮到“升序>日期”。您还可以右键单击行/列，“更多排序选项”。我花了很长时间才找到这个解决方案……
#1
9
Your problem is that excel does not recognize your text strings of "mm/dd/yyyy" as date objects in it's internal memory. Therefore when you create pivottable it doesn't consider these strings to be dates.
你的问题是，excel不识别你的“mm/dd/yyyy”的文本字符串作为对象的内部内存。因此，当您创建数据透视表时，它不认为这些字符串是日期。
You'll need to first convert your dates to actual date values before creating the pivottable. This is a good resource for that: http://office.microsoft.com/en-us/excel-help/convert-dates-stored-as-text-to-dates-HP001162867.aspx
在创建数据透视表之前，需要先将日期转换为实际的日期值。这是一个很好的资源:http://office.microsoft.com/en-us/excel-help/convert-dates-stored as-text- hp001162867.aspx
In your spreadsheet I created a second date column in B with the formula =DATEVALUE(A2). Creating a pivot table with this new date column and Count of Sales then sorts correctly in the pivot table (option becomes Sort Oldest to Newest instead of Sort A to Z).
在您的电子表格中，我使用公式=DATEVALUE(A2)在B中创建了第二个日期列。使用这个新的日期列创建一个pivot表并计算销售额，然后在pivot表中进行正确的排序(选项将排序从最老的到最新的，而不是从a到Z)。
#2
2
April 20, 2017
2017年4月20日
I've read all the previously posted answers, and they require a lot of extra work. The quick and simple solution I have found is as follows:
我已经阅读了所有之前发布的答案，它们需要很多额外的工作。我找到的简单快捷的解决办法如下:
1) Un-group the date field in the pivot table. 2) Go to the Pivot Field List UI. 3) Re-arrange your fields so that the Date field is listed FIRST in the ROWS section. 4) Under the Design menu, select Report Layout / Show in Tabular Form.
1)取消数据透视表中的日期字段。2)转到Pivot字段列表UI。3)重新排列字段，以便在row部分中首先列出日期字段。4)在设计菜单下，选择报表布局/表格显示。
By default, Excel sorts by the first field in a pivot table. You may not want the Date field to be first, but it's a compromise that will save you time and much work.
默认情况下，Excel在数据透视表中的第一个字段排序。你可能不想把日期字段放在首位，但这是一种妥协，可以节省你的时间和工作量。
#3
1
If you want to use a column with 24/11/15 (for 24th November 2015) in your Pivot that will sort correctly, you can make sure it is properly formatted by doing the following - highlight the column, go to Data – Text to Columns – click Next twice, then select “Date” and use the default of DMY (or select as applicable to your data) and click ok
如果你想使用一个列24/11/15(2015年11月24日)在你的主能正确排序,您可以确保它是正确格式化的通过以下——突出列,去数据-文本列单击Next两次,然后选择“日期”,使用默认的DMY(或选择适用于您的数据)并单击ok
When you pivot now you should see it sorting properly as we have properly formatted that column to be a date field so Excel can work with it
现在，当您使用pivot时，您应该会看到它进行了正确的排序，因为我们已经正确地将该列格式化为一个日期字段，以便Excel能够使用它
#4
0
Try creating a new pivot table, and not just refreshing.
尝试创建一个新的pivot表，而不仅仅是刷新。
I had a case where I forgot to add in a few dates. After adding them in I updated the pivot table range and hit refresh. They appeared at the end of the pivot table, out of order. I then tried to simply create a new pivot table and the dates where all in order.
我有一个案子，我忘了加上几个日期。在添加它们之后，我更新了pivot表范围并点击refresh。他们出现在透视表的末端，出了顺序。然后我尝试简单地创建一个新的pivot表，并按顺序列出日期。
#5
0
You need to select the entire column where you have the dates, so click the "text to columns" button, and select delimited > uncheck all the boxes and go until you click the button finish.
您需要选择具有日期的整个列，因此单击“文本到列”按钮，并选择分隔的>取消选中所有框，直到单击按钮finish。
This will make the cell format and then the values will be readed as date.
这将使单元格格式，然后值将作为日期读取。
Hope it will helped.
希望它能帮助。
#6
0
Go into options. You most likely have 'Manual Sort" turned on. You need to go and change to radio button to "ascending > date". You can also right click the row/column, "more sorting options". It took me forever to find this solution...
进入选项。你很可能打开了“手动排序”。您需要切换到单选按钮到“升序>日期”。您还可以右键单击行/列，“更多排序选项”。我花了很长时间才找到这个解决方案……