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
=MONTH(serial_number)=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
| Argument | Required | Description |
|---|---|---|
serial_number | Required | The 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.
=MONTH(A2)| A | B | C | D | |
|---|---|---|---|---|
| 1 | Date | Amount | Month | Quarter |
| 2 | 14-Jan-2026 | 4,200 | 1 | 1 |
| 3 | 31-Mar-2026 | 3,150 | 3 | 1 |
| 4 | 1-Apr-2026 | 5,800 | 4 | 2 |
| 5 | 19-Aug-2026 | 2,950 | 8 | 3 |
| 6 | 5-Dec-2026 | 6,400 | 12 | 4 |
- 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.
=SUMPRODUCT((MONTH(A2:A7)=D2)*(YEAR(A2:A7)=2026)*B2:B7)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Date | Amount | Month | October 2026 Total | |
| 2 | 28-Sep-2026 | 1,200 | 10 | 3,000 | |
| 3 | 3-Oct-2026 | 800 | |||
| 4 | 15-Oct-2026 | 1,500 | |||
| 5 | 30-Oct-2026 | 700 | |||
| 6 | 2-Nov-2026 | 950 | |||
| 7 | 20-Oct-2025 | 2,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
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 tableBring 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
- YEARYIL · Year of a date.
- DAYGÜN · Day of the month (1–31).
- EOMONTHSERİAY · Last day of the month, months away.
- DATETARİH · Builds a date from year, month and day.
- SUMPRODUCTTOPLA.ÇARPIM · Multiplies matching items of arrays and sums them; the classic conditional sum: SUMPRODUCT((City="Ankara")*Amount).