Cannot group that selection in a pivot table

Copper Contributor

Hi there.  I have a formula auto-calculating the next date to a column on which I have a pivot table.  I have verified that the dates are valid based on the calendar and that there are no unallowable values that are not formatted as a date. What's also strange is that in the pivot table filter, the box for the blank cells isn't tagged as 'Blank' as it normally is when the cells are blank, but if fact, there is no tag. Further, when I uncheck the box that should be tagged as 'Blank', a little red rectangle appears. I have never seen this before, and cannot understand why I can't group the section. Lastly, I am able to group other pivot tables in that same worksheet from the same source data set, so it's really not an issue of traditional vs. OLAP-based Power Pivot.  I added some screen shots below of the data and what I'm seeing.  Any insight would be appreciated! Thanks.









6 Replies

@dharbison For the grouping to work ALL rows must be dates or truly  blank. And when you have blanks, the will show up in a group "less than the earliest date in the list". See picture below.

Screenshot 2023-02-03 at 04.56.14.png

Since you mention that the date column is calculated, I assume you have formula that returns either a date or and empty string (""). An empty string is NOT blank. That's why you see the item with no label and you can not group de dates anymore.



Thank you.  My formula =IF(H3=3,D3+90,IF(H3=6,D3+180,IF(H3=9,D3+270,IF(H3=12,D3+360,""))))

is returning "" .  I guess I've never before tried to group a selection with "" in it.


Thanks for your help!

@dharbison Not sure if it's acceptable to you but a workaround could be to return 0 in stead of "". That will create a non-sense date 0 January 1900. Use a custom format to display dates, for instance, as 


The two semi-colons at the end force a 0-date to be displayed as a blank. Then, the pivot table will still group dates by year, quarter and month. Filter out the year 1900 and you can create something like this:






That worked PERFECTLY!  Thanks so much!





I have a similar problem and have followed the instructions that you have provided but did not get a win :sad:

FYI Pivot table data source is set to include row 39.

Pivot table filter displays the 0 however does not recognise date format.Pivot2.jpgpivot error.JPG

@Simon_Fish Well, the fact that the filter dialogue box says you can Sort A to Z or Z to A suggests that you are not dealing with texts that merely look like dates. With real dates it will say:


Select the 'dates' in P2:P39. Then, on the Data ribbon select Text-to-columns and press Finish. So, just Finish. change nothing. That should convert the text dates into real dates. Refresh the pivot table and it shall work.

Save your file first, though, so that you don't loose your data if it goes wrong.