Get the Month from a Date in Excel: number or name

Reports are by month; dates are by day. These formulas pull the month out so you can group and total.

Month as a number (1–12)

1. Dates in column A.
2. Click B1. Type =MONTH(A1). Enter.

29-Sep-2026 → 9.

Month as a name

=TEXT(A1, "mmm")    → Sep
=TEXT(A1, "mmmm")   → September
=TEXT(A1, "mmm yyyy") → Sep 2026

Use the last one for grouping — it keeps different years apart.

Year and quarter

=YEAR(A1)
="Q" & ROUNDUP(MONTH(A1)/3, 0)

Total sales per month

Dates in A, amounts in B, month label in C (from the TEXT formula). Then:

=SUMIF(C:C, "Sep 2026", B:B)

Or skip the helper column with a pivot table — it groups dates by month on its own: pivot table.

First and last day of the month

=EOMONTH(A1, -1) + 1   → first day
=EOMONTH(A1, 0)        → last day

Check it worked

Sorting by the TEXT result sorts alphabetically (Apr, Aug, Dec…). For calendar order sort by the original date, or use =TEXT(A1,"yyyy-mm").

Common mistake

#VALUE! — the date is text. Retype it, or =MONTH(DATEVALUE(A1)).