YEAR, MONTH, DAY: Extract Date Parts (2025)
Quick Answer: Extract date components: =YEAR(12/3/2025) returns 2025. =MONTH(12/3/2025) returns 12. =DAY(12/3/2025) returns 3. Combine with DATE for date math: =DATE(YEAR(A1)+1, MONTH(A1), DAY(A1)) adds 1 year. Perfect for age calculations and date analysis.
What are YEAR, MONTH, and DAY?
These three functions extract individual components from dates. Use them to analyze dates, calculate ages, group by time periods, or build new dates with modified components.
The Three Extraction Functions:
YEAR
Extracts year (2025)
=YEAR(12/3/2025)
Returns: 2025
MONTH
Extracts month (1-12)
=MONTH(12/3/2025)
Returns: 12
DAY
Extracts day (1-31)
=DAY(12/3/2025)
Returns: 3
Visual Breakdown:
December 3, 2025
YEAR(date)
2025
MONTH(date)
12
DAY(date)
3
Why Extract Date Components?
Calculate Ages
=YEAR(TODAY())-YEAR(Birthdate)
Simple age calculation
Group by Period
=YEAR(OrderDate)&"-Q"&ROUNDUP(MONTH(OrderDate)/3,0)
Create "2025-Q4" labels
Date Math
=DATE(YEAR(A1)+1, MONTH(A1), DAY(A1))
Add 1 year to date
Common Applications:
Analysis & Reporting:
- • Group sales by year or month
- • Calculate fiscal year
- • Create period labels (Q1, Q2)
- • Age demographics
Date Calculations:
- • Add/subtract years/months
- • Compare date components
- • Build conditional dates
- • Extract parts for formulas
💡 Pro Insight
All three functions return numbers: YEAR returns 4-digit year, MONTH returns 1-12, DAY returns 1-31. Perfect for mathematical operations and comparisons.
Combine with DATE: Extract components, modify them, rebuild date. This is the foundation of dynamic date calculations in Excel.
YEAR, MONTH, DAY Syntax
YEAR
=YEAR(serial_number)
Returns year as 4-digit number (1900-9999)
serial_number (required)
Date to extract year from. Can be date value, cell reference, or formula.
MONTH
=MONTH(serial_number)
Returns month as number 1-12 (1=January, 12=December)
serial_number (required)
Date to extract month from.
DAY
=DAY(serial_number)
Returns day as number 1-31
serial_number (required)
Date to extract day from.
Common Patterns
Extract from TODAY
=YEAR(TODAY())
Current year
Age Calculation
=YEAR(TODAY())-YEAR(A2)
Basic age from birthdate
Fiscal Year
=IF(MONTH(A2)>=7, YEAR(A2)+1, YEAR(A2))
FY starts July 1
Quarter Label
=YEAR(A2)&"-Q"&ROUNDUP(MONTH(A2)/3,0)
Returns "2025-Q4"
Same Day Next Year
=DATE(YEAR(A2)+1, MONTH(A2), DAY(A2))
Anniversary/renewal date
Month Number to Name
Using TEXT Function
MONTH(A2)=12 → "December"
Using CHOOSE Function
Custom abbreviations
⚠️ Important Notes
- • Return numbers only: MONTH returns 1-12, not "January"
- • Works with serial numbers: Excel date values (1 = Jan 1, 1900)
- • Text dates need conversion: Use DATEVALUE first if date is text
- • Combine with DATE: Extract → modify → rebuild for date math
- • Time component ignored: Only extracts date part, ignores time
- • Invalid dates: Returns #VALUE! error for non-date values
- • Performance: Very fast calculations, even on large datasets
12+ YEAR/MONTH/DAY Examples
Basic Extraction
1. Extract Year
=YEAR(A2)
12/3/2025 → 2025
2. Extract Month Number
=MONTH(A2)
12/3/2025 → 12
3. Extract Day
=DAY(A2)
12/3/2025 → 3
Age & Time Calculations
4. Calculate Age (Simple)
=YEAR(TODAY())-YEAR(A2)
Birthdate 1990 → Age 35 (in 2025)
5. Years Between Dates
=YEAR(B2)-YEAR(A2)
Count years between two dates
6. Current Quarter
="Q"&ROUNDUP(MONTH(TODAY())/3, 0)
December → "Q4"
Date Math & Rebuilding
7. Add Years to Date
=DATE(YEAR(A2)+1, MONTH(A2), DAY(A2))
12/3/2025 → 12/3/2026 (1 year later)
8. Same Date Last Year
=DATE(YEAR(A2)-1, MONTH(A2), DAY(A2))
Year-over-year comparisons
9. First Day of Month
=DATE(YEAR(A2), MONTH(A2), 1)
12/3/2025 → 12/1/2025
Reporting & Analysis
10. Year-Quarter Label
=YEAR(A2)&"-Q"&ROUNDUP(MONTH(A2)/3, 0)
12/3/2025 → "2025-Q4"
11. Month Name
=TEXT(DATE(2025, MONTH(A2), 1), "mmmm")
Month 12 → "December"
12. Fiscal Year
=IF(MONTH(A2)>=7, YEAR(A2)+1, YEAR(A2))
FY starts July 1 (Dec 2025 → FY 2026)
🎯 Pro Tips
- • Combine with DATE: Extract → modify → rebuild for flexible date math
- • Use DATEDIF for age: More accurate than YEAR subtraction for exact ages
- • TEXT for month names: Convert MONTH number to full month names
- • ROUNDUP for quarters: =ROUNDUP(MONTH/3, 0) gives quarter 1-4
- • Fiscal year logic: Adjust based on your FY start month
- • Performance tip: Extract once, reference result vs repeated extractions
Frequently Asked Questions
How do I calculate exact age from birthdate?
Simple method: =YEAR(TODAY())-YEAR(birthdate) gives approximate age. More accurate: =DATEDIF(birthdate, TODAY(), 'Y') accounts for exact dates. Example: Born Dec 15, 1990, today is Dec 3, 2025 → YEAR method says 35, DATEDIF says 34 (birthday not yet passed).
Why does MONTH return a number instead of name?
MONTH returns 1-12 for calculations. Convert to name with TEXT: =TEXT(DATE(2025, MONTH(A1), 1), 'mmmm') gives 'December'. Or CHOOSE: =CHOOSE(MONTH(A1), 'Jan', 'Feb', 'Mar', ...). Numbers allow sorting and math operations (Q1, Q2, etc).
Can I use these functions with time values?
Yes, they extract date part and ignore time. YEAR(12/3/2025 3:30 PM) = 2025. Time component doesn't affect result. Excel stores dates as numbers (date + time fraction), these functions read the integer part only.
How do I get fiscal year instead of calendar year?
Use IF with MONTH: =IF(MONTH(A1)>=7, YEAR(A1)+1, YEAR(A1)) for FY starting July 1. Adjust month check (>=7) based on your FY start. December 2025 with July FY start = FY 2026. Common starts: 7 (July), 10 (October), 4 (April).
What happens with invalid dates?
Returns #VALUE! error for non-date values. YEAR('hello') = #VALUE!. Text dates need DATEVALUE first: =YEAR(DATEVALUE('12/3/2025')). Empty cells return #NUM! error. Always validate date input before extraction.
How do I add months without changing day?
Use DATE with extracted components: =DATE(YEAR(A1), MONTH(A1)+3, DAY(A1)) adds 3 months. Or simpler with EDATE: =EDATE(A1, 3). Both handle month-end correctly (Jan 31 + 1 month = Feb 28/29). DATE method more flexible for complex logic.