Skip to content

Excel function · Date & time

Excel MONTH function

MONTH returns the month of a date as a number from 1 to 12: 1 for January, 12 for December. Use it to split sales by month or quarter and to total a single month.

In Turkish Excel: AY

What it does

=MONTH(A2) returns the month number of the date in A2, not its name. Because the result is a number, it can be used as a criterion and in further calculations. If you need the month name, use =TEXT(A2,"mmmm") or CHOOSE with a list of names.

To find the quarter, divide the month by 3 and round up: =ROUNDUP(MONTH(A2)/3,0) gives 1 for January to March and 4 for October to December. To total a month without a helper column, apply MONTH to the range inside SUMPRODUCT.

MONTH returns only the month and ignores the year: October 2025 and October 2026 get the same number. With data that spans several years, add YEAR to the condition. For the day of the month use DAY; for the end of the month use EOMONTH.

Syntax

English Excel=MONTH(serial_number)
Turkish Excel=AY(tarih)

Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.

Arguments

ArgumentRequiredDescription
serial_numberRequiredThe date, or the cell holding the date, whose month you want, e.g. A2.

Examples

1Get the month and quarter from a date

Column C gives the month number and column D the quarter: the month is divided by 3 and rounded up to a whole number with ROUNDUP.

C2fx=MONTH(A2)
Get the month and quarter from a date: example table
ABCD
1DateAmountMonthQuarter
214-Jan-20264,20011
331-Mar-20263,15031
41-Apr-20265,80042
519-Aug-20262,95083
65-Dec-20266,400124
  • C2 (filled down to C6)=MONTH(A2)
  • D2 (filled down to D6)=ROUNDUP(MONTH(A2)/3,0)

31 March is the last day of a quarter and stays in quarter 1; the next day, 1 April, moves to quarter 2. December gives month 12 and quarter 4.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

2Total one month while minding the year

MONTH and YEAR are applied to the whole date range; SUMPRODUCT adds the amounts of rows where both match. The last row of the list is from October of last year.

E2fx=SUMPRODUCT((MONTH(A2:A7)=D2)*(YEAR(A2:A7)=2026)*B2:B7)
Total one month while minding the year: example table
ABCDE
1DateAmountMonthOctober 2026 Total
228-Sep-20261,200103,000
33-Oct-2026800
415-Oct-20261,500
530-Oct-2026700
62-Nov-2026950
720-Oct-20252,100

The three rows from October 2026 are added: 3,000. Without the year condition, the row from October 2025 would be included too.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

Common errors and fixes

#VALUE!

Cause: The date is stored as text that Excel cannot read.

Fix: Convert the values to real dates; to build them from parts, use DATE.

Years mixed up

Cause: A monthly total added the same month from different years together.

Fix: Add the year to the condition: (YEAR(A2:A9)=2026)*(MONTH(A2:A9)=10).

Result is 1

Cause: The month of an empty cell was requested; an empty cell counts as 0, which Excel reads as January 1900.

Fix: Skip empty rows: =IF(A2="","",MONTH(A2)).

Good to know

  • If you want the month as two-digit text such as 01 or 02, use =TEXT(A2,"mm"); it stops 10 sorting before 2 in text order.
  • For a fiscal year that starts in April, the fiscal month is =MOD(MONTH(A2)-4,12)+1 (MOD).

In Nexell

Get Nexell to do it from a sentence

Type this in Nexell

Add Month and Quarter columns to the sales list from the date, and give the total for each quarter in a separate table

Convert to table

Bring your sales list into the tool and type this. Nexell builds the columns and the quarterly summary with real Excel formulas and shows them in the preview; in the downloaded file, the summary updates when a date or an amount changes.

Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.

Free account · no card needed · 30 credits a month

Frequently asked questions

How do I get the quarter from a date in Excel?

Use =ROUNDUP(MONTH(A2)/3,0); the result is the quarter number from 1 to 4. To show it as "Q3", use ="Q"&ROUNDUP(MONTH(A2)/3,0).

Related functions

All date & time functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free