Forum Discussion

SergSlim's avatar
SergSlim
Copper Contributor
Jan 31, 2025

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?

  • SergSlim's avatar
    SergSlim
    Copper 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

    • SergSlim's avatar
      SergSlim
      Copper 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_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    Date grouping pivot tables only works when all entries are dates. Empty cells and/or empty strings like "" will block that feature. 

    • SergSlim's avatar
      SergSlim
      Copper 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.

    • SergSlim's avatar
      SergSlim
      Copper 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 :(

  • 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...?

Resources