11

I have a 2-column spreadsheet which contains Dates and Sales figures as follows:

enter image description here

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).

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:

enter image description here

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?

PS: I have uploaded the pivot_table_date_porder_issue.xlsx file which exhibits the problem for whoever wants to see it.

Max
  • 12,794
  • 30
  • 90
  • 142

10 Answers10

12

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.

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

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).

Tim
  • 35,413
  • 11
  • 95
  • 121
  • 3
    Your solution does solve the problem, however I still can't understand why I am having this issue in the first place. I have other spreadsheets with dates in the same format and everything's fine, very weird. – Max Feb 20 '13 at 09:04
6

April 20, 2017

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.

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.

Ty N.
  • 61
  • 1
  • 1
2

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

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

ianlee50
  • 21
  • 2
2

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...

Helpful
  • 21
  • 1
2

The problem I had was that although the source data was correctly formatted as 'date' dd/mm/yyyy, the pivot table placed (for example) 22/05/2019 between 16/05/2019 and 17/05/2019. This data was visible in the pivot table, but in the wrong place. In addition, the Pivot chart refused to show that data for that date even though the 'Date' filter allowed it to be selected.

In my case, I had to:

  1. From the Pivot Chart,open the 'Date' Filter menu.

  2. select the 'Sort Oldest to Newest' option.

  3. Bingo! The Pivot chart now shows the missing date data.
Mikev
  • 2,012
  • 1
  • 15
  • 27
Steve
  • 21
  • 1
  • This translates to VBA for my date field, 'DT': `ActiveWorksheet.PivotTables("PivotTable1").PivotFields("DT").AutoSort xlAscending, "DT"` – Valid May 14 '20 at 16:49
0

Try creating a new pivot table, and not just refreshing.

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.

Disnof
  • 1
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.

This will make the cell format and then the values will be readed as date.

Hope it will helped.

Luca Palermo
  • 1
0

I saw this somewhere else. I am using 2016 Excel. What worked for me was to use YYYY Quarters (I was looking for quarterly data). So, I had the source data sorted as YYYY xQ. 2016 1Q, 2016 2Q, 2016 3Q, 2016, 4Q, 2017 1Q, 2017 2Q... You get the idea.

user9944315
  • 321
  • 2
  • 4
0

Right-click and Ungroup at Date areas works wonder for me.

Djoko Triono
  • 1
  • 1
    Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jan 07 '22 at 04:45
0

Right click in the Date column and go to group options and simply add an unwanted group and then remove it. This will fixed the sorting issue without losing any slicers you may have included. Hope this helps.

Pasindu Sulochana
  • 1