Excel MONTH Function Examples: Formulas and Tips

Excel’s MONTH function extracts the month number from a date, returning an integer from 1 (January) to 12 (December). For example, if cell A2 contains March 15, 2024, enter =MONTH(A2) to get 3; the result is a number, not the word “March.”

What the MONTH function returns

MONTH is a date-and-time function with one required argument: a date, or a reference to a cell containing a date. Its result is always a whole number from 1 to 12. The function does not change the source date, and it does not return a year or a month name. That compact numeric result makes it useful in calculations, comparisons, charts, and summaries.

Excel stores valid dates as serial numbers and displays them using a date format. MONTH reads the date value and identifies its month. This distinction matters: a cell might display “Mar 15” while its underlying value still includes a year, day, and date serial. MONTH uses that value, not just the visible text on screen.

In Microsoft 365 for Windows, type a formula into a cell and press Enter. Excel calculates the result and typically shows a number such as 3. The same function is available in Excel for Mac and Excel for the web. The main examples below use ordinary A1-style cell references.

Basic MONTH function examples

Here are simple ways to extract a month from a cell or a date created directly in a formula. Using DATE to build a date is especially helpful because its year, month, and day arguments are unambiguous.

With a valid date of March 15, 2024, in A2, enter =MONTH(A2). The result is 3.
With a date of December 31, 2025, represented directly in a formula, enter =MONTH(DATE(2025,12,31)). The result is 12.
With a date of January 1, 2026, in A2, enter =MONTH(A2). The result is 1, not 0.

Each formula needs a date value that Excel can interpret. If a cell contains a date and is formatted as a date, MONTH can read it regardless of whether the cell displays the full date, a short date, or just a month and year. A cell formatted to display only “March,” for instance, may still contain a specific day and year.

Show a month name instead of a number

If the goal is a readable label rather than a number for calculations, use TEXT with a date. The format code “mmmm” displays the full month name; “mmm” displays an abbreviated name. These results are text, so they are convenient for headings and labels but not as useful as numeric month values for arithmetic.

With March 15, 2024, in A2, enter =TEXT(A2,"mmmm"). The result is March.
With the same date in A2, enter =TEXT(A2,"mmm"). The result is Mar.

For a label that includes a year, use a format such as “mmmm yyyy”; the result for that example is March 2024. If you only need to change how a date appears in a cell, consider changing its number format rather than using TEXT. Formatting keeps the underlying value as a date, while TEXT returns a text string.

Format a date without extracting its month

A common misconception is that a date must be converted with MONTH just to display its month. It does not. If A2 contains a genuine date, select the cell and use the number-format controls on the Home tab to display a date style. You can also open Format Cells with Ctrl+1 on Windows or Command+1 on Mac, then select a date format or create a custom one. A format such as “mmmm” displays the month name while preserving the complete date value.

This distinction affects later work. A formatted date can still be sorted chronologically and used in date calculations. A month name created with TEXT is text; sorting those names alphabetically would put April before February, not arrange them in calendar order. For analysis, keep the original date column and use a separate display or helper column only when it helps readers.

Compare dates by month

MONTH can help compare two dates, but comparing only the returned month numbers ignores the year. For example, March 2024 and March 2025 both produce 3. That is useful if the question is whether two dates fall in the same month of the calendar year, but it is not enough to determine whether they are in the same month and year.

With March 15, 2024, in A2 and March 1, 2025, in B2, enter =MONTH(A2)=MONTH(B2). The result is TRUE because both dates are in March, even though the years differ.
With those same dates in A2 and B2, enter =AND(MONTH(A2)=MONTH(B2),YEAR(A2)=YEAR(B2)). The result is FALSE because the years do not match.

Use the first comparison to identify recurring calendar-month patterns, such as sales that tend to rise each December. Use a year-aware comparison when checking whether two dates belong to the same reporting period. For a broader date comparison, comparing the original date values may be more appropriate than extracting a month.

Build the first day of a date’s month

MONTH is often combined with DATE and YEAR to create a useful monthly boundary. With a date in A2, the following formula returns the first day of that date’s month. For March 15, 2024, the result is March 1, 2024.

With March 15, 2024, in A2, enter =DATE(YEAR(A2),MONTH(A2),1). The result is the date March 1, 2024.

The output is a date value, so Excel may display it as a serial number if the result cell is formatted as General. Apply a date format to show it as a recognizable date. Creating a month-start date can be more useful than keeping only a month number because it retains the year and can serve as a clear key in a monthly report.

Summarize records for a month

For a monthly total, it is generally safer to filter a date column using the first day of the month and the first day of the next month than to match only a MONTH result. That approach distinguishes March 2024 from March 2025 and works with dates that include times, because the upper boundary excludes the next month.

Suppose transaction dates are in A2:A6 and amounts are in B2:B6. The rows contain January 5, 2024 ($100), February 10 ($80), March 1 ($120), March 15 ($50), and April 2 ($90). To total March 2024, enter =SUMIFS(B2:B6,A2:A6,">="&DATE(2024,3,1),A2:A6,"<"&DATE(2024,4,1)). The result is 170, because the two March amounts are 120 and 50.

SUMIFS adds values only when all specified criteria are met. Here, the date must be on or after March 1 and strictly before April 1. If the data includes time values, a transaction at 11:00 p.m. on March 31 is still included, while a transaction at midnight on April 1 is excluded. For a report covering another month, change the boundary dates to that month and the following month.

Common problems and how to fix them

The result is #VALUE!: The referenced cell may contain text that Excel cannot recognize as a date. Check the cell’s underlying value and how the text is written. Date text can be interpreted differently depending on regional settings, so unambiguous dates created with DATE are a safer option in formulas.
The result is 3 when you expected March: That is the normal numeric result. Use a date display format if you want the cell to show a month name, or TEXT if you specifically need a text label.
Two dates match even though they are a year apart: A comparison of MONTH results checks only the month number. Include YEAR as well when the year must match.
A blank row appears to be January: A blank cell referenced as a date can be treated like zero in calculations. To leave the result blank when A2 is empty, enter =IF(A2="","",MONTH(A2)). With A2 empty, this returns a blank display; with March 15, 2024, in A2, it returns 3.
The date looks right but gives an unexpected month: A custom number format can hide part of a date, and text that resembles a date may not be stored as a date. Check the source data and, if necessary, convert it to genuine date values before relying on MONTH.

When checking a suspicious result, temporarily change the source cell’s format to General or inspect the formula bar. Those checks can help reveal whether the cell contains a true date value, text, or a date different from the one its display suggests.

Choosing between MONTH and related options

Choose the function or method that matches the output you need. MONTH returns a reusable number. TEXT returns a label. Cell formatting changes appearance without replacing the date. A date-range formula is usually preferable when summing or counting records for a particular month and year.

Use MONTH when you need a number from 1 to 12 for a calculation or month-based comparison.
Use TEXT when a formula must return a month name as text, such as a report heading.
Use a date number format when the value should remain a date but appear in a shorter or more readable way.
Use date boundaries with functions such as SUMIFS when a summary must include one specific month and year.

For example, a monthly chart might use month-start dates as category values and format those dates to show month names. That preserves chronological order across years more reliably than sorting a list of text labels. If the chart covers multiple years, include the year in the displayed label so that repeated names such as March are not ambiguous.

Frequently Asked Questions

Does Excel’s MONTH function return a month name?
No. It returns a number from 1 to 12. Use TEXT with a month format such as “mmmm” for a full month name, or format a date cell to show the month without changing its underlying date.

How do I get the month number from a date in Excel?
If A2 contains a valid date, enter =MONTH(A2). The result is an integer from 1 through 12; for a date in March, the result is 3.

Can MONTH distinguish March 2024 from March 2025?
No. Both dates return 3. Compare the year too, or use a full date-range condition when you need to isolate one month in one particular year.

Why does MONTH return a number instead of a date?
That is the function’s intended output: a month index from 1 to 12. If you need a date, construct one with DATE; if you need a written month, use TEXT or change the date’s display format.

Can I use MONTH with a date stored as text?
Only if Excel can interpret that text as a date under the workbook’s regional settings. If the result is an error or differs between computers, convert the input to a genuine date value rather than relying on ambiguous text.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.