How to use the YEAR function in Google Sheets

Let’s explore what the YEAR function is in detail, and how you can use it to organize, manage, and streamline your operations within Google Sheets.

What is the YEAR function?

The YEAR function in Google Sheets essentially allows you to extract the year, as a four-digit number, from a given date. This basic function can replace manual searches for dates within your large datasets – which can often be time-consuming and complex.

Here are some of the major benefits of using the YEAR function in your Google Sheets:

• Increase efficiency: Being able to rely on automated procedures, such as YEAR, or other date-based functions allows you to dramatically reduce the time it would take to otherwise comb through your date data manually.
• Avoid human error: Like any computer-aided method, you will noticeably avoid the risk of human error; especially when it comes to daily or frequent calculations.
• Boost productivity: The YEAR formula streamlines the entire process from start to finish, so you can dedicate your time to other tasks.

Syntax

The syntax for the YEAR function is extremely simple, as shown below:

• date – refers to the date you want to extract the year value from.

Please note: In order for this function to work, you must make sure that your dates include numbers only. If Google Sheets doesn’t recognize your dates as a numerical value, it will return a #VALUE! error. To make sure you are working with the valid data type, use the function DATE, DATEVALUE or TO_DATE to return date objects or use references to cells that have dates or numbers.

How to use the YEAR function in Google Sheets

Let’s imagine that I work in admissions for a college. I have been sent a database of all of the students that have applied for a Master’s program between 2018-2021. My job is to identify the students that have applied for 2022 admission in order to send them confirmation letters via email.

There are thousands of rows of data that I need to comb through.

To do this, simply select all of the dates, and head to Format > Number > Date. Choose a date format that uses a 4-digit year.

Now that your dates are correctly formatted, let’s take a look at the different ways you can use the YEAR function to manage your data in Google Sheets.

Valid date string

You can find the year of any data by adding the date directly into the YEAR function.

For example, I can copy and paste the date from cell A2 and add it to the formula as =YEAR(“7/6/2021”). 2021 will return as the value.

Reference to date cell

Similarly, I can also return the specific year of a date by substituting the date with the actual cell reference. If I use the formula =YEAR(A2), I get the same result.

What’s more, I can easily apply this to the rest of my rows by dragging the cell down the column for the rest of the students.

Reference to number cell

Imagine that a spreadsheet was forwarded to you, or that you copied and pasted data from another document. Some of the cells containing dates might appear as a simple numeric value. Chances are, you are going to get cells with data such as “43,000” or “42,500”. This is because every date in Google Sheets, e.g. 43,000 or 42,500, is equivalent to a date.

In the example below, 44,000 is the equivalent to 6/18/2020, which is why the YEAR formula has returned 2020 as the value.

IF + YEAR function

As mentioned previously, YEAR functions can become quite a powerful function when combined with other functions. For example, I can use a combination of the IF and YEAR function to indicate which students need to be sent an email based on their admission date.

The formula would look like this:

If you would like to learn more about how you can use the IF function in conjunction with other functions such as YEAR, read our post How to use the nested IF function in Excel.

YEAR function in Google Sheets

And there you have it! The YEAR function is a great function to use in order to quickly extract years in a large Google Sheet, and is a great one to combine with other useful functions to streamline your operations.

If you’re interested in other date-related formulas, why not check out our article on How to use the DATE formula in Google Sheets?

Alternatively, here are some other related articles you may also find useful below!