Unable to sort pivot table by date

4
votes

I have a pivot table with 2 columns spanning dates (Create Date & Target Date). I am unable to sort any field within my pivot table, but I need to be able to sort the date fields. I have double checked that the format is date (when changed to number format, the date serial is shown and ISTEXT shows FALSE)

Photos of field list and relevant columns below. The bottom photo is just to drive home the point that the format is indeed date.
enter image description here enter image description here enter image description here enter image description here

Will this not sort due to all fields being displayed under rows? Is there another way to sort this with the current set up?

I am open to VBA solution as I already have a macro that loops through each pivot for other reasons.

1
excelvbapivot-table
I made up a mock table with data that I think should be similar to yours based on the field names. I was able to sort every column in the pivot table without trouble. I would check that you haven't enabled manual sorting, as I know that can cause the drop-downs to 'grey out.' i.stack.imgur.com/ViPwo.png - John Stark
@JohnStark manual filter was on, but the data will still not sort after removing. I added the sort option as a photo. Good suggestion. I really cannot figure out why this will not sort. Driving me crazy - urdearboy
Are you sure that manual filtering is turned off for all the other fields as well? I could see that locking created date up if all the other fields were essentially immobile. Other odd advice I've heard in the past, is try moving the created date field to the front of the chart (top of the rows stack), and see if that unlocks the drop-down menu. - John Stark
@JohnStark can confirm that is the issue. You can only sort the first column when the pivot is set up this way. The sort options after will only sort the subvalues that exist in the grouping. I cannot switch the column order so now im torn on what to do. - urdearboy
Awesome, yeah that works! - John Stark

1 Answers

4
votes

Posting a quick synopsis of what was uncovered in the comments:

tl;dr : If you have a pivot table in tabular form, sorts applied to columns will only apply to sub-grouped fields and will not sort all columns of the table (therefore the first column is the only field capable of reordering all rows of the table.


If you start with the table completely unsorted, and sort the first column, it will reorder all rows across all columns, because all other columns are sub-columns(children) of the first column:

Unsorted Starting Pivot Table unsorted pivot table


First Column Sorted Pivot Table First column sorted pivot table

Subsequently sorting the 2nd column (in this example), effectively does nothing to alter the table, because the values in Target Date are already tied to the values in Created Date 1-to-1. So they are locked to the sorting order chosen for column 1.

Sorting the third column in this case however, does alter the structure of the remaining fields, because there is a 2-to-1 relationship of Task Number to Target Date. The two versions of the table you can get out of sorting the third column follow. Notice that the are being sorted within the duplication of Target Date:

Task Number Sorted Largest to Smallest Third column sorted largest to smallest pivot table


Task Number Sorted Smallest to Largest Third column sorted smallest to largest pivot table