How to Use the YEAR Function in Google Sheets

The YEAR function in Google Sheets is useful to return the year from any given unit of time, in numeric format.

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?

Easy. The `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 `YEAR` function.

The Anatomy of the YEAR Function

The syntax (the way we write) the `YEAR` function is quite simple, and it is as follows:

`=YEAR(date)`

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.
• `date` is 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 `DAY` and `YEAR` function), the `YEAR` function 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 `N` function (for example, the underlying numeric value for the date ‘06/18/2020’ is 44000).
• The `YEAR` function 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 `NOW` and `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 `DAY` and `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:

1. To make a cell active, you should click on it. For this guide, we will use the cell C3.

1. 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.

1. 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 ‘)’.

1. If you did everything according to the guide, the `YEAR` function 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 `IF` function.

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 `YEAR` and `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 🙂

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content. There will be no spam and you can unsubscribe at any time.

How to Create a Dependent Drop Down List in Google Sheets

Learning how to create a dependent drop-down list in Google Sheets is useful to create several lists of…

How To Remove Duplicates in Google Sheets

In this article, we’ll see how to remove duplicates in Google Sheets. Consider you have a massive amount…

How To Use MDURATION Function in Google Sheets

The MDURATION function in Google Sheets is useful when you need to return the modified Macaulay duration of…

How to Use the IF THEN Formula in Google Sheets

You can use the IF THEN formula in Google Sheets if you need to make logical comparisons. When…

How to Use NPV Function in Google Sheets

The NPV function in Google Sheets is a useful tool to help determine the net present value of…

How to Use SCAN Function in Google Sheets

This guide will explain how to use the SCAN function in Google Sheets. When we want to scan…