Excel 2021: Pivot Table not recognising dates where source table is dynamic and only has formulas

[BrerRabbit]

I have a source table that is only populated by formulas, there is no hard data. It's also dynamic so a few rows will only have formula but no retrieved data.

The difficulty is dates in pivot tables and graphs: if there are empty rows then the dates column won't group or be recognised as dates. However, if I put dates in the empty cells in the date column so that there are no blanks (=if(get date,date,use really early date)) then this really early date is used in the pivot table itself, skewing the data.

NOTE: without the empty rows the dates are recognised and are treated as dates.

I've tried using a dynamic array formula but this formula recognises that each formula leaves an empty string bcuz of the iferror function. If I don't use this then #n/a is left instead.

Dynamic Array formula: =tblClassRoster[[#Headers],[CALENDAR CODE]]:INDEX(tblClassRoster[[#Headers],[CALENDAR CODE]]:tblClassRoster[#All],COUNTA(tblClassRoster[[#Headers],[CALENDAR CODE]]:tblClassRoster[[#All],[CALENDAR CODE]]),COUNTA(tblClassRoster[[#Headers],[CALENDAR CODE]]:tblClassRoster[#Headers]))

From:

Is there a dynamic array formula that will differentiate between an empty string and a "full" or "not empty" string ie retrieved data?

 

[RoryA]

COUNTA will include formulas that return "". If you only need to count dates, use COUNT instead as that only counts numbers. (Add 1 if you need to include the header row)

 

[BrerRabbit]

COUNTA will include formulas that return "". If you only need to count dates, use COUNT instead as that only counts numbers. (Add 1 if you need to include the header row)

unfortunately it contains numbers and letters and empty strings

 

[RoryA]

Why does your date column contain all that junk?

 

[BrerRabbit]

Why does your date column contain all that junk?

I'm using a dynamic array that includes counting teh date column. the array contains all that junk

 

[RoryA]

But if you use count on just the date column, you will be able to determine the correct number of rows to return.

 

[BrerRabbit]

countif(range,">""") instead of counta worked a treat. I just literally figured it out.