Fixing date aggregation in pivot

Hi,
I am trying to aggregate the data in sheet 1 into a pivot table but when the dates don't aggregate by month.

For example it will show a total of resources for dates 11/12/2022, 15/12/2022 and 17/12/2022. But it won't show the total for the month.

I'd added into cell A21 (below the pivot table) of how I would like to show the pivot table with the months aggregated.

Is there a way to do this? I've tried formatting the source column dates and doing text-to-column which does not seem to help!. I'm sure its because excel is not reading the dates properly so it is not giving me the option to break down the dates by day/month/year but I'm stomped!

Any advice is appreciated.

Originally Posted by dflak

Quite often the solution to Pivot Table problems can be solved with the source data. In this case, getting the last day of the month for the Planned Displacement Date.

In this case, I converted your data into an Excel table because Excel Tables know how big they are. So when I added the helper column to the source data, the pivot table picked it up when it was refreshed.

The helper column has the formula: =IF(NOT(ISNUMBER([@[Planned Displacement Date]])),"No Date",EOMONTH([@[Planned Displacement Date]],0)) - You can replace "No Month" with the null string "" if you like.

Interesting! Many thanks bro! I added the helper column the main spreadsheet but I didn’t convert the data source into a table. I just expanded the column range to include the helper column in the pivot table and it worked like a charm!