Updated June 9, 2023
YEAR in Excel
YEAR function in Excel can fetch you the year value from the supplied date. YEAR is one of the Date & Time function in Excel. This is useful when extracting the year from the dates or supplying a year value to another formula.
YEAR is an inbuilt function in Excel that can be used as a worksheet function and VBA function in Excel.
For Example, =YEAR (2018-11-25) would return the result as 2018.
It does not matter whether the date is in date format or date & time format. YEAR can extract the year value from the supplied value.
YEAR Formula in Excel
Below is the YEAR Formula in Excel:
The syntax of the YEAR Function in Excel has only one parameter, i.e., Serial Number.
- Serial Number: This is the value we give to extract the Year from the date or serial number. The return value is always between 1900 and 9999.
How to Use the YEAR Function in Excel?
The function is very simple and easy to use. Let us understand the working of the YEAR function in Excel by some YEAR Formula examples. YEAR function can be used as a worksheet function and VBA function.
Example #1
From the below-given dates, extract the year values.
Apply the YEAR formula to get the YEAR values from these dates.
Result is :
We can drag the formula using Ctrl + D or double click on the right corner of cell B2. So the result would be:
Example #2
We can use the YEAR function in Excel with many other functions. In this example, I will illustrate the usage of IF with YEAR.
Below is the sales data we have for a few years. In a particular year, we have 2 months. In a particular year, we have 3 months. In certain months, we have 5 months.
From the above table, I need only the years to display without duplicate years, for example, as per the below one.
Looks like a beauty, right? We can rearrange the data like the above one using the IF and YEAR functions together. Follow the below steps to explore this beautiful technique.
Step 1: Create a template first like the below one.
Step 2: Apply the YEAR formula for the first cell and make the format General.
Output is :
Step 3: Apply the IF with YEAR formula for the remaining cells, as shown in the image below.
=IF (YEAR (A3) =YEAR (A2),””, YEAR (A2))
If the YEAR of cell A3 equals the YEAR value of cell A2, then return “” (nothing).
If the YEAR of cell A3 is not equal to the YEAR value of cell A2, then return the year value of cell A2.
We can drag the formula using Ctrl + D or double click on the right corner of cell D3. So the result would be:
Step 4: Give a link to the date column and make the format “mmm” for the month column in the rearranged table.
Output is :
We can drag the formula using Ctrl + D or double click on the right corner of the cell E2. So the result would be:
Step 5: Now give a link to the sales column too.
Output is :
We can drag the formula using Ctrl + D or double click on the right corner of the cell F2. So the result would be:
Now tell me which one looks like a beauty. Your manager or boss must be ready to send an appreciation email to you.
Example #3
We can use the TEXT function as an alternative to the YEAR function to get the Year value from the supplied date.
Get the YEAR values from the below-given dates by using the TEXT function.
Apply the TEXT function to get the year value.
The output will be :
We can drag the formula using Ctrl + D or double click on the right corner of cell B2. So the result would be:
Example #4
We can use the YEAR Function in Excel to find the date difference. From the below data, find the year difference between year 1 and year 2.
By using the YEAR function, deduct the year 1 value from the year 2.
The output would be :
We can drag the formula using Ctrl + D or double click on the right corner of cell C2. So the result would be:
For example, the difference between 01-Dec-2017 and 01-Jan-2018 is just 2 months, but the YEAR function returns the result as 1 year.
VBA Code to use YEAR Function
We can use the YEAR function in VBA too.
Copy and paste the below code into your module to get the result.
The result of the above code will be 2018.
Things to remember about the YEAR Function in Excel
- YEAR function in Excel always returns the 4-digit number.
- YEAR function in Excel results should be shown as a serial number, not a date format. If you apply the date format, it will show you different values.
- If the date is not in the correct format YEAR function and VBA function will return the error.
- The YEAR’s format should be general or number, or it will show the result as a date only.
Recommended Articles
This has been a guide to YEAR in Excel. Here we discuss the YEAR Formula in Excel and how to use the YEAR Function in Excel, along with practical examples and a downloadable Excel template. You can also go through our other suggested articles –
