There's a quiet frustration hiding in that Reddit post, and it's one we've all felt at some point. The user did everything right: they formatted the cells to show "mmm-year," copied values, and still the pivot table insists on treating January 10 and January 30 as different things. The problem isn't their effort. It's that they're fighting the spreadsheet's underlying data model, not the display. And that's the real lesson here.
When you change the number format to "mmm-year," you're only changing what you see. The underlying serial number, the full date with the day intact, is still sitting there in the formula bar. Pivot tables don't care about appearances. They group by the actual value, so two dates in the same month but different days will always be separate rows until you give them something genuinely shared to group on. The user tried copying and pasting values, which is a reasonable instinct, but it only pastes the same underlying serial numbers unless you also convert to text or use a function like `TEXT()` to strip the day out entirely.
Here's what this means for you, practically. If you want your pivot table to roll up by month and year, you need to create a helper column that contains only the month and year as a real value, not a format. Use something like `=TEXT(A1,"mmm-yyyy")` or `=DATE(YEAR(A1),MONTH(A1),1)`. The second option is better because it keeps a true date you can sort chronologically, while the first gives you clean text labels. Either way, that new column becomes your row label, and suddenly January 10 and January 30 collapse into a single "Jan-2026" row. No more fighting the days.
This is the part of spreadsheet work that trips people up: the difference between what a cell shows and what it is. The tool isn't being difficult on purpose. It's being literal. And once you understand that, you stop wrestling with formats and start designing your data for the output you actually need. The fix is simple, but it requires a shift in mindset from "make it look right" to "make the underlying values right." That's the difference between a spreadsheet that merely displays data and one that works with you.
So next time you're stuck on a pivot table, don't ask why the format isn't sticking. Ask what the data actually contains, then reshape it to match your question. That's the move that turns a frustrating afternoon into a clean, repeatable workflow. Start with a helper column, keep your dates real, and let the pivot table do what it does best: summarize, not guess.