serial_number - Required. Date value, cell reference, or text date to extract year from.
| A | B | |
|---|---|---|
| 1 | Date | Year |
| 2 | 2025-01-15 | |
| 3 | 12/31/2024 | |
| 4 | Formula: | =YEAR(A2) 2025 |
Get 4-digit value from dates
=YEAR("2025-01-15") → 2025
Get current value dynamically
=YEAR(TODAY()) → 2025
Approximate age from birth date
=2025-1990 → 35
Label records by time period
=IF(2025=2025) → "Current"
Use this date function to calculate employee age from birth dates and service duration from hire dates. Perfect for HR analytics, retirement planning, benefits eligibility, and workforce demographics. It automatically updates as time passes, ensuring accurate age and tenure calculations without manual updates. Combine with TODAY for dynamic calculations that reflect current dates, essential for employee anniversary tracking, age-based benefits, and tenure-based compensation adjustments in human resources management.
| A | B | C | |
|---|---|---|---|
| 1 | Employee | Birth Date | Age |
| 2 | Alice | 1990-05-15 | |
| 3 | Bob | 1985-08-22 | |
| 4 | Formula: | =YEAR(TODAY())-YEAR(A2) 35 |
Extract periods from transaction dates to create annual sales reports and period-over-period comparisons. This function enables grouping of time-series data for trend analysis, fiscal reporting, and multi-period performance tracking. Essential for financial analysts, sales managers, and business intelligence teams who need to aggregate daily transactions into annual summaries. Combine with SUMIF to calculate totals, use with pivot tables for temporal grouping, or pair with other date functions for comprehensive analysis.
| A | B | C | |
|---|---|---|---|
| 1 | Transaction | Date | Year |
| 2 | Sale 1 | 2024-03-15 | |
| 3 | Sale 2 | 2025-01-10 | |
| 4 | Formula: | =YEAR(A2) 2024 |
❌ The Problem:
✅ Solution:
=YEAR(DATEVALUE("January 15, 2025"))Use DATEVALUE to convert text dates to proper date serial numbers that this function can process. It requires valid date values. Alternatively, use standardized formats like "2025-01-15" or "1/15/2025" that Excel recognizes universally. For reliable results, always convert text dates using DATEVALUE or use cell references with properly formatted dates instead of text strings.
❌ The Problem:
✅ Solution:
=IFERROR(YEAR(DATEVALUE(A1)), "Invalid Date")Wrap the function in IFERROR with DATEVALUE to handle text-formatted dates gracefully. It requires numeric date values, not text. Use DATEVALUE to convert text to dates, then IFERROR to catch invalid inputs. Check cell formatting (Ctrl+1) to ensure dates are stored as Date format, not Text. For data imports, use Text-to-Columns with Date parsing to convert text to proper date values before applying this approach.
❌ The Problem:
✅ Solution:
=IF(A1="", "", YEAR(A1))Excel stores dates as serial numbers where 0 = January 0, 1900 (displayed as 1900). This function converts the serial number back. To handle blank cells correctly, wrap it in IF to test for empty cells first. Use =IF(ISBLANK(A1), "", YEAR(A1)) for explicit blank checking. Understanding date serial numbers helps troubleshoot unexpected results and design robust formulas that handle edge cases properly.
Master these YEAR variants:
From basics to advanced - AI generates perfect formulas instantly.