Function guides ยท Date
MONTH function
Returns the month of a date as a number from 1 to 12.
Syntax
=MONTH(serial_number)
| Argument | What it is |
|---|---|
serial_number | A date, as a cell reference, a DATE result, or a date typed in quotes. |
What MONTH does
MONTH returns the month of a date as a number from 1 to 12. January is 1 and December is 12.
It is used for monthly summaries, quarter calculations and seasonality checks. A column of month numbers next to a column of dates makes grouping easy.
The function does not know the year. March 2025 and March 2026 both give 3, so a summary that spans more than one year needs YEAR as well, or a date window.
For the month name, TEXT with a format code does it, and for the last day of the month use EOMONTH.
For a fiscal year that does not start in January, shift the month first. A year starting in April has the first quarter in April to June, and the fiscal month number is MOD(MONTH(A2)-4,12)+1. Tests that mention a fiscal calendar are checking whether you adjust for it or assume January.
Pivot tables can group dates by months and years without any helper column, which is the quickest route for a one-off summary. Use MONTH when the grouping has to live in formulas.
MONTH examples
1. The month number
| A | B | C | |
|---|---|---|---|
| 1 | Order | Date | |
| 2 | 1001 | 3/14/2026 | 3 |
MONTH returns 3 for any date in March. By itself it does not say which year, so combine it with YEAR when the data spans several.
2. Calendar quarter
| A | B | C | |
|---|---|---|---|
| 1 | Order | Date | |
| 2 | 1001 | 3/14/2026 | 1 |
Dividing the month by 3 and rounding up gives the quarter. March is month 3, so ROUNDUP(3/3,0) is 1, the first quarter. A date in April would give 2.
3. Month name for a report
| A | B | C | |
|---|---|---|---|
| 1 | Order | Date | |
| 2 | 1001 | 3/14/2026 | March |
MONTH gives a number. For a name, use TEXT with a month format. The result "March" is text and ready to label a chart or a summary row.
Common MONTH mistakes
- Ignoring the year. March 2025 and March 2026 both return 3. Use a date window or add YEAR to the grouping.
- Using MONTH for fiscal quarters. A fiscal year starting in July needs an offset, for example
ROUNDUP(MOD(MONTH(A2)-7,12)/3+0.01,0), or a small lookup table. - Text months. MONTH("March") is an error. It needs a full date. Build one with DATEVALUE("1 March 2026").
- Using it where TEXT is better. For a name rather than a number, TEXT with "mmmm" is the right tool.
MONTH on Excel job tests
Monthly summaries are the usual ask: total sales by month, the month with the highest revenue, a column showing the quarter.
SUMIFS with a window built from DATE and EOMONTH keeps the year in play. A helper column of MONTH is fine when the data covers one year only. Make sure January through December are all present in a summary table, that a month with no data shows 0 rather than an error, and that two years have not been merged by accident.
Related functions
MONTH questions
- How do I get the month name from a date?
=TEXT(A2,"mmmm")gives the full name,"mmm"the abbreviation.- How do I get the quarter?
=ROUNDUP(MONTH(A2)/3,0)for a calendar year.- How do I sum by month?
- SUMIFS with a date window, a helper column of MONTH, or a pivot table grouped by months.
- Does MONTH show a leading zero?
- No. It returns 3, not 03. Use TEXT(A2,"mm") for two digits.
Practice MONTH
Try it in a working grid: the free lesson DATE, YEAR, MONTH and DAY (module 10 of Everyday Excel) has checked MONTH exercises.
Practice this function