Excel YEAR, MONTH, DAY Functions: Complete Guide
YEAR, MONTH, and DAY extract the components of a date. Simple functions that show up everywhere: filtering data by year, grouping by month, calculating birthdays, and rebuilding dates for custom period math. This guide covers all three together because they work as a family.
Syntax
| Function | Returns | Range |
|---|---|---|
YEAR(date) | The year as a 4-digit number | 1900-9999 |
MONTH(date) | The month as a number | 1-12 |
DAY(date) | The day of the month | 1-31 |
All three accept a single argument — a date, or something that resolves to a date. All three return integers.
Basic examples
Extracting parts of September 6, 2026
=YEAR("2026-09-06") → 2026
=MONTH("2026-09-06") → 9
=DAY("2026-09-06") → 6
From a date cell
=YEAR(A1)
=MONTH(A1)
=DAY(A1)
Common uses
1. Extract the year from a date column
New column for YoY analysis
=YEAR(A2)
2. Group by month across years
Seasonality analysis
=MONTH(A2)
3. Check if a date is in the current year
YTD filter
=YEAR(A2) = YEAR(TODAY())
4. Birthday reminder
"Birthday this month?"
=MONTH(A2) = MONTH(TODAY())
5. Rebuild date without time
Strip time from a datetime
=DATE(YEAR(A1), MONTH(A1), DAY(A1))
=INT(A1), but more explicit.6. Same day next month
Rolling forward by month
=DATE(YEAR(A1), MONTH(A1) + 1, DAY(A1))
7. Quarter from a date
Calendar quarter (1-4)
=ROUNDUP(MONTH(A1) / 3, 0)
Q + number label
="Q" & ROUNDUP(MONTH(A1) / 3, 0)
8. Fiscal year (April start)
UK/Australia FY
=IF(MONTH(A1) >= 4, YEAR(A1), YEAR(A1) - 1)
9. Age at the year mark (rough)
Age by end of year
=YEAR(TODAY()) - YEAR(A1)
10. Precise age (birthday check)
Correct age today
=YEAR(TODAY()) - YEAR(A1) - (DATE(YEAR(TODAY()), MONTH(A1), DAY(A1)) > TODAY())
MONTH returns a number — for names use TEXT
MONTH always returns 1-12. For month names:
| Formula | Result |
|---|---|
=MONTH("2026-09-06") | 9 |
=TEXT("2026-09-06", "m") | 9 |
=TEXT("2026-09-06", "mm") | 09 (with leading zero) |
=TEXT("2026-09-06", "mmm") | Sep |
=TEXT("2026-09-06", "mmmm") | September |
Extracting from datetime values
When a cell contains a datetime like "September 6, 2026 3:47 PM":
=YEAR(A1) → 2026
=MONTH(A1) → 9
=DAY(A1) → 6
=HOUR(A1) → 15
=MINUTE(A1) → 47
YEAR, MONTH, and DAY ignore the time portion. HOUR, MINUTE, and SECOND handle the time part.
The date-to-parts-to-date roundtrip
A common pattern:
Original date → YEAR/MONTH/DAY → modified components → DATE → new date
Example: shift every date in column A to the 15th of the same month.
Normalize to mid-month
=DATE(YEAR(A1), MONTH(A1), 15)
Common pitfalls
If A1 contains "2026-09-06" as literal text (not a date), MONTH(A1) usually still works because Excel auto-converts. But formats like "6/September/2026" may fail. Always wrap in DATEVALUE for text sources: =MONTH(DATEVALUE(A1)).
An empty cell has value 0, which is January 0, 1900 in Excel's date system. MONTH(A1) returns 1 for an empty cell. Guard with =IF(A1="", "", MONTH(A1)).
If import data has 2-digit years, DAY and MONTH still work but YEAR returns the interpreted 4-digit year (2000-2029 for 00-29, 1930-1999 for 30-99). Verify before assuming YEAR = source year.
To group by year-month (e.g., "2026-09" as a text label), use =YEAR(A1) & "-" & TEXT(MONTH(A1), "00"). Padding with "00" ensures sortable strings.
YEAR, MONTH, DAY in Google Sheets
Identical functions in Google Sheets. Same syntax, same behavior, same integer output. Sheets also has an ISOWEEKNUM function that's occasionally handy alongside YEAR/MONTH/DAY.
Related functions
Date extraction, decoded
YEAR/MONTH/DAY plus TEXT combinations power most date-based grouping and filtering. Excel Wizard figures out the right pattern from your intent — "show me monthly totals" builds MONTH-based grouping; "quarterly view" combines MONTH with ROUNDUP.
Install Excel Wizard →Frequently asked questions
What do YEAR, MONTH, and DAY do in Excel?
They extract the year, month number (1-12), or day-of-month (1-31) from a date. All three take a single date argument.
How do I get the month name instead of the number?
Use TEXT instead of MONTH. =TEXT(A1, "mmmm") returns 'September'; =TEXT(A1, "mmm") returns 'Sep'.
Can I use YEAR, MONTH, DAY on text dates?
Only if Excel recognizes the text as a date. For unreliable formats, wrap in DATEVALUE first: =YEAR(DATEVALUE(A1)).
How do I rebuild a date from its parts?
Use DATE with YEAR, MONTH, DAY: =DATE(YEAR(A1), MONTH(A1), DAY(A1)). Often used to strip time from a datetime.