Excel YEAR, MONTH and DAY: Extract Parts of a Date
Pull the year, month or day number out of a date.
These three take a date apart. YEAR returns the four-digit year, MONTH returns 1 to 12, and DAY returns 1 to 31. They are the inverse of DATE, which puts those three numbers back together.
Their most common job is grouping. A column of order dates cannot be summarised by month directly, but =MONTH(B2) gives you a helper column that SUMIFS and pivot tables can group on. Pair it with YEAR when the data spans more than one year, or two Januarys will be added together.
MONTH returns a number, not a name. Getting "March" instead of 3 is a formatting job: =TEXT(B2, "mmmm") gives the full name and "mmm" gives the short one. That distinction trips people up because the number is rarely what you want to display and always what you want to sort and group by.
Syntax
=YEAR(serial_number) =MONTH(serial_number) =DAY(serial_number)Arguments
serial_numberRequired- The date to take apart. It must be a real date — text that looks like a date returns #VALUE!.
The example data
Headers in row 1, data in A2:D5. Column B holds real dates; C and D hold numbers.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | Placed | Year | Month |
| 2 | SO-4417 | 2024-03-15 | 2024 | 3 |
| 3 | SO-4418 | 2024-06-30 | 2024 | 6 |
| 4 | SO-4419 | 2024-11-04 | 2024 | 11 |
| 5 | SO-4420 | 2025-01-28 | 2025 | 1 |
Worked examples
=YEAR(B2)Result: 2024
The four-digit year, as a number you can compare and group on.
=MONTH(B3)Result: 6
June as a number. Note it is 6, not "June" — that is a formatting question.
=DAY(B3)Result: 30
The day of the month, ignoring which month it is.
=TEXT(B3, "mmmm") & " " & YEAR(B3)Result: June 2024
A readable label. TEXT handles the name; YEAR keeps it unambiguous across years.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
#VALUE!
Why it happens: The argument is text that looks like a date rather than a real date.
How to fix it: Convert with DATEVALUE, or fix the column — a right-aligned date is real and a left-aligned one is text.
MONTH returns 1 for every row
Why it happens: The dates are text and Excel is reading the first number it finds, or the column was imported in a different order (mm/dd versus dd/mm).
How to fix it: Check a known date. If 15/03 shows as March and 03/04 shows as March too, the parsing is wrong for half the column.
Two different years merge in a summary
Why it happens: Grouping on MONTH alone, so every January lands in the same bucket.
How to fix it: Group on YEAR and MONTH together, or on =TEXT(B2, "yyyy-mm") which sorts correctly as text.
Tips worth knowing
- =TEXT(date, "yyyy-mm") is the best single grouping key — unique per month and sorts in date order alphabetically.
- MONTH is a number for a reason: it sorts correctly, where month names sort alphabetically and put April first.
- For quarter, use =ROUNDUP(MONTH(B2)/3, 0).
- WEEKNUM gives the week of the year, with an argument for whether weeks start on Sunday or Monday.
Frequently asked questions
How do I get the month name instead of the number?
Use TEXT: =TEXT(B2, "mmmm") gives "March" and =TEXT(B2, "mmm") gives "Mar". MONTH deliberately returns a number because that is what sorts and groups correctly; the name is for display only.
How do I group data by month?
Add a helper column with =TEXT(B2, "yyyy-mm"), then SUMIFS or group on that. Using MONTH alone merges the same month from different years, which is almost never what you want.
Why does YEAR return #VALUE! on my dates?
They are text, not dates. Excel right-aligns real dates and left-aligns text by default, so a column of left-aligned dates is the signature. DATEVALUE converts them, or select the column and use Data → Text to Columns to force a re-parse.
Related functions
Guides that use it
Longer reads where this function does real work in a real sheet.