Group Dates by Month and Year in Excel Pivot Tables

Lowfield Interiors took £134,200 in January 2026 against a £150,000 forecast. The board pack read £182,400, called it £32,400 ahead of plan, and paid the depot a £3,400 bonus on the strength of it. Not one figure in the file had been typed wrong.

The pivot's date field was grouped by Months and not by Years. The £48,200 the depot took in January 2025 was sitting on the same row, twelve months after it was banked and reported.

A month label without a year is not a month. Jan is a bucket twelve wide, and every January in the file falls into it.

01The Month That Happened Twice

Column D is the row label the pivot showed and column E is the label it should have shown. Both Januaries carry the same label in column D, twelve months apart.

The two 2025 orders in rows 2 and 3 are real revenue, banked and closed in last year's accounts. Grouped by Months alone they are also part of this January, because Jan is the whole of what the row label says.

Nothing in the file is double counted. The pivot moved £48,200 of last year onto this year's row, the grand total never moved, and the reconciliation at the bottom of the pack passed on the way out of the door.

02What Grouping a Date Field Actually Does

Grouping changes nothing in your data. It adds fields to the pivot cache and relabels the rows.

Right-click any date in the pivot and choose Group, and the dialog offers Seconds, Minutes, Hours, Days, Months, Quarters and Years. The list is multi-select: Ctrl-click to take more than one.

Pick Months and Years together and the field list gains a Years field, while the original date field keeps its name and now holds Jan to Dec. That split is the part worth knowing.

Pick Months on its own and there is no Years field to drag in later. The date field holds twelve labels, and nothing in the pivot can tell 2025 from 2026.

03Group by Years and Months, Never Months Alone

The fix for the board pack was one Ctrl-click. Right-click a row label, choose Ungroup, then group again with both Years and Months selected.

Order in the Rows box decides what the table reads like. Years above the date field gives 2025 > Jan; the other way round gives Jan > 2025, which is twelve headings of two rows each and no running order anybody can follow.

The Starting at and Ending at boxes default to the first and last date in the data. Leave them alone: a hand-typed start date goes stale the moment the file gains a row, and the rows outside it collapse into a single bucket at each end.

Scope. Grouping by Years uses calendar years, and no Starting at date changes that. For a year that runs April to March, add a column with =YEAR(B2)+(MONTH(B2)>=4) and put that in Rows instead.

A pivot built on the Data Model cannot group dates at all. The Group command is greyed out, and the answer there is a date table in Power Pivot rather than a right-click.

04Cannot Group That Selection

Excel refuses to group a date field that holds anything other than dates. One text date in 480 rows is enough, and so is one blank cell.

The message names nothing, so count your way to the offenders. =COUNT(B2:B481) counts real dates, =COUNTA(B2:B481) counts every non-empty cell, and =COUNTBLANK(B2:B481) counts the gaps between them.

One cell says it outright: =IF(COUNT(B2:B481)<COUNTA(B2:B481),"Text dates","Clean"). A gap of eleven is eleven cells to fix, not a pivot to rebuild.

Text dates convert in a helper column. =VALUE(TRIM(B2)) handles text already written in the sheet's own date order, and =DATEVALUE(TRIM(B2)) handles the rest; TRIM takes off the trailing space that usually caused the problem.

For a column holding both kinds, =IFERROR(DATEVALUE(TRIM(B2)),B2) converts the text and leaves the real dates alone. Paste the result over the original as values, format it as a date, refresh, and the Group dialog stops refusing.

05Grouping Lives in the Cache, Not the Pivot

A pivot's grouping belongs to the cache behind it rather than to the sheet you are looking at. Two pivots on the same source usually share one cache, so grouping one groups the other.

That is how a signed-off report changes by itself. Somebody groups the summary tab by Quarters on Thursday, and the monthly tab is quarterly when it is opened on Monday.

Copy a pivot and the copy shares the original's cache, which is the most common way it happens. Building a second pivot from the same range usually shares it too, because Excel reuses a cache it already has.

The old wizard is the way out. Press Alt, then D, then P, and it asks whether to base the new pivot on the existing one. Answer no and it keeps its own grouping, at the cost of a second copy of the data in the file.

06Days, Weeks and the Grouping That Will Not Mix

Days is the only option that takes a number. Set Number of days to 7 and the pivot groups into weeks.

It will not combine with anything else. Choosing Days with a number greys out Months, Quarters and Years, so a weekly table carries no year label of its own and a two-year file runs on in one long list.

The buckets start at whatever is in Starting at, which defaults to the earliest date in the data. A file that opens on a Tuesday gets weeks that run Tuesday to Monday, and they will not line up with anybody else's week.

For real weeks, add a column and group nothing. =B2-WEEKDAY(B2,3) returns the Monday of that date's week, and it sorts, filters and subtotals like the date it is.

07The Helper Column Two Years Cannot Collapse

The sturdiest pivot is one that never needs the Group dialog. Add a month column to the source and group nothing at all.

=EOMONTH(B2,0) returns the last day of the month a date falls in: 31/01/2025 for one January and 31/01/2026 for the other. Format the column mmm yyyy and the pivot shows Jan 2025 and Jan 2026 as the separate months they are.

It is a real date underneath, so it sorts in order, filters by range and feeds SUMIFS without a lookup. =TEXT(B2,"yyyy-mm") does the same job as text, which also sorts correctly but cannot be compared against a date.

Two more columns cost nothing and answer most of what a pack asks: =YEAR(B2) and =MONTH(B2), one of each, ready for any slicer or filter somebody wants later.

08The Same Report Without a Pivot Table

A monthly table is four formulas, and formulas cannot regroup themselves overnight.

=SORT(UNIQUE(EOMONTH($B$2:$B$481,0))) spills the month ends in order, one row for each month the data actually contains. Format that column mmm yyyy and it is the left-hand side of the report.

Beside it go the total and the count, both bounded by the month rather than labelled with it:

=SUMIFS($D$2:$D$481,$B$2:$B$481,">="&EOMONTH($F2,-1)+1,$B$2:$B$481,"<="&$F2)
=COUNTIFS($B$2:$B$481,">="&EOMONTH($F2,-1)+1,$B$2:$B$481,"<="&$F2)

The forecast comes alongside on the same month ends: =XLOOKUP($F2,$H$2:$H$13,$I$2:$I$13), or =INDEX($I$2:$I$13,MATCH($F2,$H$2:$H$13,0)) in a workbook that has to open in an older Excel.

To see the rows a collapsed label was hiding, spill them: =FILTER($A$2:$D$481,(YEAR($B$2:$B$481)=2025)*(MONTH($B$2:$B$481)=1)) returns the two orders that turned £134,200 into £182,400.

09Three Checks Before the Pack Goes Out

Three formulas catch every version of this before anybody reads the pack.

The span. =MAX($B$2:$B$481)-MIN($B$2:$B$481) returns the days the file covers. More than 365 of them in a twelve-row monthly table means at least one row is holding two years.

The count. =COUNT($B$2:$B$481) against =COUNTA($B$2:$B$481). Equal, and the column is dates all the way down; unequal, and the Group dialog is about to refuse.

The row. =SUMPRODUCT(--(YEAR($B$2:$B$481)=2025),--(MONTH($B$2:$B$481)=1),$D$2:$D$481) returns £48,200.00 here: the amount last January is adding to this one.

And the check that catches nothing: =SUM($D$2:$D$481) matches the pivot's grand total whether the grouping is right or wrong, because grouping moves rows between labels and never loses one.

10Eight Things That Bite

1. Months without Years. Twelve rows, every year in the file folded into them, and a grand total that still reconciles.
2. Dragging Years out of the Rows box. The table halves, the figures change, and Excel says nothing at all.
3. Automatic date grouping. Excel groups a date field the moment it lands in Rows, and the fields it adds are easy to remove by accident. File > Options > Data switches it off.
4. A text date. One cell in 480 and the Group dialog refuses the lot with "Cannot group that selection".
5. A blank date. The same message, harder to find, because a blank cell looks like nothing rather than like the wrong thing.
6. A shared cache. Grouping one pivot regroups every pivot built on the same source, on every sheet in the file.
7. Seven-day groups. They start at the earliest date in the data, so a file that opens on a Tuesday keeps Tuesday weeks all year.
8. Calendar years only. Grouping by Years cannot start in April, whatever the contract says.

11Mini Exercises

1. Group a two-year date column by Months alone and count the rows. Expect 12 rather than 24.
2. Put =EOMONTH(B2,0) beside the dates, format it mmm yyyy and use it in Rows. Expect Jan 2025 and Jan 2026 on separate lines.
3. Type an apostrophe in front of one date, refresh and try to group. Expect "Cannot group that selection" and no grouping at all.
4. Compare =COUNT(B2:B481) with =COUNTA(B2:B481). Expect a gap of exactly the number of text dates in the column.
5. Total January twice: read the pivot row, then write =SUMIFS(D2:D481,B2:B481,">="&DATE(2026,1,1),B2:B481,"<"&DATE(2026,2,1)). Expect £182,400.00 and £134,200.00.

What to Take Away

Nothing in this workbook was mistyped. Four hundred and eighty orders were dated correctly, the pivot added them correctly, and the grand total at the bottom of the pack was right to the penny.

One row label was a month with no year on it. That is the whole of the £48,200, the bonus and the month that was reported as ahead of plan while it was £15,800 short.

So group a date field by Years and Months together, or better, give the source a month-end column and never open the dialog. A label that cannot tell 2025 from 2026 will eventually be asked to.