How to use the YEAR Function in Google Sheets

Engineering - Follow us to learn every day 💡

❤️ Learning & Engineering

Published Feb 27, 2024

Returns the year specified by a given date in Google Sheets

The YEAR function in Google Sheets is a powerful tool for extracting the year from a date value, enabling users to analyze temporal data, organize and summarize information based on annual cycles, and facilitate year-over-year comparisons. Understanding and utilizing the YEAR function can greatly enhance your data analysis and reporting capabilities in Google Sheets.

👉Purchase our book to improve your Excel productivity

Benefits of Using the YEAR Function

Simplifies Date Analysis: Quickly extract the year from complex date data for streamlined analysis and reporting.
Facilitates Yearly Comparisons: Essential for performing year-over-year growth or trend analysis.
Improves Data Organization: Helps in organizing and categorizing data by year for better visualization and summarization.
Automates Processes: Can be used in conjunction with other functions to automate data sorting, filtering, and calculation based on the year.

Step-by-Step Guide

Step 1: Understanding the Syntax

The syntax of the YEAR function is straightforward:

YEAR(date)

date: The date from which you want to extract the year. This can be a cell reference, a date created by other functions, or a date string in quotation marks.

Step 2: Entering the Formula

Select a Cell: Click on the cell where you want the result of the YEAR function to appear.
Enter the Formula: Type =YEAR(A1), assuming A1 contains the date you're extracting the year from.

Step 3: Applying the Function

Direct Input: =YEAR("2023-12-31") will return 2023.
Cell Reference: If A1 has 12/31/2023, =YEAR(A1) outputs 2023.
With Other Functions: Combine with DATE function, =YEAR(DATE(2023,12,31)) to get 2023.
👉Purchase our book to improve your Excel productivity

Example

Let's say you have a list of dates in column A representing sales transactions. You want to determine how many sales occurred in each year.

Step 1: Extract the Year

In B1, enter the header Year.
In B2, enter =YEAR(A2) and drag the formula down to apply it to all dates.

Step 2: Count Sales per Year

In C1, enter the header Sales Count.
Use a combination of UNIQUE and COUNTIF functions to count sales per year. In D2 (next to the last year extracted), enter =UNIQUE(B2:B) to list each year once. In E2, enter =COUNTIF(B:B, D2) and drag down to count sales for each year listed in D.

Advanced Tips

Dynamic Year Extraction: Combine YEAR with TODAY to get the current year: =YEAR(TODAY()).
Yearly Comparison: Use YEAR in conditional formatting to highlight rows or cells related to a specific year.
Data Validation: Create dropdown lists based on years extracted from your data for user-friendly interfaces.
👉Purchase our book to improve your Excel productivity :