Pivot table error when trying to refresh

[Indywar2]

Link to spreadsheet:Upload file 20-Apr-2024.xlsx

Hi everyone,

I am having problems with an excel file and pivot tables. I have included a copy above. I seem to have problems every year or so with this pivot table, so if anyone has a better suggestion/different program to store data and then recall the information I would greatly appreciate any suggestions. Please be mindful this is for a hobby so I am not looking for a commercial (expensive) alternative to excel , but I would be interested if there is a better way of collecting this data and retrieve it.

Background:
This file contains data from a hobby of mine, metal detecting. On the spreadsheet titled "Coins Found" I have recorded the date found, coins found, year of coin and location. I have a few formulas in this spreadsheet:

• Column E calculates the year of the coin (removing the mint mark from the original entry)

• Column F calculates the age of the coin when found

• Column G that calculates the year range

• Column H calculates the quantity found during a single day

In the 2nd spreadsheet in this file labeled "summary-Location-Date categories" the pivot table that starts at A54 has been giving me problems about 1x per year. This time I can't seem to figure out how to fix it.

• When I place my cursor in cell A54b and select "refresh" the pivot tables changes from the current format of Years > Month>total coins found during that month to showing the date (M/dd/yyyyy) for every entry on the 1st spreadsheet.

Any advice how to fix this?

Thanks for any advice.
Rob

[romperstomper]

You have (nonsensical) text in cell A3066 which prevents that field from being recognised as a date field, so your groupings don't work any longer. Clear that cell and you should be able to refresh without issue.

[Indywar2]

Thank you for the help. The fixed the problem. I would have never found that on my own.

Thanks,
Rob