Introduction
The YEAR function in Excel extracts the year from a valid Excel date. For example, if a cell contains 15-Nov-2026, the YEAR function returns 2026. It is useful for preparing employee reports, analysing yearly sales, organising transaction records, and grouping dates by year.
In this tutorial, you will learn its syntax, understand how it works, and practise it with simple examples.
1. What Is the YEAR Function in Excel?
The YEAR function returns the year from a given date as a four-digit number.
Suppose a company has stored an employee’s joining date:
| Joining Date |
|---|
| 15-Nov-2026 |
You want to display only the year from this date.
Use:
=YEAR(A2)
Expected Result: 2026
The function reads the date in cell A2 and extracts its year.
Remember: YEAR returns only the year. It does not return the month or day.
2. YEAR Function Syntax
=YEAR(serial_number)
Let’s understand the argument.
| Argument | Meaning |
|---|---|
| serial_number | A valid Excel date from which you want to extract the year. |
For example:
=YEAR("15-Nov-2026")
Result: 2026
You can provide a date directly or use a cell reference containing a valid Excel date.
3. How Does the YEAR Function Work?
Excel stores dates internally as serial numbers. A serial number represents a particular calendar date.
When you enter a date in Excel, it recognises the date value and stores it according to its date system.
The YEAR function reads that date and returns its year component.
For example:
=YEAR(A2)
If A2 contains 20-Aug-2024, Excel extracts:
| Date Component | Value |
|---|---|
| Day | 20 |
| Month | 8 |
| Year | 2024 |
Final Result: 2024
Important Points to Remember
- YEAR returns a four-digit year for normal modern dates.
- The result is a number, not a complete date.
- The input must be a valid date value.
- If you provide text that Excel cannot recognise as a date, the function may return a
#VALUE!error. - YEAR does not change the original date in your worksheet.
Practical YEAR Function Examples
Question 1: Extract Year from a Date
Problem Statement
A company wants to extract the year from an employee’s joining date.
Data
| Employee | Joining Date |
|---|---|
| Amit | 15-Nov-2026 |
Assume the joining date is in cell B2.
Excel Formula
=YEAR(B2)
Expected Result
2026
Explanation
The formula reads the date from B2 and extracts its year.
The original date is 15-Nov-2026, so the result is 2026.
Concept Covered: Extracting the year from a date.
Question 2: Extract Years from Multiple Employee Joining Dates
Problem Statement
An HR department wants to identify the joining year of every employee.
Data
| Employee | Joining Date |
|---|---|
| Amit | 15-May-2022 |
| Neha | 10-Feb-2023 |
| Rahul | 20-Aug-2024 |
| Priya | 05-Jan-2025 |
| Karan | 12-Nov-2026 |
Assume joining dates are in column B.
Excel Formula
=YEAR(B2)
Expected Results
| Employee | Joining Date | Joining Year |
|---|---|---|
| Amit | 15-May-2022 | 2022 |
| Neha | 10-Feb-2023 | 2023 |
| Rahul | 20-Aug-2024 | 2024 |
| Priya | 05-Jan-2025 | 2025 |
| Karan | 12-Nov-2026 | 2026 |
Explanation
Enter the formula in C2 and copy it down for the remaining employees.
Excel extracts the year from each employee’s joining date.
Concept Covered: Extracting years from multiple records.
Question 3: Extract Year from Sales Transaction Dates
Problem Statement
A sales manager wants to identify the year in which each transaction occurred.
Data
| Transaction ID | Transaction Date | Sales |
|---|---|---|
| T101 | 10-Mar-2024 | 25000 |
| T102 | 15-Jul-2024 | 30000 |
| T103 | 20-Jan-2025 | 45000 |
| T104 | 12-Sep-2025 | 50000 |
| T105 | 05-Nov-2026 | 60000 |
Assume transaction dates are in column B.
Excel Formula
=YEAR(B2)
Expected Results
| Transaction ID | Transaction Date | Sales | Year |
|---|---|---|---|
| T101 | 10-Mar-2024 | 25000 | 2024 |
| T102 | 15-Jul-2024 | 30000 | 2024 |
| T103 | 20-Jan-2025 | 45000 | 2025 |
| T104 | 12-Sep-2025 | 50000 | 2025 |
| T105 | 05-Nov-2026 | 60000 | 2026 |
Explanation
YEAR extracts the year from each transaction date.
This helps the sales manager identify the year associated with every transaction.
Concept Covered: Extracting years from sales records.
Question 4: Extract Year from Student Admission Dates
Problem Statement
A school wants to identify the admission year of every student.
Data
| Student | Admission Date |
|---|---|
| Rohan | 05-Apr-2023 |
| Simran | 12-Jun-2024 |
| Mohit | 18-Jul-2025 |
| Pooja | 20-Mar-2026 |
Assume admission dates are in column B.
Excel Formula
=YEAR(B2)
Expected Results
| Student | Admission Date | Admission Year |
|---|---|---|
| Rohan | 05-Apr-2023 | 2023 |
| Simran | 12-Jun-2024 | 2024 |
| Mohit | 18-Jul-2025 | 2025 |
| Pooja | 20-Mar-2026 | 2026 |
Explanation
The YEAR function reads each admission date and returns only its year.
Concept Covered: Extracting admission years from student records.
Question 5: Extract Year from a Date Entered Directly in the Formula
Problem Statement
A user wants to extract the year from a specific date without referring to another cell.
Excel Formula
=YEAR("25-Dec-2025")
Expected Result
2025
Explanation
The date is entered directly inside the formula. Excel recognises the date and YEAR extracts its year.
Concept Covered: Providing a date directly to the YEAR function.
Question 6: Extract Year from a Date Stored in a Different Cell
Problem Statement
A company stores an important project completion date in cell B2. The manager wants to display only the completion year in another cell.
Data
| Project | Completion Date |
|---|---|
| Website Development | 18-Aug-2025 |
Assume the completion date is in B2.
Excel Formula
=YEAR(B2)
Expected Result
2025
Explanation
The formula refers to B2, reads the project completion date, and returns its year.
If the completion date in B2 changes, the result will update automatically.
Concept Covered: Extracting a year using a cell reference.
Question 7: Extract Year from Dates Across Different Centuries
Problem Statement
A historical record contains dates from different centuries. You want to extract the year from each date.
Data
| Record | Date |
|---|---|
| Record A | 15-Aug-1995 |
| Record B | 20-Jan-2000 |
| Record C | 10-Jun-2010 |
| Record D | 25-Dec-2026 |
Assume dates are in column B.
Excel Formula
=YEAR(B2)
Expected Results
| Record | Date | Extracted Year |
|---|---|---|
| Record A | 15-Aug-1995 | 1995 |
| Record B | 20-Jan-2000 | 2000 |
| Record C | 10-Jun-2010 | 2010 |
| Record D | 25-Dec-2026 | 2026 |
Explanation
YEAR extracts the year from each valid date, regardless of whether the date belongs to 1995, 2000, 2010, or 2026.
Concept Covered: Extracting years from dates across different years.
Student Practice Tasks
Try these tasks using the YEAR function.
Task 1: Employee Joining Year
Extract the year from every employee’s joining date.
Task 2: Sales Transaction Year
Create a separate column that displays the year of every sales transaction.
Task 3: Student Admission Year
Extract the admission year from student admission dates.
Task 4: Project Completion Year
Display only the year from each project’s completion date.
Task 5: Historical Date Records
Extract the year from dates belonging to different decades.
Final Student Challenge: Build an Employee Joining-Year Report
An HR department wants to prepare a report showing the joining year of every employee.
Employee Data
| Employee | Department | Joining Date |
|---|---|---|
| Amit | IT | 15-Mar-2021 |
| Neha | HR | 20-Jun-2022 |
| Rahul | Finance | 10-Jan-2023 |
| Priya | IT | 25-Aug-2024 |
| Karan | Sales | 05-Feb-2025 |
| Simran | HR | 18-Nov-2026 |
Your Challenge
- Add a new column named Joining Year.
- Extract the year from every joining date.
- Apply the formula to all employees.
- Add two new employee records with different joining dates.
- Extract their joining years as well.
Goal: Create an employee joining-year report using the YEAR function.
Key Takeaways
- YEAR extracts the year from a valid Excel date.
- Its syntax is
=YEAR(serial_number). - It requires only one argument.
- The result is a four-digit year for normal modern dates.
- You can use a cell reference or a recognised date value.
- YEAR is useful for employee, student, sales, and project reports.
- The function does not change the original date.
Frequently Asked Questions (FAQs)
1. What is the YEAR function in Excel?
The YEAR function extracts the year from a valid Excel date and returns it as a number.
2. What is the syntax of the YEAR function?
The syntax is =YEAR(serial_number), where serial_number represents a valid date.
3. Can YEAR extract the year from a cell reference?
Yes. If cell A2 contains a valid date, =YEAR(A2) returns its year.
4. Does YEAR return the complete date?
No. YEAR returns only the year, such as 2026.
5. Can YEAR work with dates from previous years?
Yes. It can extract years from valid dates belonging to different years.
6. Why does YEAR return a #VALUE! error?
This can happen when the supplied value is text that Excel cannot recognise as a valid date.
7. Can YEAR extract the year from a date entered directly in a formula?
Yes. For example, =YEAR("15-Nov-2026") returns 2026 when Excel recognises the date text.
8. Does YEAR change the original date?
No. It only returns the year. The original date remains unchanged.
9. Can YEAR be used in sales and employee reports?
Yes. It is useful for extracting transaction years, joining years, admission years, and project completion years.
10. What type of result does YEAR return?
YEAR returns a numeric value representing the year.

