What Is YEAR Function In Google Sheets?
The YEAR in Google Sheets extracts the year from the selected date value and return a 4-digit year ranging from 1900, regardless of the date format.
Using the YEAR Google Sheets function we can add or subtract years to find the employee tenure, calculate age, calculate loan years, etc.
For example, the below table contains valid date values. We will extract the year, using the Google Sheets YEAR function.
Select cell B2, enter the formula =YEAR(A2), press “Enter” and drag the formula from cell B2 to B3 using the fill handle, to get the following results.
The output is shown above, where only the 4-digit year is returned.
Key Takeaways
- The YEAR in Google Sheets fetches only the year value from the selected valid date format. It does not matter in what valid format the date is, whether long or short date, date is with or without time value, etc.
- The output will always be a 4-digit year value, even if we have the date value entered in the dd/mm/yy or dd/mm/yyyy format.
- Since there is only one argument for the formula and is mandatory, ensure to provide the valid data to avoid errors. Also, the format of the result cells must be checked to execute the formula and get the precise results.
- We can use the YEAR() with other Google Sheets functions, such as IF(), FILTER(), ARRAYFORMULA(), etc. to fetch other required details.
- The ARRAYFORMULA() is found only in Google Sheets and not in MS Excel. Therefore, to execute the formula as an array, as an alternate to the formula, we must use the shortcut keys, “Ctrl+Shift+Enter”.
Syntax
The syntax of the YEAR formula in Google Sheets is,
The one and only mandatory argument of the YEAR formula in Google Sheets is,
- date: A valid date value that is entered directly within double-quotes or as a cell reference.
How To Use YEAR Function In Google Sheets?
We can use the YEAR in Google Sheets in two ways, as follows:
- Access from the Google Sheets ribbon.
- Enter the formula in the worksheet manually.
Method #1 – Access From the Google Sheets Ribbon →
Step 1: Choose an empty cell for the output → select the “Insert” tab → click the “Function” option right arrow → click the “Date” option right arrow → select the “YEAR” function, as shown below.
Step 2: The “YEAR” formulaappears, as shown below. Enter the argument as cell reference.
Method #2 – Enter the Formula in the Worksheet Manually →
Step 1: Select an empty cell for the output.
Step 2: Type =YEAR( in the cell, as shown below. [Alternatively, type =Y or =YE and double-click the YEAR from the Google Sheets suggestions.]
Step 3: Enter the arguments as cell values or cell references and close the brackets.
Step 4: Press Enter to view the outcome.
Examples
Let us consider some YEAR in Google Sheets examples and extract the year from the dates with different formats, using the FILTER(), IF(), ARRAYFORMULA() functions.
Example #1 – YEAR function to extract the YEAR from different Date formats.
Consider the dataset that consists of the dates of different years and the last 4 dates, from cells A9:A12, are the same date inserted in different formats, such as long date, short date, date with time, date with day, etc. We will use the YEAR function to extract the YEAR from different Date formats.
The steps to extract the years form different date formats are,
Step 1: Select cell B2, enter the formula =YEAR(A2) and press “Enter”, as shown below.
Step 2: Drag the formula from cell B2 to B13 using the fill handle, to get the following results.
Output Observation:
- The starting range is 1900 for year and no ending date, unlike MS Excel where the range is from 1900 to 9999. If the date is before 1900, then, we get an error as resulted in cells B2 and B3, and if date is beyond 9999, we can still fetch the year as seen in cell B13.
- In cells A9:A13, regardless of the date format, such as long date, short date, date with time, date with day, etc, the precise year is extracted and returned in the corresponding cells, B9:B13.
Example #2 – Combining YEAR with IF Function.
Consider the dataset that consists of the dates with years before and after the year 2000. Let us get the required alternate results by combining YEAR with IF Function.
The steps to combine the YEAR and the IF functions are,
Step 1: Select cell B2 and enter the formula =IF(YEAR(A2)>=2000,“Millenium Years”,“Before Y2K”), as shown below.
Step 2: Press “Enter” and drag the formula from cell B2 to B7 using the fill handle, to get the following results.
Example #3 – Combining YEAR with FILTER Function.
The dataset given below has the employee details, such as their names, ID’s, Department and date of joining. We will extract the details of the date that satisfy the given criteria by combining YEAR with FILTER Function. The cell F1:I6 is the result table with just the headers and the formatting to, populate the results. We can also format after deriving the results.
The procedure to combine FILTER with the YEAR function is,
Select cell F2, enter the formula =FILTER(A2:D11,YEAR(D2:D11)>2015) and press “Enter”, as shown below.
The output above is the filtered data derived by checking if the year value is greater than 2015. We can change the criteria as required to get other respective years.
Example #4 – Combining YEAR with ARRAYFORMULA Function.
Consider the dataset given below that consists of different date values. We will be combining YEAR with ARRAYFORMULA Function to derive the results at once.
The advantage of using ArrayFormula is that it helps in saving time. Because, we can only give one cell value argument for the YEAR function and then we must drag the formula for the rest of the cells. However, the ArrayFormula helps is deriving the years for a cell range.
The procedure to combine ARRAYFORMULA with the YEAR function is,
Select cell B2, enter the formula =ARRAYFORMULA(YEAR(A2:A11)) and press “Enter”, to get the following results in one go.
Important Things To Note
- Ensure the date values we provide to the YEAR Google Sheets function have the data format set as Date.
- Ensure to provide the argument value, either direct values within double-quotes or cell references.
- We get the #VALUE! error for invalid argument values and “#NA” error if not arguments are provided.
- If the result cells format is not valid, then we get wrong date value such as 1900. In such cases change the date format using the path, “Format → Number → Number”, as shown below.
Frequently Asked Questions (FAQs)
A few reasons the YEAR Google Sheets Function may not work are,
The one and only mandatory argument value is not provided or is incorrectly entered.
The cell value for the arguments given directly are inserted without double-quotes.
When we combine YEAR function with other functions, if the result of the formula, that must serve as an argument is not a year, then we will get an error.
The format of the result cells is different from the selected cell range data format.
We have provided a cell range as an argument and have not executed as an array formula.
We often forget in which category a function falls, here, the “YEAR” function. Then, we can insert the function as follows:
Choose an empty cell → select the “Insert” tab → click the “Function” option right arrow → click the “All” option right arrow → select the “YEAR” function, as shown below.
Alternatively, we can find the Functions icon to insert the YEAR in Google Sheets by following the path shown below.
• Choose an empty cell → click the “More” option represented by the three vertical dots at the end of the toolbar, as shown below.
• A list of icons appears when we click the “More” option. Here, click the “Functions” icon, as shown below.
• Here, click the “Functions” option → click the “All” option right arrow → select the “YEAR” function, as shown below.
We can insert the Google Sheets FILTER function as follows:
Choose an empty cell for the output → select the “Insert” tab → click the “Function” option right arrow → click the “Filter” option right arrow → select the “FILTER” function, as shown below.
We can insert the Google Sheets FILTER function as follows:
Choose an empty cell for the output → select the “Insert” tab → click the “Function” option right arrow → click the “Google” option right arrow → select the “ARRAYFORMULA” function, as shown below.
Download Template
This article must help understand the YEAR in Google Sheets, with its formula and examples. We can download the template here to use it instantly.
Recommended Articles
Guide to What Is YEAR in Google Sheets. We learn its syntax & how to use it to extract the year from the selected date value with examples. You can learn more from the following articles. –
Leave a Reply