YEAR Function in Excel (Table of Contents)
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 very useful when we are extracting the year from the dates or supply a year value to another formula.
YEAR is an inbuilt function in excel that can be used both 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 YEAR Function in Excel has only one parameter, i.e. Serial Number.
- Serial Number: This is the value we giving to extract the Year from the date or serial number. The return value is always between 1900 and 9999.
How to Use YEAR Function in Excel?
YEAR function in Excel is very simple and easy to use. Let us understand the working of YEAR function in Excel by some YEAR Formula example. YEAR function can be used as a worksheet function and as VBA function.
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 by using Ctrl + D or double click on the right corner of the cell B2. So the result would be:
We can use the YEAR function in Excel with other many 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, 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 a beauty right? We can rearrange the data like the above one using the IF and YEAR function 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 as General.
Output is :
Step 3: Apply the IF with YEAR formula for the remaining cells like I have shown in the below image.
=IF (YEAR (A3) =YEAR (A2),””, YEAR (A2))
If the YEAR of the cell A3 is equal to the YEAR value of the cell A2 then return “” (nothing).
If the YEAR of the cell A3 is not equal to the YEAR value of the cell A2 then return the year value of the cell A2.
We can drag the formula by using Ctrl + D or double click on the right corner of the cell D3. So the result would be:
Step 4: Give a link to the date column and make the format as “mmm” for the month column in the rearranged table.
Output is :
We can drag the formula by 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 by using Ctrl + D or double click on the right corner of the cell F2. So the result would be:
Now tell me which one it looks a beauty. Your manager or boss must be ready to send an appreciation email to you.
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 by using Ctrl + D or double click on the right corner of cell B2. So the result would be:
We can use the YEAR Function in excel to find the difference between the two dates. 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 year 2 values.
Output would be :
We can drag the formula by using Ctrl + D or double click on the right corner of the 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 in 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 date format, it will show you indifferent value.
- If the date is not in the correct format YEAR function and VBA function will return the error.
- The format of the YEAR should be general or number, or else it will show the result as date only.
This has been a guide to YEAR in Excel. Here we discuss the YEAR Formula in Excel and how to use YEAR Function in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –