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.
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.
Best Answer
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
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
Task Number Sorted Smallest to Largest