Forum Discussion
Pivot table and date sorting/grouped by months/years when empty cells in selected data
Hi, I am just starting to use pivot table and I have to say it si so helpful! I am not able to group my date data per month/year. In my selected column of date I have many empty cells, this seems ...
Yes,
Step 1 - Split your Date Column like this
15-March-2022 | Year(Date) | Text(Date, "MMM")
So you should have an helper column in years and Months
Then Load the table to Pivot Table > Drag the Month Field to Rows and Years To Column Fields and Amount to Values Field.
Does your formula for the dates have blanks? For whatever reason, the pivot recognizes a true blank cell differently than a formula that returns "". True blanks don't affect grouping but the formula ones will not allow you to group.
You could try using PowerQuery to do the data cleaning and then your output will have true blanks and thus be able to be manipulated & grouped correctly through a pivot.
Step 1 - Split your Date Column like this
15-March-2022 | Year(Date) | Text(Date, "MMM")
So you should have an helper column in years and Months
Then Load the table to Pivot Table > Drag the Month Field to Rows and Years To Column Fields and Amount to Values Field.
Does your formula for the dates have blanks? For whatever reason, the pivot recognizes a true blank cell differently than a formula that returns "". True blanks don't affect grouping but the formula ones will not allow you to group.
You could try using PowerQuery to do the data cleaning and then your output will have true blanks and thus be able to be manipulated & grouped correctly through a pivot.
Donald_Genes_ All my blank cells are "true blank", I checked with the function Isblank(), but still not able to group dates. Do you have any suggestions? Thank you in advance
Do you have dates (which are actually numbers) or texts which looks like dates? You may check by ISNUMBER(). Also, do you have any other texts in Date column?
SergeiBaklan I have the date column formatted as date. When I insert a pivot table and try to group the dates it tells me the error "cannot group...". I attach here a similar scenario.
It doesn't matter how Date column is formatted. If you apply date or number format to the text, it still will be a text. Formatting doesn't convert the value.
Here is the sample with dates (=ISNUMBER() returns TRUE) and texts which looks like dates
Again, please be sure you have dates, not texts.
