Forum Discussion
dharbison
Feb 02, 2023Copper Contributor
Cannot group that selection in a pivot table
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...
Riny_van_Eekelen
Feb 03, 2023Platinum Contributor
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:
Simon_Fish
May 20, 2024Copper Contributor
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.
- Riny_van_EekelenMay 20, 2024Platinum Contributor
Simon_FishWell, the fact that the filter dialogue box says you can Sort A to Z or Z to A suggests that you are 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.