Forum Discussion
Cannot group that selection in a pivot table
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.
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
dd/mm/yyyy;;
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:
I have a similar problem and have followed the instructions that you have provided but did not get a win
FYI Pivot table data source is set to include row 39.
Pivot table filter displays the 0 however does not recognise date format.
