The YEAR function in Google Sheets is useful to return the year from any given unit of time, in numeric format.
Table of Contents
Just like the DAY and MONTH functions, the
YEAR function is a simple and straightforward date function that is useful when you are working with dates and want to get rid of the excess part of each date. You can also combine it with some other functions to get more powerful formulas.
Let’s take an example.
Say you have a list of candidates, with their names and birthdates, and need to know if they are older than 18 or not since only candidates older than 18 will be considered 🙎♀️🙎♂️🔞
So how do we do that?
YEAR function just needs the date, and it will automatically return only the year, in numeric format. And with the help of the IF function, we can find out which candidates are older than 18 and which are not.
Let’s first take a look at the anatomy of the
The Anatomy of the YEAR Function
The syntax (the way we write) the
YEAR function is quite simple, and it is as follows:
Now, we will break this down to help you understand the syntax of the
YEAR function and what each of these terms means:
=the equal sign is how we begin any function in Google Sheets.
YEAR()is our function.
dateis the given unit of time we want to extract the year from.
⚠️ A few notes you should know when writing your own YEAR function in Google Sheets:
- Just like the other date functions we talked about before (the
YEARfunction cannot read all human-readable dates. As the input value, you will have to use either a cell reference, a date function, or a date serial number returned by the
Nfunction (for example, the underlying numeric value for the date ‘06/18/2020’ is 44000).
YEARfunction will return the error (#VALUE!) if the given unit of time is recognised as a text and not a number.
A Real Example of Using the YEAR Function
Let’s take a look at how the
YEAR function works with different date values. This will help you understand the
YEAR function and how to use it in Google Sheets.
Whether you enter the date using slashes (cell A2) or dashes (cell A3), the
YEAR function will recognise it as a number and it will return the year from the given date. The same goes if you add the time after your date (cells A5 and A6). But note that the YEAR function will not recognise your date as a number if you enter it using dots (cell A4).
But be careful when using words to enter your date (cells A7, A8, and A9). As you can see in the cell B8, the
YEAR function will not work if you enter your date as 1st, 2nd, 3rd, etc, and will return the error (#VALUE!).
Dates in cells A10 and A11 are entered using the
TODAY functions. Both these dates are recognised as date values by the
YEAR function and the function was able to return the year from these dates. Please note that these two date values change, so the output will look different when you make a copy of this sheet.
The ‘zero’ date
But what about the cell A12? There is no date, just the time, but the
YEAR function did not return the error (#VALUE!). Instead, it returned the ‘1899’ year. How did this happen? When there is no date, the
YEAR function, just like the
MONTH functions, uses the ‘zero’ date, which is 12/30/1899.
The same will happen if you live the reference cell blank (cell A13). If you do not leave the cell blank but enter a random number instead, the ‘zero’ date will be incremented by that number. In cell A14 we entered the number ‘365’ so the output in cell B14 is ‘1900’ (12/30/1899+365=12/30/1900).
If you enter 44000, you will get the year 2020 (the actual date will be 06/18/2020) so you should add a few more days to get to the present date.
Make a copy of the spreadsheet using the link below and take a closer look at different date values and how to use the
YEAR function in Google Sheets:
How to Use the YEAR Function in Google Sheets
If you want to learn how to use the
YEAR function in Google Sheets and how it works with different formulas, take a look at this step-by-step guide:
- To make a cell active, you should click on it. For this guide, we will use the cell C3.
- When writing any function, we should first enter the equals sign ‘=’. Then, enter the name of the function, which is ‘YEAR’. As you start typing, Google Sheets will suffer functions that start with the same letters. Choose your function from the list or enter it manually.
- After the name of the function, you should put an opening round bracket ‘(‘ and input the date value. In this guide, we will use a cell reference to a cell containing the date value. Type B3 and close the function. You can close the function if you press the Enter key on your keyboard, or if you type a closing round bracket ‘)’.
- If you did everything according to the guide, the
YEARfunction will return ‘2002’. Now repeat these steps for the next rows.
How to Use the YEAR Function in Other Formulas In Google Sheets
If you remember our example from the beginning of this text, we do not need just the year each of the candidates was born at. We need to know if they are older than 18 or not 🔞
For this, we will use the
Enter the formula as follows: =IF(YEAR(TODAY())-YEAR(B3)>=18,”Suitable”,”Not Suitable”) in cell D3.
Now type the formula in other cells, just change the cell reference accordingly and with the help of the
IF functions, you will find out which candidates are older than 18 and which are not.
That is it! Now you know how to use the
YEAR function in Google Sheets! Now you can learn more on how to use the
IF function in Google Sheets or take a look at the other Google Sheets formulas and learn how to create even more effective formulas that will help you sort and filter your data 🙂