Date & Time

MONTH Function in Excel

Returns the month of a date as a number from 1 to 12.

Syntax

  • =MONTH(serial_number)

Arguments

  • serial_number (required): The date to extract month from

Examples

  • =MONTH(TODAY()) - Current month number - Result: 1
  • =TEXT(A1, "MMMM") - Month name from date - Result: January
  • =MONTH(A1)=MONTH(B1) - Check if same month - Result: TRUE/FALSE

Excel MONTH function documentation

  • MONTH returns the month number (1–12) from a date serial.
  • Syntax: =MONTH(serial_number). Core building block for seasonal reports and fiscal filters.
  • Combine with [YEAR](/functions/year/) and [DAY](/functions/day/) for full date parts.
  • Related: [EOMONTH](/functions/eomonth/), [EDATE](/functions/edate/), [TEXT](/functions/text/).

MONTH syntax and display

  • serial_number: valid Excel date or datetime.
  • MONTH returns number — use TEXT(A2,"mmmm") for month name label.
  • Fiscal year offset: =MOD(MONTH(A2)+FY_Start-1, 12)+1 custom patterns.
  • #VALUE! on unparsed text dates.

Step-by-step: monthly sales flag column

  • Step 1 — Order dates in column A, amounts in B.
  • Step 2 — =MONTH(A2) in C for month index.
  • Step 3 — Summarize with [SUMIFS](/functions/sumifs/)(B:B, C:C, 6) for June.
  • Step 4 — Better: criteria on date column >=DATE(YEAR(TODAY()),6,1) for precision.
  • Step 5 — Chart by month using pivot or UNIQUE+SUMIFS stack.

MONTH vs TEXT vs MONTH in SUMIFS

  • MONTH — numeric filter helper column.
  • TEXT — month name for labels.
  • Direct SUMIFS on dates — preferred over MONTH helper when possible.
  • Category: [Date & Time](/categories/date-time/).

Worked examples to copy

  • Month number: =MONTH(TODAY()).
  • Same month last year: =MONTH(A2)=MONTH(EDATE(TODAY(),-12)).
  • Q1 check: =MONTH(A2)<=3.
  • Month name: =TEXT(A2,"mmmm").

People also ask

  • MONTH on datetime? — Ignores time; uses calendar month of date part.
  • MONTH text date? — Use DATEVALUE if needed.
  • MONTH vs MONTH(TODAY()) in conditional format? — Common rolling month highlight.
  • MONTH in Google Sheets? — Same =MONTH(date).

Common errors

  • Returns number 1-12, not name
  • Use TEXT for month name

Use cases

  • Monthly analysis
  • Seasonal grouping
  • Date validation

Frequently asked questions

  • How do I get month name instead of number? Use TEXT: =TEXT(A1, "MMMM") for full name (January), =TEXT(A1, "MMM") for abbreviation (Jan). Or CHOOSE: =CHOOSE(MONTH(A1), "Jan", "Feb", ...).
  • How do I group data by month? Create Month column: =MONTH(Date) or =TEXT(Date, "YYYY-MM") for year-month. Use in pivot tables or SUMIFS for monthly totals.
  • How do I calculate months between dates? Use DATEDIF: =DATEDIF(Start, End, "M") for complete months. Or approximate: =(YEAR(End)-YEAR(Start))*12 + MONTH(End)-MONTH(Start).
  • How do I find the first/last day of a month? First day: =DATE(YEAR(A1), MONTH(A1), 1). Last day: =EOMONTH(A1, 0) or =DATE(YEAR(A1), MONTH(A1)+1, 0).
  • How do I check if a date is in a specific month? Use =MONTH(A1)=3 for March. For month and year: =AND(MONTH(A1)=3, YEAR(A1)=2026). Or =TEXT(A1,"YYYY-MM")="2026-03".

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use MONTH

  • Monthly analysis — common Date & Time scenario for MONTH.
  • Seasonal grouping — common Date & Time scenario for MONTH.
  • Date validation — common Date & Time scenario for MONTH.

MONTH in the Date & Time category

  • Browse all Date & Time functions at /categories/date-time/ for related formulas.
  • MONTH syntax: =MONTH(serial_number)
  • serial_number (required): The date to extract month from
  • Confirm MONTH arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm MONTH arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • YEAR (/functions/year/): Returns the year of a date as a four-digit number.
  • DAY (/functions/day/): Returns the day of a date as a number from 1 to 31.
  • DATE (/functions/date/): Creates a date from year, month, and day components.
  • EOMONTH (/functions/eomonth/): Returns the last day of the month, a specified number of months before or after a date.

Errors to watch for

  • Returns number 1-12, not name — review causes on linked error pages in the directory.
  • Use TEXT for month name — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter MONTH on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

MONTH worked examples to copy

  • =MONTH(TODAY()) — Current month number. Expected result: 1.
  • =TEXT(A1, "MMMM") — Month name from date. Expected result: January.
  • =MONTH(A1)=MONTH(B1) — Check if same month. Expected result: TRUE/FALSE.

MONTH reference summary for crawlers and offline review

  • MONTH belongs to the Date & Time category in Excel. Returns the month of a date as a number from 1 to 12.
  • Full syntax: =MONTH(serial_number). Open /functions/month/ for parameters, FAQs, and related pages.
  • Common mistakes: Returns number 1-12, not name; Use TEXT for month name
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.