The MONTH, YEAR and DAY functions in Excel
MONTH returns the month of a date (1–12), YEAR the year and DAY the day of the month. Use them to group invoices by month, filter one year, or check whether a date falls in a given quarter.
Syntax
=MONTH(serial_number)
| Argument | What it means |
|---|---|
| serial_number | A date or a cell containing one. YEAR and DAY take the same single argument. |
Examples
1. The month number
| A | B | C | |
|---|---|---|---|
| 1 | Invoice | Date | Month |
| 2 | INV-31 | 2026-03-18 | 3 |
March is month 3.
2. The year: YEAR
| A | B | C | |
|---|---|---|---|
| 1 | Invoice | Date | Year |
| 2 | INV-31 | 2026-03-18 | 2026 |
Handy as a helper column when you sum or count per year.
3. The day: DAY
| A | B | C | |
|---|---|---|---|
| 1 | Invoice | Date | Day |
| 2 | INV-31 | 2026-03-18 | 18 |
The day of the month, not the day of the week. For that, use WEEKDAY.
4. Which quarter?
| A | B | C | |
|---|---|---|---|
| 1 | Invoice | Date | Quarter |
| 2 | INV-31 | 2026-03-18 | Q1 |
Months 1–3 give 1, 4–6 give 2, and so on: March is in Q1.
Common mistakes
The result shows as a date like 1900-01-03. The cell kept a date format from before.
Fix: Set the cell's format to General or Number: the result is a plain number.
#VALUE! error. The "date" is actually text, often from an import.
Fix: Convert it to a real date first (Data › Text to Columns, or DATEVALUE).
Frequently asked questions
How do I get the month's name instead of its number?
Use TEXT: =TEXT(B2,"mmmm") returns the full name, "mmm" the short one.
How do I sum per month?
Use SUMIFS with two date conditions, from the first to the last day of the month, or a helper column with MONTH.