Excel MONTH Function

MONTH extracts the month number from a date, returning a value between 1 and 12. It doesn't care about the day or the year in that date, only the month. One thing that trips up new users right away: MONTH always returns a number, never a month name like "March." Getting the name requires a different function entirely, which we'll cover below.

Syntax

=MONTH(serial_number)

Parameter	Required	Description
`serial_number`	Yes	The date you want to pull the month from. Can be a cell reference, a date typed directly in quotes, or the result of another formula like `TODAY()`. Excel stores dates internally as serial numbers, so this argument just needs to resolve to a valid one.

⚠

MONTH accepts text dates like =MONTH("3/14/2026"), but this is risky. On a computer set to day/month/year regional settings, that same text string gets read as March 14 or the 3rd of the 14th month, depending on interpretation, and either fails or returns the wrong answer. Always reference a cell that already contains a real date value instead of typing a date as text inside the formula.

Basic Example

You're building a monthly sales report and need to know which month each order fell in.

=MONTH(C2)

// C2  = the order date, 3/14/2026

MONTH reads the date in C2 and returns 3. It ignores the day (14) and the year (2026) completely. Drag that formula down a column of order dates and you've got a month number for every row, ready to feed into a SUMIFS or a pivot table.

How MONTH Works

It returns a whole number, never text

MONTH always returns an integer from 1 to 12. It never returns "Jan," "January," or any text representation of the month. If you see a month name in a cell, that cell contains a different formula, not MONTH.

It reads any valid date, not just typed dates

MONTH works identically whether the input is a formatted date, a raw serial number, or the output of another date formula. =MONTH(44927) and =MONTH(DATE(2023,1,1)) both return 1, because 44927 is the serial number Excel uses internally for January 1, 2023.

It ignores time-of-day values

A date-and-time value like 3/14/2026 6:45 PM still returns 3 from MONTH. The time portion lives in the decimal part of the serial number and has no effect on the month extraction.

It doesn't validate the year

MONTH will happily extract a month from a date decades in the past or future. There's no upper or lower bound check beyond what Excel's date system itself supports (dates from 1900 forward). It will not, however, produce a sensible result from a negative or non-date number, which is covered in Handling Errors below.

Common Use Cases

Group transactions by month

You have a year of orders and want total revenue per month.

=SUMIFS(orders[amount], orders[date], ">="&DATE(2026,MONTH(A2),1), orders[date], "<"&EOMONTH(DATE(2026,MONTH(A2),1),0)+1)

// A2  = any date in the target month

This builds a date range for the month that A2 falls in, then sums every order within it. Useful when you're building a report and don't want to hardcode month boundaries by hand.

Get the month name for a report label

MONTH can't return a name, so use TEXT instead when you need "March" instead of 3.

=TEXT(C2, "mmmm")   // returns "March"
=TEXT(C2, "mmm")    // returns "Mar"

Watch out here: TEXT returns a text string, not a number. =TEXT(C2,"mmmm")="March" works fine for a label, but if you try to add, sort numerically, or run further math on that result, it won't behave like a number. Keep MONTH in the formula chain for anything you plan to calculate with.

Build a unique list of months that appear in your data

This is where a modern, dynamic-array approach beats the old SUMPRODUCT tricks you'll find in older tutorials. MONTH itself works in every Excel version, but this specific SORT/UNIQUE combination requires Excel 365 or Excel 2021; it won't spill on Excel 2019 or earlier.

=SORT(UNIQUE(MONTH(orders[date])))

// Returns a spilled list of every distinct month number present in the orders table

Feed that spilled range into FILTER to isolate rows for a specific month without writing a single helper column:

=FILTER(orders, MONTH(orders[date])=3)   // every order placed in March

Convert a month name back into a number

If a column has month names as text ("March") and you need the number back, concatenate a space and "1" onto the name and let MONTH coerce the result into a date.

=MONTH(B2&" 1")

// B2 = "March"
// Excel reads "March 1" as March 1 of the current year, then MONTH extracts 3

The space matters: B2&1 produces "March1", which Excel's date parser doesn't recognize as anything, and MONTH returns #VALUE! instead of a number. This trick also only recognizes English month names and abbreviations, so it breaks on a workbook set to a non-English locale, where "March" isn't a recognized month string at all.

Handling Errors

MONTH throws a catchable error when its argument can't be resolved to a valid date. The two you'll actually see:

Common causes of #VALUE!:

The argument is a text string Excel can't parse as a date, like "March the fourteenth"
A date typed as text conflicts with the workbook's regional date settings
The referenced cell contains unrelated text, not a date

Common causes of #NUM!:

The argument is a negative number. Excel's date system starts at serial number 1 (January 1, 1900), so anything below that has no valid month to return.

=IFERROR(MONTH(A2), "Invalid date")
=IFNA(MONTH(A2), "")   // use IFNA only if you're specifically catching #N/A from a nested lookup

✓

If MONTH keeps failing on a whole column of imported dates, select the column and run Data > Text to Columns with the Date option selected. This forces Excel to recognize the values as real dates instead of text that looks like dates.

Notes & Gotchas

How do I use the MONTH function in Excel?

Type =MONTH( followed by a cell reference that contains a date, then close the parenthesis. For example, =MONTH(A2) where A2 holds 6/12/2026 returns 6. That's the entire function. There's only one argument, and it's always required.

What does the MONTH function return in Excel?

MONTH always returns an integer between 1 and 12, representing January through December. It never returns a decimal, a date, or a text value, only the plain number of the month component of whatever date you feed it.

How do I extract the month name (not just the number) from a date in Excel?

MONTH can't do this on its own. Use =TEXT(A2,"mmmm") for a full name like "June" or =TEXT(A2,"mmm") for the abbreviation "Jun." Just remember TEXT hands back a string, not a number, so it's meant for labels and reports, not further math.

How do I find the last day of a month in Excel (EOMONTH function)?

Use =EOMONTH(A2,0), which returns the last day of the month that A2 falls in. The day portion of A2 doesn't matter at all: EOMONTH("6/1/2026",0) and EOMONTH("6/28/2026",0) both return June 30, 2026. Change the second argument to shift forward or back a set number of months, so =EOMONTH(A2,-1) gives you the last day of the prior month.

How do I calculate the number of days in a given month using Excel?

Wrap EOMONTH in DAY: =DAY(EOMONTH(A2,0)). EOMONTH finds the last date of the month, and DAY strips off just the day number, so for any date in February 2026 this returns 28. This is the cleanest way to get days-in-month without a lookup table of "30 days hath September."

Why does MONTH "reset" every 12 months instead of counting up?

Because MONTH only ever reports a position within a single 12-month cycle, like a clock face. =MONTH("1/15/2025") and =MONTH("1/15/2027") both return 1, even though two full years separate them. If you need the total number of months between two dates rather than just the month position, use DATEDIF(start,end,"m") instead.

What happens if the referenced cell is blank?

An empty cell is treated as serial number 0, and MONTH(0) returns 12, not an error and not 0. This is a silent trap in reports built from partially filled columns: a blank date row won't throw a visible error, it'll just quietly get counted as December. Check for blanks with ISBLANK before relying on MONTH output in a summary formula.

Does MONTH work with dynamic array functions like FILTER and UNIQUE?

Yes. MONTH operates on each value independently, so wrapping it around an entire array, like MONTH(orders[date]), produces an array of month numbers that FILTER, UNIQUE, and SORT can all consume directly. This replaces older SUMPRODUCT-based month extraction patterns entirely on Excel 365, with no array-entry keyboard shortcut required.

ℹ

A serial number is the number Excel actually stores behind every date. January 1, 1900 is serial number 1, and every day after that adds one.

Related Functions

Function	Use this when...
`EOMONTH`	You need the last day of a month, a set number of months forward or back.
`YEAR`	You need the year component of a date instead of the month.
`TEXT`	You need the month displayed as a name ("March") rather than a number.
`DATEDIF`	You need the total number of months or years between two dates, not just a month position.