How to extract month and year from a date in Excel

A month-year label with TEXT

The sheet is an order log that runs from January 2025 to April 2026. TEXT turns a date into text using a format code: mmm is the short month name, yyyy the four-digit year. It works on dates with a time of day too, like A6 (January 27, 2026 at 14:35).

Other codes for the same date: "mmmm yyyy" gives January 2026, "mmm yy" gives Jan 26, "mm/yyyy" gives 01/2026 and "yyyy-mm" gives 2026-01. Pick the last one for file names and anything that will be sorted as text, because year-first labels sort in date order.

Two things to know. The result is text, so it lines up on the left and cannot be used in date maths. And TEXT format codes follow the language of your Excel: in a German version the year code is JJJJ, not yyyy, so a shared formula can show the wrong thing on another person's computer.

Month and year as numbers: MONTH and YEAR

For filters, comparisons and maths you want numbers, not text. =MONTH(A6) returns 1 and =YEAR(A6) returns 2026; the time of day is ignored. Put them in two columns when someone wants to filter on "month 1" and "2026" separately.

MONTH alone is a trap once your data spans more than a year. =SUMPRODUCT((MONTH(A2:A9)=1)*C2:C9) returns 1,875, because it adds January 2025's 340 to January 2026. Add the year: =SUMPRODUCT((YEAR(A2:A9)=2026)*(MONTH(A2:A9)=1)*C2:C9) returns 1,535.

To keep both in one number, multiply the year by 100 and add the month. 202601 sorts and compares correctly, and it is easy to read in a lookup table.

The best grouping key: the 1st of the month

Column D holds =EOMONTH(A2,-1)+1, filled down. EOMONTH(A2,-1) is the last day of the previous month, and +1 moves to the 1st of this month. Format the column as mmm yyyy (Ctrl+1 > Number > Custom, type mmm yyyy) and it reads like a label, but every value is a real date. =DATE(YEAR(A2),MONTH(A2),1) gives the same result if you find it easier to read.

Because every order in a month gets exactly the same date, the key groups cleanly. =SUMIFS(C2:C9,D2:D9,D5) totals January 2026 to 1,535 and =COUNTIF(D2:D9,D5) counts its 2 orders. A PivotTable on column D shows one row per month, in date order, and you can still do date maths on it.

Watch out for =A2-DAY(A2)+1, a formula often suggested for the first of the month. It keeps the time of day: for A6 it gives January 1, 2026 at 14:35, which does not equal D5, so that order drops out of the January group. EOMONTH and DATE both drop the time.

Why text labels sort in the wrong order

Build a month list from text labels and Excel sorts it like words: =SORT(UNIQUE(TEXT(A2:A9,"mmm yyyy"))) returns Apr 2026, Dec 2025, Feb 2026, Jan 2025, Jan 2026. The same thing happens in a PivotTable or a sorted table built on a text label column.

Sort the real dates first, then turn them into labels. With the key column in place, SORT and UNIQUE work on dates and TEXT only changes how the finished list looks. Without a helper column, =TEXT(SORT(UNIQUE(DATE(YEAR(A2:A9),MONTH(A2:A9),1))),"mmm yyyy") does the same. Both need Excel 365 or 2021.

If you must keep text labels, use the "yyyy-mm" format: 2025-01, 2025-12, 2026-01 sort correctly as text.

A custom format only changes the look

Formatting the order dates themselves as mmm yyyy makes column A look like months, and that is all it does. The cells still hold the full dates (and A6 its time). Formulas, UNIQUE and lookups see eight different values, not five months.

Use the format when you just want a tidy display and keep the full dates. Use the 1st-of-the-month key from the section above when you want to group by month.

1. Select the dates.
2. Press Ctrl+1 and choose Number > Custom.
3. Type mmm yyyy in the Type box and click OK.

No formulas: group a PivotTable by months and years

For a monthly summary you do not need a helper column at all. Excel 2016 and later group a date field automatically when you add it to Rows; you can also do it yourself.

Select both Months and Years. With Months alone, January 2025 and January 2026 are merged into one "Jan" row, the same trap as MONTH without YEAR. If Group is greyed out or says it cannot group that selection, the column contains a text date or another non-date entry; see the guide on converting text to dates.

1. Click inside the data and choose Insert > PivotTable > OK.
2. Drag Order date to Rows and Amount to Values.
3. Right-click any date in the PivotTable and choose Group.
4. Select Months and Years (click both), then OK.

Which method to pick

A label in a report or a heading: TEXT with "mmm yyyy". Something to filter or compare as numbers: MONTH and YEAR, never MONTH on its own across years. Anything you will group, sort, total or chart by month: the 1st-of-the-month date from EOMONTH or DATE, formatted as mmm yyyy. A one-off summary: a PivotTable grouped by Months and Years.