The Excel MONTH function, explained interactively.
MONTH extracts the month component from a date, returning an integer between 1 (January) and 12 (December). It’s the counterpart to DATE’s second argument — where DATE takes a month integer to build a date, MONTH pulls that integer back out. Pair it with YEAR and DAY to fully decompose any date.
How to use MONTH
- Type
=MONTH(and supply a date — a cell reference containing a date, aDATE()formula, or any formula that returns a real Excel date serial. - Close the parenthesis. MONTH returns an integer from 1 to 12 — there are no extra arguments.
- The result is a plain integer, not a date. Format the output cell as General or Number — not as Date, which would misinterpret the integer as a serial number.
- To display the month as a name, wrap with
CHOOSE(MONTH(A2), "Jan", ..., "Dec")or useTEXT(A2, "MMMM")directly on the original date cell.
1, December is 12. Pair with CHOOSE() or TEXT(date, "MMMM") to render month names.DATE() formula, or any formula returning a serial. Time portions are ignored. Text dates require DATEVALUE() first.| A | B | |
|---|---|---|
| 1 | Date | Serial |
| 2 | 2026-04-19 | 46131 |
| 3 | ||
| 4 | MONTH result | 4 |
=MONTH(DATE(2026, 4, 19)) → 4. That’s April 19, 2026 (Sunday) — result range 1–12.MONTH syntax and arguments
One required argument — the date to extract from. See Microsoft’s official MONTH reference for the canonical specification.
DATE() result, or any formula returning a date serial number. Text strings that look like dates (e.g. "2026-04-19") return #VALUE!; wrap with DATEVALUE to coerce text first. MONTH returns values 1–12 for any valid date.MONTH examples
Four patterns covering the most common real-world MONTH use cases.
Example 1: Extract month number from dates
The direct use — pull the month integer from each date in a column. The result is always a number 1–12.
Drag down a column of dates to produce a month-number helper column. Use this as a key for SUMIFS or pivot tables to aggregate by month across all years.
Example 2: Convert month number to name with CHOOSE
MONTH returns 1–12, but reports often need “Jan”, “Feb”, etc. CHOOSE maps the number to a name.
The CHOOSE list acts as a lookup table: position 1 returns “Jan”, position 12 returns “Dec”. Swap the short names for full names (“January”, etc.) as needed. The alternative TEXT(A2, "MMMM") is shorter but applies locale formatting.
Example 3: Group sales by month
Add a MONTH helper column to a sales table. Then use SUMIFS to total by month number.
Column D shows the month number for each sale. A downstream formula like =SUMIFS(B:B, D:D, 1) would sum January sales. Change A4 to a different month and D4 updates automatically.
Example 4: Flag dates in a specific month
Combine MONTH with IF to create a conditional label — useful for seasonal rules or year-end logic.
Change the 12 to any month number to test a different season. For multi-month checks use OR: =IF(OR(MONTH(A2)=11, MONTH(A2)=12), "Q4", "Other").
Common MONTH errors and fixes
Three failure modes — each with what to check and how to recover.
MONTH returns #VALUE!
Cause: the argument is text, not a real date. This is the most common MONTH error — text cells that look like dates (left-aligned, from CSV imports or copy-paste) are not date serials.
Wrap with DATEVALUE(): =MONTH(DATEVALUE(A2)). Or replace the text with a DATE() formula. Check alignment: real dates right-align, text dates left-align.
MONTH returns a serial number instead of 1–12
Cause: the output cell is formatted as Date. MONTH returns a plain integer like 4, but a date format turns 4 into 1900-01-04.
Format the result cell as General or Number. MONTH’s output is an integer — never format it as a date.
MONTH shows wrong month due to locale date ambiguity
Cause: date strings like 04/05/2026 are interpreted as DD/MM or MM/DD depending on regional settings. MONTH may return 4 in one locale and 5 in another.
Use unambiguous date input: =DATE(2026, 4, 5) always means April 5, regardless of locale. Avoid raw date strings in formulas. Use ISO format YYYY-MM-DD when entering dates manually.
YEAR, MONTH & DAY compared
The three date-extraction functions are a family — they share the same single-argument syntax and always return integers.
| Function | Returns | Range | Typical use |
|---|---|---|---|
| YEAR | Year integer | 1900–9999 | Fiscal year grouping, age approximation, period anchors |
| MONTH | Month integer | 1–12 | Monthly grouping, CHOOSE for month names, seasonal flags |
| DAY | Day-of-month integer | 1–31 | Month-end checks, days-remaining, payroll cut-off flags |
| DATE | Date serial | Any valid date | Reconstruct a date from YEAR/MONTH/DAY components |
Rule of thumb: YEAR, MONTH, and DAY decompose a date into integers; DATE assembles integers back into a date. A common pattern combining all four: =DATE(YEAR(A2), MONTH(A2), 1) — the first day of the same month and year as A2.
MONTH frequently asked questions
6.01What does the MONTH function return in Excel?▸
MONTH returns an integer between 1 and 12 representing the month component of the date passed to it. January = 1, December = 12. It always returns a whole number — use CHOOSE or TEXT to convert the number to a month name if you need text output.
6.02How do I convert a month number to a month name in Excel?▸
Use CHOOSE: =CHOOSE(MONTH(A2),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"). This maps 1→Jan, 2→Feb, through 12→Dec. For full names replace the short names. Alternatively, TEXT(A2, "MMMM") returns the full month name directly from the date, but requires the full date rather than just a MONTH number.
6.03Does MONTH work on text-stored dates?▸
No — MONTH requires a real Excel date serial number. Text like "2026-04-19" returns #VALUE!. Wrap with DATEVALUE to convert first: =MONTH(DATEVALUE(A2)). Or use Data → Text to Columns → Date format to convert the column in place. Real dates right-align in cells; text dates left-align.
6.04How do I sum sales for a specific month across all years?▸
Use SUMPRODUCT: =SUMPRODUCT((MONTH(A2:A100)=4)*B2:B100) sums all values in B where the date in A is April (month 4), across any year. If you also want to restrict by year, add another condition: =SUMPRODUCT((MONTH(A2:A100)=4)*(YEAR(A2:A100)=2026)*B2:B100).
6.05Why does MONTH return a different number than expected on some dates?▸
The most common cause is that the date is stored as text and Excel is interpreting it ambiguously (e.g. MM/DD vs DD/MM). Check if the cell left-aligns — that signals text, not a real date. Also check the regional date setting: in some locales, 04/05/2026 means May 4, in others it means April 5. Use explicit =DATE(2026, 4, 5) to avoid locale ambiguity.
Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.