Grouping Dates by Month or Quarter in a Pivot Table

Group dates in a pivot table by month, quarter or year, fix the Cannot group that selection error, handle fiscal quarters, and do the same grouping in SQL.

6 min read

Summarize in AI

Quick Summary

Right-click a date in the pivot, choose Group, and select Months, Quarters and Years. If Excel says it cannot group the selection, the date column holds text or blanks. Fiscal quarters are not built in, so add a helper column, or group with date_trunc in SQL.

Key takeaways

Right click, Group offers seconds up to years, and selecting Years together with Months keeps January of one year separate from January of the next.
Cannot group that selection means the field has blank cells, text or errors in it, and the fix is to repair the source column rather than the pivot.
Excel groups into calendar quarters only, so a fiscal year starting in another month needs a helper column in the source or a SQL expression.
Grouping in the pivot hides the logic in a click, while a month column made in SQL or Power Query can be reused, tested and read.
Dates stored as text will not group, and converting them in Power Query with an explicit locale is the reliable repair.

Sales by month is the first pivot most people build, and the date grouping is the first thing that goes wrong. The dates appear as a daily list, the Group command is greyed out or errors, or two Januaries merge into one. The behavior is consistent once you know what Excel needs from the column. This post covers the grouping steps, the error and its causes, fiscal quarters, and the SQL expressions that produce the same buckets. It is a spoke of pivot tables and their SQL equivalents.

Group by month, quarter and year

Build the pivot with the date field in Rows and a value such as revenue in Values. Newer Excel versions group dates automatically as soon as you drop a date field, adding Years, Quarters or Months fields to the list. If yours did not, right-click any date in the Rows area and choose Group. The dialog lists: Seconds, Minutes, Hours, Days, Months, Quarters, Years. Select the levels you want and click OK.

The choice has an effect people miss. With only Months selected, Excel puts all Januaries together, whatever the year: January 2024 and January 2025 become one row. With Months and Years selected, you get a Years field and a Months field, and the months nest under each year. With Quarters and Years you get Q1 to Q4 under each year. Select Years whenever the data covers more than twelve months.

The grouped fields appear in the field list as new fields (Years, Quarters, Date). Drag them wherever you like. To undo the grouping, right-click a grouped item and choose Ungroup.

For a day range such as weeks, choose Days, set Number of days to 7 and Excel starts at the date in the Starting at box. That box matters: the first week begins where you say, so set it to a Monday if weeks should run Monday to Sunday.

Cannot group that selection

The error message reads "Cannot group that selection". It means Excel found something in the field that is not a date. The usual causes:

A blank cell in the date column. Even one blank in a thousand rows blocks the grouping.
Text that looks like a date. 2025-03-14 imported from a CSV may be text and left-aligned in the cell. Excel's grouping needs real date serial numbers (Excel stores dates as serial numbers).
An error value such as #N/A or #VALUE! in a formula result.
A pivot built on the Data Model, where the field has to be of Date type in the model, not text.

Find the offenders with a filter: in the source, filter the date column for blanks, then use =ISNUMBER(A2) in a helper column to flag the non-dates. Fix the source, then refresh the pivot. Grouping persists only if the field stays valid; a new text date in next month's data will break it again, which is why repairing the column in the query beats repairing it by hand each month (converting Excel serial dates to real dates and dealing with text that looks like a date).

The most dependable repair is in Power Query: set the column type to Date, with a locale if the text format is ambiguous. That makes the pivot source clean on every refresh. For blanks, filter out rows with no date or replace them with a sentinel date you can find, since a revenue row with no date is a data problem worth seeing.

Fiscal quarters

Excel groups quarters on the calendar: Q1 is January to March. A company whose fiscal year starts in April, July or October cannot get its quarters from the Group dialog. There is no setting for it. Two ways around:

A helper column in the source. Add a column that computes the fiscal quarter. For a year starting in April:

Text

=CHOOSE(MONTH([@Date]), "Q4","Q4","Q4","Q1","Q1","Q1","Q2","Q2","Q2","Q3","Q3","Q3")

and a fiscal year column that adds one when the month is April or later. Put both fields in the pivot instead of the grouped dates.

A date dimension. In the Data Model, a calendar table with one row per day and columns for month, fiscal quarter and fiscal year gives every pivot the same definitions. This is what BI tools do, and it prevents two reports from disagreeing about when Q1 starts.

Either way, write down the rule. "Fiscal year 2025 starts 1 April 2024" is a definition, and a helper formula is how you encode it.

The same buckets in SQL

A date in SQL becomes a bucket by truncating it. In PostgreSQL:

SQL

SELECT date_trunc('month', order_date)::date   AS month,
       date_trunc('quarter', order_date)::date AS quarter,
       SUM(quantity * unit_price)              AS revenue
FROM orders
GROUP BY 1, 2
ORDER BY 1;

date_trunc('month', ...) returns the first day of the month, so the result sorts correctly and keeps the year. For MySQL, which lacks date_trunc, use DATE_FORMAT(order_date, '%Y-%m-01') for the month and CONCAT(YEAR(order_date), '-Q', QUARTER(order_date)) for the quarter. SQL Server has DATETRUNC from version 2022; earlier versions use DATEFROMPARTS(YEAR(d), MONTH(d), 1).

A fiscal quarter is a small expression too. For a year beginning in April in PostgreSQL, shift the date by nine months and take the quarter:

SQL

SELECT EXTRACT(YEAR FROM order_date + INTERVAL '9 months')    AS fiscal_year,
       EXTRACT(QUARTER FROM order_date + INTERVAL '9 months') AS fiscal_quarter
FROM orders;

A date in April 2024 plus nine months is January 2025, so it lands in fiscal year 2025, quarter 1. That matches the rule above. Putting the expression in a view makes it a single definition for everyone (date functions in Excel versus SQL date functions).

Months with no data

A pivot shows only months that have rows. If March had no sales, March is missing, and a chart of the pivot jumps from February to April. The Show items with no data option helps only in some layouts. In SQL, generate the months and left join:

SQL

SELECT m.month, COALESCE(SUM(o.quantity * o.unit_price), 0) AS revenue
FROM generate_series('2025-01-01'::date, '2025-12-01', interval '1 month') AS m(month)
LEFT JOIN orders o ON date_trunc('month', o.order_date) = m.month
GROUP BY m.month
ORDER BY m.month;

Empty months now show as zero. That is a different statement from "not reported", so decide which you mean before filling.

When pivot date grouping is not enough

Weeks that cross year boundaries. ISO weeks need a helper column (WEEKNUM(date, 21) in Excel) and agreement on which year week 53 belongs to.

Time zones. A timestamp near midnight lands in different days depending on the zone. Convert in the database before bucketing.

Rolling windows. Last twelve months or year to date compare periods that are not calendar buckets. Use window functions or measures.

Several reports must agree. A click in one pivot is not a definition. Put the bucket in SQL or in a date table.

See Microsoft's page on grouping or ungrouping data in a PivotTable and the PostgreSQL reference for date_trunc. To check a small export as JSON, use the CSV to JSON converter.

PostgreSQL lists the other bucket functions on its page for date and time functions.

Also inData Analysis

Taggedanalysis how-to