The YEAR function in Excel is used to extract the year from a given date. It returns a four-digit year (e.g., 2023) from a date value. Below is an explanation of the YEAR function, along with examples of how to use it with data and formulas.
Syntax of the YEAR Function
=YEAR(serial_number)
serial_number: The date from which you want to extract the year. This can be a cell reference, a date entered directly, or a formula that returns a date.
Examples of Using the YEAR Function
Example 1: Extracting the Year from a Date
If you have a date in cell A1 (e.g., 10/15/2023), you can extract the year using:
=YEAR(A1)
This will return 2023.
Example 2: Extracting the Year from a Hardcoded Date
You can also extract the year directly from a hardcoded date:
=YEAR("10/15/2023")This will also return 2023.
Example 3: Calculating Age Based on Birth Year
If you have a birthdate in cell A2 (e.g., 05/20/1990), you can calculate the person's age as of the current year:
=YEAR(TODAY()) - YEAR(A2)
This formula subtracts the birth year from the current year to calculate the age.
Example 4: Combining YEAR with Other Date Functions
You can combine YEAR with other date functions like TODAY or DATE. For example, to check if a date in cell A3 is in the current year:
=IF(YEAR(A3) = YEAR(TODAY()), "Current Year", "Different Year")
This formula checks if the year in A3 matches the current year and returns "Current Year" if true.
Example 5: Extracting Year from a Text Date
If your date is stored as text (e.g., "2023-10-15"), you can convert it to a date using the DATEVALUE function and then extract the year:
=YEAR(DATEVALUE("2023-10-15"))This will return 2023.
Example 6: Using YEAR in a Conditional Formatting Rule
You can use the YEAR function in conditional formatting to highlight cells where the year matches a specific value. For example:
Select the range of dates.
Go to Home > Conditional Formatting > New Rule.
Use the formula:
=YEAR(A1) = 2023
Set the formatting style and click OK.
Example 7: Calculating Fiscal Year
If your fiscal year starts in April, you can calculate the fiscal year for a date in cell A4:
=IF(MONTH(A4) >= 4, YEAR(A4), YEAR(A4) - 1)
This formula checks if the month is April or later and returns the current year. Otherwise, it returns the previous year.
Example 8: Extracting Year from a Timestamp
If you have a timestamp in cell A5 (e.g., 10/15/2023 14:30), you can extract the year:
=YEAR(A5)
This will return 2023.
Example 9: Using YEAR in a Pivot Table
You can use the YEAR function to group dates by year in a Pivot Table:
Add a helper column to your data with the formula:
=YEAR(A6)
Use this helper column in your Pivot Table to group data by year.
Example 10: Calculating Years Between Two Dates
If you have two dates in cells A7 and B7, you can calculate the difference in years:
=YEAR(B7) - YEAR(A7)
This formula subtracts the year of the earlier date from the year of the later date.
Example 11: Extracting Year from a Date in a Different Locale
If your date is formatted for a different locale (e.g., 15/10/2023 for UK format), you can still use the YEAR function:
=YEAR(DATEVALUE("15/10/2023"))This will return 2023.
Example 12: Using YEAR in an Array Formula
If you have a list of dates in column A and want to extract the years into column B, you can use an array formula (in Excel 365 or Excel 2021):
=YEAR(A1:A10)
This will return an array of years for the dates in cells A1 to A10.
Key Notes
The
YEARfunction works with valid Excel dates. If the input is not a valid date, it will return a#VALUE!error.Excel stores dates as serial numbers, so the
YEARfunction works by interpreting the serial number and extracting the year.
No comments:
Post a Comment