PivotTable Grouping with Exceptions

[pdevito3]

Hey Everyone,

I have several pviot tables organizing data based on month and year with grouping. At this point, the data does not order properly unless there is a date in every single field. Is there a way to have a Pivot table group a set of data by dates and have it exclude a value when there is not a date. For example, a date could either be there or TBD. Is it possible to have it group all of the dates by month and year and have a seperate column for TBD?

Thanks

 

[Jerry Sullivan]

Hi pdevito3,

As far as I know, grouping by months will only work if all the data in the grouped by field are dates. If you have any non-dates including text, blanks, or errors then the field can't be grouped.

One workaround would be to replace TBD's with a dummy date like 1/1/1900 (or 12/31/9999 depending on which side you want TBD values to be when sorted).

After initially refreshing your PivotTable and grouping by Month and Year, change the caption value of the dummy year 1900 to "TBD". The PivotTable will retain this mapping to display 1900 as "TBD" even after you modify your source data and refresh the pivot.

Revising the caption for the Month is more problematic, because if you modify the "Jan" for 1/1/1900 to "TBD" it will remap Jan for any other years displayed to also read "TBD".
One option to hide this special month's caption would be to use the custom number format to blank the cell using a Conditional formatting rule to be applied to the cell below the cell with the value "TBD".