Column of dates from an exported report. In the pivot, right click, Group is greyed out for the date field, and the rows show every single day as its own line. If I type a date manually into a blank cell it looks identical to the imported ones. About 4,000 rows, one date column, no blanks that I can see.
Also check for blank cells inside the source range, not just at the bottom of it. And make sure the pivot source is a table or a dynamic range — if the range says A1:F3000 and you now have 4,000 rows, the pivot silently ignores the rest and you will get stranger problems later.
Once the underlying data is fixed, add a helper column with =EOMONTH(A2,0) or =TEXT(A2,"yyyy-mm") and group on that instead. It survives refreshes and it sorts correctly, which the built-in grouping does not always do when your data spans multiple years.
The yyyy-mm version sorting correctly is the bit people miss. Month names on their own sort alphabetically and the chart looks insane.
@xlookup_lena · 2w ago · 3 replies
Your dates are text, or there is at least one non-date value hiding in the column. Grouping fails if a single cell in the field is text or blank. Two checks: select the column and compare the status bar count of numbers against the count of values — if the numeric count is lower, some cells are not real dates. Then run
=ISNUMBER(A2)down the column and filter for FALSE. Real dates are numbers underneath, which is also why they align right by default while text dates align left. That alignment difference is the two second visual check.Reply
Report
@xlookup_lena · 2w ago
I would bet they are in the other date order, and your locale rejected exactly the ones where the day number is above 12. That produces this pattern almost every time.
Reply
Report
@sightread_sid · 2w ago
Numeric count was 3,976 against 4,000 values. So 24 rogue rows.
Reply
Report