YEARThe YEAR formula in Google Sheets returns the year of a specified date. This function is useful when you need to extract the year from a date for analysis or calculations. The YEAR formula takes one argument, which is the date from which to extract the year. The output is a four-digit number representing the year.
- How to use
- Examples of using
YEARformula not working?
- Similar formulas to
YEAR formula with the syntax shown below, it has 1 required parameter:
- date (required):
The date from which to extract the year. This argument must be a valid date reference, such as a cell reference containing a date, or a date returned by another formula.
ExamplesHere are a few example use cases that explain how to use the
YEARformula in Google Sheets.
The YEAR formula can be used to calculate a person's age based on their birthdate. By subtracting the birth year from the current year, you can get an accurate age calculation that updates automatically as time passes.
Grouping data by year
If you have a large dataset that includes dates, you can use the YEAR formula to group the data by year. This can be useful for analyzing trends over time, or for creating charts and graphs that show data by year.
Extracting year from text strings
If you have text strings that include dates in a variety of formats, you can use the YEAR formula to extract the year and convert it to a numeric value. This can be useful if you need to perform calculations on the dates, or if you want to sort the data by year.
YEARnot working? Here are some common mistakes people make when using the
YEARGoogle Sheets Formula:
Incorrect date format
The YEAR formula requires a valid date format. If the date is not formatted correctly, the formula will return an error. Ensure that the date is formatted correctly, such as YYYY-MM-DD.
If the date provided is not a valid date, the YEAR formula will return an error. Check that the date is correct and try again.
Using a cell reference without a date
If a cell reference is used without a date in it, the YEAR formula will return an error. Make sure that the cell reference contains a valid date.
Providing a non-date value
If a non-date value is provided instead of a date, the YEAR formula will return an error. Ensure that a valid date value is used.
Using the wrong syntax
The YEAR formula requires the syntax YEAR(date). If the syntax is incorrect, the formula will return an error. Check that the syntax is correct and try again.
The following functions are similar to
YEAR or are often used with it in a formula:
MONTHformula is used to extract the month from a given date, returning it as a number between 1 and 12. This function is commonly used to summarize and analyze data that includes dates.
DAYfunction is used to extract the day of the month from a given date. It takes in a single argument, a date in a valid format, and returns the day of the month as a number between 1 and 31. This function can be useful for tasks such as sorting or filtering data based on the day of the month.
DATEformula is used to create a date object in Google Sheets. It takes three arguments: year, month, and day and returns a date. This formula is useful when you need to perform date calculations or create date-based charts and graphs.
WEEKDAYformula takes a given date and returns the day of the week it represents, as a number ranging from 1 (Sunday) to 7 (Saturday). This function is most commonly used to calculate the day of the week for a given date and to perform calculations or comparisons based on the day of the week.
You can learn more about the
YEAR Google Sheets function on Google Support.