Excel MONTH Function: Syntax, Examples and Tips

Part of the free Module 5: Excel Formulas and Functions · Function 59 of 105 · Full Excel course

The Excel MONTH function returns the month of a date as a number from 1 (January) to 12 (December). It reads the date’s serial number, so it works on real dates, date-time stamps and text Excel can recognise as a date. MONTH is used to group and summarise data by month, calculate quarters and fiscal periods, and rebuild dates together with YEAR and DAY.

Table of Contents

Syntax

=MONTH(serial_number)

Arguments

Argument	Required / Optional	Meaning
serial_number	Required	The date to read: a date cell, a function such as TODAY() or DATE(), or recognisable date text such as “2026-09-04”.

How MONTH works

Excel stores dates as sequential serial numbers, and MONTH converts the number back into a calendar date and returns the month part only. =MONTH("2026-09-04 18:30") returns 9; the time is ignored. The result is a plain integer, so it can be compared, summed and used in lookups. MONTH returns #VALUE! when the input is text that is not a date, and #NUM! for negative numbers. It never returns the month name; that requires TEXT.

Step-by-step example: monthly sales summary

Column A holds transaction dates and column B the amounts.
In C2 enter =MONTH(A2) and fill down. Each row now shows 1 to 12.
In E2:E13 type the numbers 1 to 12 and in F2 enter =SUMIF($C$2:$C$500,E2,$B$2:$B$500).
Fill F2 down. Column F shows the total for each month. Add =TEXT(DATE(2026,E2,1),"mmmm") in D2 to display the month names beside the numbers.

Practical use cases

1. Month name instead of number

=TEXT(A2,"mmmm")

Returns September. Use “mmm” for Sep. The result is text, so sort by the MONTH number, not the name.

2. Calendar quarter

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

3. Fiscal year starting in April

=YEAR(A2)+(MONTH(A2)>=4)

Dates from April onwards belong to the fiscal year that ends the following calendar year.

4. Year-month key for sorting and PivotTables

=YEAR(A2)*100+MONTH(A2)

Returns 202609 for September 2026, a numeric key that sorts correctly across years.

5. Total for the current month, any year

=SUMPRODUCT((MONTH($A$2:$A$500)=MONTH(TODAY()))*$B$2:$B$500)

Common mistakes and tips

Expecting the month name: MONTH returns 1 to 12. Wrap the date in TEXT for names.
Result formatted as a date: a cell in date format shows 9 as 09/01/1900. Set it to General.
Dates stored as text: a left-aligned column of dates needs DATEVALUE or Text to Columns before MONTH works reliably.
Grouping across years: MONTH alone merges September 2025 and September 2026. Combine it with YEAR when the data spans more than one year.
Regional text dates: “04/09/2026” is read by system settings. Real date cells avoid ambiguity.

MONTH compared with TEXT, EOMONTH and PivotTable grouping

MONTH gives a number, which is what formulas need for comparisons, SUMIF criteria and sort keys. TEXT(A2,”mmmm”) gives a name, which is what reports need for labels, but the name sorts alphabetically (April before January) and cannot be used in arithmetic. EOMONTH returns a full date at the month end, which is preferable when the summary must distinguish years automatically, because 30/09/2025 and 30/09/2026 are different values. PivotTables can group a date field by months and years without any helper column, and that is the quickest option for one-off analysis; MONTH remains the choice for formula-driven dashboards, SUMIFS models and data-validation rules where the month number must be available in a cell.