Forum Discussion
SergSlim
Jan 31, 2025Copper Contributor
PivotTable does not group column with mix of formulas and dates
Hello,
I have a table, which has a column with Dates, which I enter manually.
And I have a PivotTable using this table as Source. I added column with dates to Rows and Grouped to show Years and months only.
It was working fine, until I decided, that I need Pivot Table to use another list of dates instead.
I added another column (Stat) to Source table. In most rows I use formula: =IF([@Date]="";"";[@Date])
However, in few rows I enter date manually, because it has to be different from the original date.
Now I wanted to change the Rows of Pivot Table to use Stat instead of Date column.
But I faced a few issues. First of all, it adds an empty row to the filter of Stat field. This was not the case for Date field. Maybe it's because I actually have formulas instead of actually empty cells.
But that's not the main problem as I can simply unselect it in the filter.
The main problem, is that I can't Group rows by Year, Month, etc. It does not consider the data to be dates, or to be of the same type or whatever.
Is there a way to fix this, so I can actually group the Stat by Year and Month?
- SergSlimCopper Contributor
Here's an example.
Tab Expenses - does not have an IF formula in Stat column. Empty lines do not have any formulas at all.
Pivot (date) tab - has a PivotTable that has Date from source table used as Columns, Grouped by date. So that I can see months and years and not each date seprately.Tab Expenses (formulas in Stat col) - has IF formula in Stat column copied in whole column. Few cells have specific date instead of the formula (they are highlighted with yellow).
Pivot (Stat) tab - has a PivotTable that uses Stat from source table (from Expenses (formulas in Stat col) sheet) instead of Date as Columns. I can't Group it by Years and Months.
I'd add helper column to the source as
which returns only two values.
We may filter PivotTable on this value to exclude blank dates. That could be classic filter or slicer
Slicer itself could be moved on another sheet, as variant into hidden one, if we always use TRUE only.
Even better not to keep blank rows in structured table, it expands automatically if you add new data at the bottom.
As workaround you may use
=IF([@Date]=""; 1; [@Date])
With that everything with skipped date will be under Jan 01, 1900
- SergSlimCopper Contributor
This wouldn't work, because I don't want to see anything in the cells when actual Date is empty. In this case, Date is empty, while Stat would have 01.01.1900.
- Riny_van_EekelenPlatinum Contributor
Date grouping pivot tables only works when all entries are dates. Empty cells and/or empty strings like "" will block that feature.
- SergSlimCopper Contributor
Eventually I decided to remove the IF formulas from Stat column, leaving it simply =[@Date] and I copy it from previous line each time, and fill in with another date when I need it to be different. Now Grouping in PivotTable works.
- SergSlimCopper Contributor
Ok, I removed formulas from empty lines.
Now Source table has:
- a lot of lines that use formula =IF([@Date]="";"";[@Date]) and actually result in a date taken from Date column. None of the formulas result in empty string.
- few lines that have actual date written in them instead of formula
- few empty lines without formulas or anything in them
Grouping is still unavailable in Pivot Table :(
- NnyiimockBitanyanmiBrass Contributor
I love pivotTables. Can you possibly share the sheet or some dummy data screenshot or workbook.
what about you try to use GROUPBY function in Excel/Pivotby...?