Pivot table Dates - extracting Month

I hope this question belongs here as this was the only one I could find that seem to deal with Pivot Tables. If this is not the appropriate forum, then I do hope the mods will move it to the appropriate one. I am using Excel 365 and Win 10 Pro.

I have one spreadsheet with a set of data following grocery prices for the last 6 months in 2021 and then another spreadsheet that overs the first 4 months of 2022. In the 2021 spreadsheet, when I create a Pivot table to summarize data by Dates, I can get a breakdown of the Date into Months, so that I can see a monthly summary - this is what I want. Then, I started a second spreadsheet for 2022 with the grocery expenses for that year. When I tried to do a Pivot table by Date and extract the Month from the Date to get a monthly report, I could not get this to work, so all I got was the individual dates (week dates). I am really puzzled why the ability to extract Month from the Date in one Pivot table based on date from the 2021 spreadsheet will give me a monthly summary, whereas this ability to extract the Month from the Date in the 2022 date is not possible. I have checked closely and the two spreadsheets have the data stored in the same columns and in the same format.

Any suggestions as to what I might do to achieve the same results with the 2022 spreadsheet that I have with the 2021 spreadsheet, will be greatly appreciated. Thanks.

WesternGuy

If you right click on the 2022 Pivot - Date field - and select Group -- and you receive a Dialog that says "cannot group by..." this implies one or more values in the source range are not dates; conversely, if you can group, select Years & Months and "OK" -- this would indicate that the app settings have been changed such that new Pivots are no longer grouping automatically {this setting can be found within the Data area of Excel Options -- "Disable automatic grouping of Date/Time..."}

XLent, thanks for the reply. When you say that "this implies one or more values in the source range are not dates", does this mean that, if I get the response you indicate, then I can "fix it" by reformatting all the dates as "Dates"?

WesternGuy

hi, formatting a cell doesn't change the underlying data type -- you need to coerce the values (assuming they're valid, of course)

first, just double check that your Pivot Range is only referencing the range it should be...

if it is... try entering 0 in a blank cell, copying it, highlighting your date range then, Paste Special -> Operation: Add and click OK; then retry the grouping

if the grouping is still not working you might want to post a sample of your troublesome date values for someone to take a look at.