MONTH function
MONTH returns the month of a date as a number from 1 to 12. Use it to summarize by month, to work out quarters, or to test whether a date falls in a particular month. For the month name instead of the number, use TEXT.
- Available in
- Every current version
- Category
- Date functions
- Returns
- A whole number from 1 (January) to 12 (December).
- Google Sheets
- Works the same in Google Sheets.
Syntax
=MONTH(serial_number)
| Argument | Required? | What it is |
|---|---|---|
serial_number | Required | The date to read, usually a cell reference. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| E2 | =MONTH(B2) | 1 | January is month 1 |
| E3 | =SUMPRODUCT((MONTH(B2:B5)=2)*C2:C5) | $1,980 | Total of the February orders |
| E4 | =ROUNDUP(MONTH(B5)/3,0) | 2 | The quarter for the May order |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Order | Date | Amount | Result | |
| A-1 | 1/14/2026 | $480 | 1 | |
| A-2 | 2/3/2026 | $1,250 | $1,980 | |
| A-3 | 2/27/2026 | $730 | 2 | |
| A-4 | 5/9/2026 | $915 |
Practice MONTH
From lesson 17, DATE, YEAR, MONTH & TEXT. Graded instantly, then re-checked on changed data.
Year and month
B2:B6 with the year and C2:C6 with the month number of each invoice date.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| Months from different years are lumped together | MONTH ignores the year, so January 2025 and January 2026 both return 1. Test the year too, or group by TEXT(date,"yyyy-mm"). |
| #VALUE! | The cell holds text that looks like a date. Convert it with DATEVALUE, then take the month. |
| The result shows as a date in January 1900 | The cell kept a date format. Change it to General. |
Tips
- =TEXT(A2,"mmm") gives the short month name (Jan, Feb) when you need a label rather than a number.
Questions
How do I get the month name instead of the number?
Use TEXT: =TEXT(A2,"mmmm") for the full name or =TEXT(A2,"mmm") for the three-letter version.
How do I get the quarter from a date?
Divide the month by 3 and round up: =ROUNDUP(MONTH(A2)/3,0) returns 1 to 4.
Lessons that use MONTH
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.