Оффлайн
- Регистрация
- 1 Мар 2015
- Сообщения
- 1,481
- Баллы
- 155
YEAR Function: A Brief
The YEAR function in Excel is a simple yet powerful tool used to extract the year from a given input date. This function is extremely useful when you are working with dates and need to separate or analyze the year part individually.
| Objective | Value Returned by function |
|---|---|
| Aim to returns the year value | YEAR function in excel will return year value in 4 digits from the given input date. |
The YEAR function was introduced in Excel version 1.0, which was released in 1985 for Macintosh and 1987 for Windows.
YEAR Function: Basic Syntax
=YEAR(serial_number) // Serial_number or Excel Date Value
- serial_number: It refers to a valid Excel date from which you want to extract the year. If the serial_number value is a invalid date or a text value and then YEAR function will return the error.
The list of supported input formats that Excels YEAR function can handle as serial numbers:
- Date entered directly, =YEAR("12/31/2023")
- Cell reference containing a date, =YEAR(A1), where A1 = 01-Jan-2020
- Excel serial number, =YEAR(44561) → returns 2022
- DATE function, =YEAR(DATE(2022,5,10))
- DATEVALUE function, =YEAR(DATEVALUE("July 1, 2021"))
- NOW() or TODAY(), =YEAR(TODAY())
- Text date strings that Excel can recognize, =YEAR("2022-03-15")
Basic Example of Using Excel YEAR Function
| Input Date | Output Year | Comments |
|---|---|---|
| 26-Feb-95 | 1995 | YEAR function correctly extracts the year. |
| 29-Feb-16 | 2016 | YEAR returns the leap year correctly. |
| 12345 | 1933 | Excel interprets number as a serial date (Jan 18, 1933). |
| 1900 | 1905 | Excel treats the number as Feb 28, 1905 and returns 1905. |
| 0 | 1900 | Excel interprets 0 as Jan 0, 1900 and so returns the 1900. |
| 13/32/2022 | Input date is invalid and so YEAR function returns error! | |
| Not a date | #VALUE! | Text not recognized as a date and so YEAR function returns an error. |
| =YEAR(“”) | #VALUE! | Empty string causes YEAR to return an error. |
| Formula Used | =YEAR(serial_number) |
- In the first example, the formula extracts the year from 26-Feb-1995 and returns 1995, showing standard usage with a valid date.
- In the second example, the formula is used with a leap day (29-Feb-2016). The YEAR function correctly identifies the year as 2016, proving it handles leap years well.
- In the third example, the input is a number (12345), which Excel interprets as a serial date (Jan 18, 1933). The YEAR function then returns 1933.
- In the fourth example, the number 1900 is treated as a serial date. Excel interprets it as Feb 28, 1905, and the YEAR function returns 1905.
- In the fifth example, the input is 0, which Excel interprets as Jan 0, 1900. The YEAR function returns 1900 for this input.
- In the sixth example, the date 13/32/2022 is invalid. Since there is no 32nd day in any month, the YEAR function returns an error: #VALUE!.
- In the seventh example, the input is “Not a date”, a text string that Excel cannot recognize as a valid date. The YEAR function returns #VALUE!.
- In the eighth example, the formula is =YEAR(“”). An empty string is not a valid input for date evaluation, so the function again returns #VALUE!.
Examples of using YEAR Function with other Nested Functions
| Input Date | 29 August 1995 |
| S.no | Scenarios | Output | Formula Used | Comments |
|---|---|---|---|---|
| 1 | Extract the year from the date | 1995 | =YEAR(B4) | Basic use of YEAR function to extract year from B4 |
| 2 | Check if the year is before 2000 | TRUE | =YEAR(B4)<2000 | Returns TRUE since 1995 is before 2000 |
| 3 | Get age based on current year | 30 | =YEAR(TODAY()) – YEAR(B4) | Calculates age as of this year |
| 4 | Combine year with month name | 1995-August | =YEAR(B4) & “-” & TEXT(B4,”mmmm”) | Merges year and full month name |
| 5 | Get first day of the year | 01-Jan-95 | =(YEAR(B4),1,1) | Returns the first day of the same year |
| 6 | Get last day of the year | 31-Dec-95 | =DATE(YEAR(B4),12,31) | Returns the last day of the same year |
| 7 | Check if date is in leap year | FALSE | =IF(MOD(YEAR(B4),4)=0,IF(MOD(YEAR(B4),100)=0,MOD(YEAR(B4),400)=0,TRUE),FALSE) | Returns FALSE – 1995 is not a leap year |
| 8 | Convert to “Year Only” text format | 1995 | =TEXT(B4,”yyyy”) | Returns year as a text string, useful for formatting |
| 9 | Determine the decade | 1990 | =INT(YEAR(B4)/10)*10 | Calculates the decade by rounding down to the nearest 10 |
| 10 | Create a “Year-Quarter” label | 1995-Q3 | =YEAR(B4) & “-Q” & ROUNDUP(MONTH(B4)/3,0) | Builds a label combining year and calendar quarter |
- In the first example, the YEAR function is used with the date 29 August 1995 (in cell B4) and simply extracts the year. It returns 1995, which is the base usage of the function.
- In the second example, the formula checks whether the extracted year is before 2000. Since 1995 < 2000, it returns TRUE.
- In the third example, the formula subtracts the year in B4 from the current year using (()). If today is in 2025, it returns 30, representing the age.
- In the fourth example, the formula combines the year with the full month name using the TEXT function. The result is “1995-August”, a dynamic text label.
- In the fifth example, the formula constructs the first day of the year by setting the month and day to January 1. It returns 01-Jan-95, showing how to build the year’s start date.
- In the sixth example, the last day of the year is constructed similarly, using December 31. The output is 31-Dec-95, the final date of that year.
- In the seventh example, the formula checks whether the year in B4 is a leap year. Since 1995 is not divisible by 4, the formula returns FALSE.
- In the eighth example, the formula formats the year as plain text using TEXT(B4,”yyyy”), returning “1995”. This is useful for exporting or merging with other text fields.
- In the ninth example, the formula calculates the decade by removing the last digit and appending a 0, resulting in 1990, the decade to which the year belongs.
- In the tenth example, the formula creates a “Year-Quarter” label by dividing the month number by 3 and rounding up. Since August is in Q3, the formula returns “1995-Q3”.
That’s it.
–.
Feel free to comment us below, if you have any queries about the above topic and find more interesting excel tutorials on our homepage: .