Group Dates by Month with a Helper Column

Excel & Google Sheets

How it works

TEXT converts the date into a label using a format code — "yyyy-mm" turns 2026-07-21 into "2026-07". Every date in the same month produces the identical label, which is exactly what pivot tables, SUMIF, and COUNTIF need to group on. Putting the year first is the trick: "2026-07" sorts chronologically even though it's text, whereas month names like "Jul" sort alphabetically (Apr, Aug, Dec…). These format codes work identically in Excel and Google Sheets, so the formula travels between platforms without changes.

B2

The date to label.

"yyyy-mm"

The format code: four-digit year, dash, two-digit month — so months sort in calendar order.

When to use it

Add this helper column before building a pivot table, a SUMIF-per-month summary, or a monthly chart. Pair it with SUMIF(month column, "2026-07", amounts) for instant monthly totals.

Common mistakes

• ✕Using a month-only code like "mmm" and getting alphabetical order.

  ✓"Jul" and "Aug" sort as text: Apr, Aug, Dec… Lead with the year — "yyyy-mm" — and the labels sort chronologically and keep different years separate.

• ✕Treating the result as a date.

  ✓TEXT returns text, so date math and date filters won't work on it. Keep the original date column for calculations and use this column only for grouping.

• ✕The formula returns the date unchanged or errors.

  ✓B2 is probably text, not a real date. Check with =ISNUMBER(B2) — if FALSE, convert the column with DATEVALUE first.