How to Use the YEAR Function in Google Sheets

How to use the YEAR function in Google Sheets
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.

How to use the YEAR function 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.

How to use the YEAR function in Google Sheets

 

  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.

How to use the YEAR function in Google Sheets

 

 

  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 Google Sheets

 

 

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.

How to use the YEAR function in Google Sheets

 

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.

How to use the YEAR function in Google Sheets

 

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 🙂

 

Get emails from us about Google Sheets.

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.

 

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like