YEAR Formula in Excel (Table of Content)
Introduction to YEAR Formula in Excel
The year function will return the 4-digit year detail from a date available in the formula or the cell that addressed the formula. That means the purpose of the Year function is to extract the year number from the specified date in the formula or cell.
Syntax:
Argument in YEAR Formula
- serial_number: Serial Number represents the date.
Explanation
If you are confused about how serial number represents the date? then you should understand how date saves in excel. From 1 Jan 1900 excel assigning a serial number to each date. For 1 Jan 1900 one, for 2 Jan 1900 two and so on.
For example, if we give serial number 1 in year formula it will return 1900 as per the below logic.
Observe the formula we just input 1 in the serial number and it returned 1900 which is the year related to that date. We will see a few more examples of how to use the YEAR function.
How to Use Excel YEAR Formula in Excel?
Excel YEAR Formula is very simple and easy. Let’s understand how to use the Excel YEAR Formula with few examples.
Example #1 – YEAR Formula with DATE Function
To know the 4-digit year we need to input the serial number of the date. But, how can we every time calculate the serial number for date. So, instead of using the serial number we can use the date formula.
4.9 (3,049 ratings)
View Course
Follow the below steps to get the year from a date using date formula instead of using the serial number.
First, start the YEAR Formula as below.
Input DATE Formula in place of the serial number as below.
Now we need to input the year, month and day in the DATE Formula.
Press Enter Key.
As we have input the Year as 2019 in the date formula hence it returns the year as 2019.
Example #2 – Year Formula with TODAY Function
If we want to get the current year details using Today function, we can get it very easily. Follow the below steps.
Start the Year Formula as below.
We need to get the 4-digit year number from today hence the input TODAY function now.
Press Enter after closing the TODAY function. It will return the current year number as below.
Example #3 – Comparing Two Dates
Comparing two dates whether it belongs to the Same Month and Year or not. Consider two sets of different dates like the below screenshot.
Now with the help of year and month function, we will check how many days in the same row are related to the same month and the same year.
Start the Formula with the YEAR first as below.
Select the First Date Cell from Set 1.
Now Add & Symbol and add MONTH function for the same cell as below.
Up to now, we merged the year and month of the first date from set 1. Similarly, do for the first date from set 2 also.
Now it will return if the month and year of both the dates are matched. If the year or month anyone criteria is not matched, then it will return False.
Drag the same formula to the other cells.
Example #4 – Find the Year is Leap Year or Not
Take a few dates from different years as shown in the below screenshot.
Now from the above data, we need to find which date is belonging to leap year. Follow the below steps to find the leap year using the YEAR function.
Start the Formula with MONTH Function.
Instead of inputting serial number, input DATE function as shown in the below screenshot.
Instead of inputting year directly use YEAR function then choose the cell which has the date.
For “MONTH” argument input 2 and for “DAY” argument input 29.
Close the DATE bracket and MONTH bracket then input equal to “=” 2 as shown below.
Press Enter Key.
Drag the Same Formula to other cells.
Conclusion
From all the dates we want to know whether February has 29 days or not for that year, February is the second month, so the formula “Month” function should return 2. It will return 2 only when the Date function is correct that means if that year February has 28 days mean it will scroll to March 1st and will return 3, so the result of the Month formula will not match with the number 2.
Whenever February has 29 days it will return 2 which will match with the right-hand side 2 hence returns “True”.
This is one way otherwise we can find in different ways as below.
1. Left-hand side calculates the date after the February 28 if it is equal to right-hand side 29 then it is a leap year.
2. Left-hand side calculates the date before March 1 if it is equal to right-hand side 29 then it is a leap year.
Things to Remember About YEAR Formula in Excel
- Year formula is helpful when we require the details of a year alone from a large bunch of data.
- Never input the Date directly in the Year function as it will consider as text and will return the error #NAME as it will consider only as text.
- If you want to use directly year function take the help of Date function and use, then you will not get any error.
- Another way of using is, to convert the date into number format and use that number in the serial number to get the 4-digit year.
- If you took the year before 1900 you will get the error message #VALUE hence ensure you are using the YEAR function for the years after 1900.
- Ensure the date format is correct because if you use a value greater than 12 in month place or greater than 31 in the day place it will throw an error.
Recommended Articles
This is a guide to YEAR Formula in Excel. Here we discuss How to use YEAR Formula in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
