This post is going to show you all the ways you can use to get the year from a date value in Google Sheets.

Most any task in Google Sheets can be done in multiple ways and extracting the year from a date is no different.

Having different ways of getting something done is handy. You never know when it will be the time-saving method you need in your situation.

Throughout this article, you will see all the different ways to extract the year from a date. You might require this component of a date value for further analysis where you either want to filter the data based on year or perform some calculation conditional on the year.

The possibilities are infinite, and you should know the multiple ways to extract the year to fulfill them.

Get your copy of the example workbook and follow along to learn how to extract those years out with these methods below!

Extract the Year from a Date Value with the YEAR Function

Suppose you want to know the current age of some of your employees so that you can alter their annual health policy premiums.

What better way than using the YEAR function to extract the year?

The YEAR function takes a date, or any other numeric value as an argument and returns the four-digit year part from the date.

The beauty of this function is that you can combine it with other functions, including the logical IF condition.

Syntax for the YEAR Function

The YEAR() function has syntax as shown below-

= YEAR ( date )

• date is a date value or a cell containing a date from which you want to extract the year value in YYYY format. You can also use the functions that return the date or a number as an input to this function.

YEAR Function Example

Suppose you have the data shown in the screenshot above, where the date values are in column A, and you want to extract the year out of those values into column B.

Check out this post on how to enter a date value in Google Sheets for more on dates.

= YEAR ( A2 )

Add the above formula to cell B2. It references cell A2 as a date argument in the function. When you hit the Enter button to execute, you will see the year value 2021 in cell B2.

Drag the formula down to B6 to get the year values for all other dates in column A.

You will notice that for cell A5, the YEAR function extracts the value as 2000. This is because the function can also take a numeric value as an argument. Dates are really only serial numbers starting at 1 for 1899-12-31.

Extract the Year from a Date with the RIGHT Function

The Google Sheets RIGHT function falls under the Text Function category. It takes a text string and returns the number of characters you specify from the right side of the text specified.

Since dates appear as mm/dd/yyyy format in Google Sheets, the RIGHT function can be helpful to extract the year by extracting the last 4 characters.

Though it is a Text Function, you can use it towards the date values and get the desired results.

Please note that the year you extract will be stored as text and not the number, but you can convert it to a numerical value using the Format menu.

Syntax for the RIGHT Function

The Google Sheets RIGHT function has the syntax as shown below.

= RIGHT ( string, [number_of_characters] )

• string specifies the text or a cell containing text which you want to extract the text from the right side or from the end of the string.
• [number_of_characters] is an optional argument that specifies the number of characters you want to extract. If not specified, the default value is set to 1. This means the first character from the extreme right will be extracted.

RIGHT Function Example

You can use the RIGHT function to get the year from the date values in column A.

= RIGHT ( A2, 4 )

Insert the above formula in cell B2 using cell A2 as a string argument in the RIGHT function.

The year in these dates is a four-character value on the right, so you will need to specify the number_of_characters as 4.

When you press the Enter button to execute the formula, you will see that the year value 2021 is extracted in cell B2.

Drag the formula down across cells to populate the formula and generate the results across B2:B6.

Notice, in cell B5 the RIGHT function generates output as 6823. This doesn’t appear to be a correct year value. The issue is that the function considers the value 36823 as a text and extracts the last four characters from it.

Be aware this will only work on values formatted as dates where the year appears at the end. It is not intelligent like the YEAR function.

You can format the value in cell A5 as a date, and then the function will generate the output as expected.

📝 Note: You can see the numbers are aligned at the left, which suggests that they are stored as text values in cells. Text values will be left-aligned in Google Sheets. You can easily convert these into numbers using the Format menu from the ribbon.

Extract the Year from a Date with the TEXT Function

The TEXT function in Google Sheets is versatile.

You can provide any numerical value as an input to this function, and it will return a formatted result.

the same in the way you want but note that it stores those values as text only. You can specify a number and format it as a date, decimal number, currency or accounting number, comma-separated number, etc.

You can use this versatile TEXT function to format the date values as a year.

This is an easy way to get a dynamic current year based on the current date and time in your Google Sheets.

Syntax for the TEXT Function

= TEXT ( number, format )

• number is a mandatory argument that specifies a number, date, time, or date-time value that you want to format as text.
• format is a mandatory argument that specifies a format pattern based on which you want to format the number

TEXT Function Example

= TEXT ( A2, "YYYY" )

Use the above formula to extract the year from the date in cell A2 using the TEXT function.

The specified format here is "YYYY" so that the function returns the four-digit year part of the date.

Hit the Enter button to execute this formula then copy and paste it down across cells to generate the results for all your date values.

The function simply converts the date values into text and formats them as YYYY to show them as year strings.

📝 Note: You can format the year as two-digits as well. Just change the format to "YY" instead of "YYYY" to get the desired result. However, this can sometimes misleads you as a date in 2022 will only return 22 which will not clearly indicate what century the year falls in and might be mistaken for 1922.

Extract the Year from a Date with the QUERY Function

Google Sheets even allows you to work with your data in a SQL-like way through a function named QUERY.

You can standardize the data cleansing and aggregation methods without altering the source when you use the QUERY function.

The QUERY function provides a lot of support for data aggregation, sorting, and filtering through a variety of available functions within it. You will even be able to use the QUERY function to extract the year from date values in your data.

If you are new to the QUERY function, then check out the complete guide to the Query function in Google Sheets. This will give you all the background knowledge needed to use this powerful function.

= QUERY ( A1:A6, "SELECT YEAR(A)" )

Insert the above QUERY formula in cell B1. This uses cell A1:A6 as the data source.

The query argument "SELECT YEAR(A)" allows the function to return the year out of all the values from column A.

When you press Enter to execute the formula, you will see all cells in column B are filled with the year component of the date values from column A.

A beautiful thing about the function is that you don’t need to copy and paste it down across the rows to complete the result for all the potential rows.

The function considers that the output should be populated across the rows equal to the number of rows in the data range.

Extract the Year from a Date with a Custom Date and Time Format

The Google Sheets Format menu has many great options available. It allows you to apply many types of number and date formats. There are literally dozens of options to change the format of your data.

Even if you don’t see the format you want your data in, you always have the option of Custom Formatting.

This option allows you to set the data format of your choice, and can even be used for the conversion of a date value to a year value.

Follow these steps to apply a year format to your date values.

1. Select all the data you want to apply a custom format to. In this example, select cell range B2:B6.
2. Go to the Format menu.
3. Select the Number option for further number formatting options.
4. Select the Custom date and time format option.

When you click on the Custom date and time, a window will appear that shows all the available date and time formats.

You can create your own custom format as well. At the top, the format is set to mm-dd-yyyy.

Click on the month and date dropdowns to Delete both of them, as shown above. Also, delete the two thin dash lines separating the month, date, and year.

You will be left with the year format only as shown above.

Press the Apply button to implement this change on selected cells of column B.

That’s it! The values in column B will all be year values now.

Extract the Year from a Date with the SPLIT Function

The following method can be handy when you want to extract the year along with the day and month components from your date value.

The SPLIT function is a way of performing text to column operations. Though, it provides additional customizations that make it more versatile than the standard text-to-column operation.

The function separates the text based on a delimiter character and then returns each separated component into a new cell.

Syntax for the SPLIT Function

= SPLIT ( text, delimiter, [split_by_each], [remove_empty_text] )

• text specifies the text you want to split or separate.
• delimiter is a character or set of characters based on which you want to separate the text.
• split_by_each is an optional argument that allows the function to decide whether or not the split should be made on each character around the delimiter. It has a default value set to TRUE.
• remove_empty_text is also an optional argument to let the function know about how to deal with the empty spaces. If set to TRUE, the empty cells will be removed. Otherwise, they will be stored as white spaces in the resulting cells or rows.

SPLIT Function Example

= SPLIT ( A2, "-" )

Insert the above formula into cell B2. The SPLIT function will take cell A2 as the text argument and split it based on the - character.

The dates use the - character to separate the month, day, and year components in this example and this is what the SPLIT function will split based on.

When you press Enter to execute the formula you will get 3 cells. The cells will contain the month, day, and year separated in columns B, C, and D, respectively.

= INDEX ( SPLIT ( A2, "-" ), 1, 3 )

Suppose you only want the year value? Then you can use an INDEX function like the above formula, to return only the third result from the SPLIT function.

Copy and paste the formula down B2:B6 to get the results for all your date values.

That’s it! You have successfully mastered the art of extracting the year out of a date value with the help of these amazing methods.

Conclusion

You have learned many different methods to extract the year values from a date throughout this article.

The YEAR function takes a date value as an argument and then returns the year. This is the easiest way to get the year!

The RIGHT function is helpful since it extracts the number of characters from the right of the cell. Date values have a year on the right, so you can specify the number of characters as 4 or 2 to extract the year in four or two digits.

The TEXT function can take a date value and format it into a two or four-digit year based on a format string you provide.

The QUERY function has its own built-in YEAR function to extract the year out of dates, and it works similar to the worksheet YEAR function.

The Format menu is a valuable tool to convert your data into almost any format. It allows you to format data in multiple ways, and you can utilize this to convert the date into a year value.

Finally, the SPLIT function works as a standard text-to-column utility and allows you to split the entire date based on hyphen or slash characters used in standard date formats.

Do you know any other tricks to get the year part of a date value? Do you use any of these methods already? Let me know in the comments below!