Excel Functions Tutorials
- Excel Formulas Cheatsheet
- Financial Functions in Excel
- Logical Functions in Excel
- TEXT Functions in Excel
- Lookup Reference in Excel
- Address Function in Excel
- Choose Function in Excel
- Column Function in Excel
- Columns Function in Excel
- REPLACE Function in Excel
- GetPivotData in Excel
- HLOOKUP in Excel
- Hyperlink Excel Function
- INDIRECT Function in Excel
- LOOKUP Excel Function
- Match Excel Function
- VLOOKUP Excel Function
- INDEX Excel Function
- VLOOKUP vs HLOOKUP
- Maths Functions in Excel
- POWER Function in Excel
- EVEN Function in Excel
- ODD Function in Excel
- ABS Function in Excel
- SUM Function in Excel
- SUMPRODUCT Function in Excel
- SUBTOTAL Excel Function
- ROUND in Excel
- AGGREGATE Excel Function
- PRODUCT Excel Function
- RAND Excel Function
- LOG Excel Function
- EXPONENTIAL Excel Function
- SUMIF in Excel
- TAN Excel Function
- CEILING Excel Function
- LN Excel Function
- SIGN Excel Function
- COS Excel Function
- FLOOR Function in Excel
- SIN Excel Function
- Date and Time Function in Excel
- Statistical Function in Excel
YEAR in Excel (Table of Contents)
- YEAR Function in Excel
- YEAR Formula in Excel
- Year Function – Usage Notes
- How to Open the YEAR Function?
- How to Use YEAR Excel?
- Applications of YEAR Function
- YEAR Excel Function Common Problem
YEAR Function in Excel
The Microsoft Excel YEAR Function is a function in excel which allows you to return the year component of an entered date as a four-digit number. The four digit number can be anywhere between 1900 to 9999, given that the entered value is a date value. The YEAR Excel function is a built-in function and is categorized under the Date/Time Function in Microsoft Excel.
YEAR function in Excel can be used both as a worksheet function and a VBA function. In the worksheet, the YEAR function can be used as a part of the formula in the excel worksheet cell. As a VBA function, the YEAR function in excel can be used in macro code which you can enter through the VB editor. Normally, the YEAR function in Excel is used for extracting a year number from an entered date into the cell of the worksheet but it can also be used for extracting and feeding a year value into any other excel worksheet formula, such as the DATE function.
YEAR Formula in Excel
The YEAR formula in excel accepts the following parameters and arguments:
date_value/serial_value – This is referred to as the value which you have entered, and it should be a valid date which is then used to return the year value.
The return value in the YEAR Function in Excel will be a numeric value which ranges between 1900 and 9999.
Year Function in Excel – Usage Notes
- The date value entered in the YEAR in excel must be a valid Excel date in serial number format. For example, if you enter the date, Jan 1, 2000, it is equal to the serial number 32526.
- You should note that Excel can only handle dates after 1/1/1900, not before that.
- If you put any invalid data in the YEAR formula in excel, you will most probably get an error, but no valid return value.
- You can use the TODAY function with YEAR in Excel to get the year from the current date as a return value.
How to Open the YEAR Function in Excel?
- You can simply enter the desired YEAR formula in excel in the required cell to attain a return value on the argument.
- You can manually open the YEAR formula in excel dialogue box in the spreadsheet and enter the required values.
- Consider the spreadsheet given below. You can see the option of YEAR in excel under the Date/Time functions tab of the Formula section.
- Click the YEAR option and the YEAR formula in excel dialogue box will open where you can enter the values to obtain a return value. See the screenshot below.
How to Use YEAR Function in Excel with Examples
Let’s look below at some of the examples of YEAR function in Excel. These examples will help you in exploring the use of YEAR function in Excel.
YEAR in Excel Worksheet
Based on the above Excel spreadsheet, let’s consider the below examples and see the YEAR in Excel return based on the formula of YEAR function.
Consider the below screenshots of the above examples for clear understanding.
YEAR in Excel Example #1
YEAR in Excel Example #2
YEAR in Excel Example #3
YEAR in Excel Example #4
YEAR in Excel Example #5
YEAR in Excel Example #6
As it has been mentioned above that, the YEAR in excel can be combined with any other formula in excel to obtain a return value, such as DATE Function, example no. 6 shows the application of YEAR and DATE Excel function together in excel spreadsheet.
YEAR in Excel VBA
The YEAR Function in Excel can also be used as a VBA code in excel.
Look at the below YEAR in excel example to see the use of the function as VBA code.
In the above example, the variable which is called LYear will now have the value of 2017.
Applications of YEAR Function in Excel
The Microsoft Excel YEAR Function can be used for various purposes and application within the spreadsheet. Some of the common applications of YEAR Function in Excel spreadsheets are given below –
- Data validation date in a specific year
- Count dates in given year
- Converting dates to Julian format
- Finding out if a year is a leap year
- Making a series of dates by year
- Getting the nth day of the year
- Adding years to date
- Getting percent of year complete
- Getting fiscal year from the date
YEAR Excel Function Common Problem
Sometimes you can face a common problem that the return value of the YEAR function in excel is not any numerical value but it looks like a date such as “01/01/1900”. Most likely, this problem will arise when you have your cell format set as ‘date’. To correct this problem, you have to set the format of the cell as ‘general’.
Things to Remember About the YEAR Function in Excel
- The YEAR formula in excel allows you to return the year component of an entered date as a four-digit number.
- The YEAR formula in excel can also be used for extracting and feeding a year value into any other excel worksheet formula, such as the DATE function or TODAY function.
- Dates which are supplied in the YEAR formula in excel should be supplied as serial numbers or reference to cells which have valid dates or a valid date value.
- You should note down that the value which is returned by the YEAR Excel function will be Gregorian values. This will have no relation with the display format for the supplied date value in the YEAR Formula in Excel.
- The YEAR formula in excel can understand dates in all possible formats perfectly.
You can download this YEAR Function in Excel template here – YEAR Function Excel Template
This has been a guide to YEAR Function in Excel. Here we discuss the YEAR Formula in excel and how to use YEAR Excel function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –