The YEAR Function in Excel is used to get the year value from the given input date! 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. YEAR function extract the year 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. 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. Objective

Value Returned by function Objective Objective Value Returned by function 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. Aim to returns the year value Aim to returns the year value YEAR function in excel will return year value in 4 digits from the given input date. 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. The YEAR function was introduced in Excel version 1.0, which was released in 1985 for Macintosh and 1987 for Windows. Excel version 1.0 1985 1987 YEAR Function: Basic Syntax =YEAR(serial_number) // Serial_number or Excel Date Value =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, then the YEAR function will return the #VALUE! error. 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, then the YEAR function will return the #VALUE! error. 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, then the YEAR function will return the #VALUE! error. serial_number #VALUE! 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") Date entered directly, =YEAR("12/31/2023") =YEAR("12/31/2023") Cell reference containing a date, =YEAR(A1), where A1 = 01-Jan-2020 =YEAR(A1), where A1 = 01-Jan-2020 Excel serial number, =YEAR(44561) → returns 2022 =YEAR(44561) → returns 2022 DATE function, =YEAR(DATE(2022,5,10)) =YEAR(DATE(2022,5,10)) DATEVALUE function, =YEAR(DATEVALUE("July 1, 2021")) =YEAR(DATEVALUE("July 1, 2021")) NOW() or TODAY(), =YEAR(TODAY()) =YEAR(TODAY()) Text date strings that Excel can recognize, =YEAR("2022-03-15") =YEAR("2022-03-15") Basic Example of Using YEAR Function: Example Explanation: 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 first example, the formula extracts the year from 26-Feb-1995 and returns 1995, showing standard usage with a valid date. first example 26-Feb-1995 1995 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 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. second example leap day 2016 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 third example, the input is a number (12345), which Excel interprets as a serial date (Jan 18, 1933). The YEAR function then returns 1933. third example number (12345) serial date 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 fourth example, the number 1900 is treated as a serial date. Excel interprets it as Feb 28, 1905, and the YEAR function returns 1905. fourth example 1900 Feb 28, 1905 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 fifth example, the input is 0, which Excel interprets as Jan 0, 1900. The YEAR function returns 1900 for this input. fifth example 0 Jan 0, 1900 1900 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 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!. sixth example 13/32/2022 #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 seventh example, the input is “Not a date”, a text string that Excel cannot recognize as a valid date. The YEAR function returns #VALUE!. seventh example “Not a date” #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!. 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!. eighth example =YEAR(“”) #VALUE! Example of Using YEAR Function with Other Nested Functions: 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

=DATE(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) & “-Q” & ROUNDUP(MONTH(B4)/3,0)(YEAR(B4)/10)*10

Calculates the decade by rounding down to the nearest 10



10

Create a “Year-Quarter” label

1995-Q3



Builds a label combining year and calendar quarter 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

=DATE(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) & “-Q” & ROUNDUP(MONTH(B4)/3,0)(YEAR(B4)/10)*10

Calculates the decade by rounding down to the nearest 10



10

Create a “Year-Quarter” label

1995-Q3



Builds a label combining year and calendar quarter S.no

Scenarios

Output

Formula Used

Comments S.no S.no Scenarios Scenarios Output Output Formula Used Formula Used Comments Comments 1

Extract the year from the date

1995

=YEAR(B4)

Basic use of YEAR function to extract year from B4 1 1 Extract the year from the date Extract the year from the date 1995 1995 =YEAR(B4) =YEAR(B4) Basic use of YEAR function to extract year from 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 2 2 Check if the year is before 2000 Check if the year is before 2000 TRUE TRUE =YEAR(B4)<2000 =YEAR(B4)<2000 Returns TRUE since 1995 is before 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 3 3 Get age based on current year Get age based on current year 30 30 =YEAR(TODAY()) – YEAR(B4) =YEAR(TODAY()) – YEAR(B4) Calculates age as of this year 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 4 4 Combine year with month name Combine year with month name 1995-August 1995-August =YEAR(B4) & “-” & TEXT(B4,”mmmm”) =YEAR(B4) & “-” & TEXT(B4,”mmmm”) Merges year and full month name Merges year and full month name 5

Get first day of the year

01-Jan-95

=DATE(YEAR(B4),1,1)

Returns the first day of the same year 5 5 Get first day of the year Get first day of the year 01-Jan-95 01-Jan-95 =DATE(YEAR(B4),1,1) =DATE(YEAR(B4),1,1) Returns the first day of the same year 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 6 6 Get last day of the year Get last day of the year 31-Dec-95 31-Dec-95 =DATE(YEAR(B4),12,31) =DATE(YEAR(B4),12,31) Returns the last day of the same year 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 7 7 Check if date is in leap year Check if date is in leap year FALSE FALSE =IF(MOD(YEAR(B4),4)=0,IF(MOD(YEAR(B4),100)=0,MOD(YEAR(B4),400)=0,TRUE),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 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 8 8 Convert to “Year Only” text format Convert to “Year Only” text format 1995 1995 =TEXT(B4,”yyyy”) =TEXT(B4,”yyyy”) Returns year as a text string, useful for formatting Returns year as a text string, useful for formatting 9

Determine the decade

1990

=INT=YEAR(B4) & “-Q” & ROUNDUP(MONTH(B4)/3,0)(YEAR(B4)/10)*10

Calculates the decade by rounding down to the nearest 10 9 9 Determine the decade Determine the decade 1990 1990 =INT=YEAR(B4) & “-Q” & ROUNDUP(MONTH(B4)/3,0)(YEAR(B4)/10)*10 =INT=YEAR(B4) & “-Q” & ROUNDUP(MONTH(B4)/3,0)(YEAR(B4)/10)*10 Calculates the decade by rounding down to the nearest 10 Calculates the decade by rounding down to the nearest 10 10

Create a “Year-Quarter” label

1995-Q3



Builds a label combining year and calendar quarter 10 10 Create a “Year-Quarter” label Create a “Year-Quarter” label 1995-Q3 1995-Q3 Builds a label combining year and calendar quarter Builds a label combining year and calendar quarter Example Explanations: 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 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. first example 29 August 1995 B4 1995 In the second example, the formula checks whether the extracted year is before 2000. Since 1995 < 2000, it returns TRUE. In the second example, the formula checks whether the extracted year is before 2000. Since 1995 < 2000, it returns TRUE. second example 1995 < 2000 TRUE In the third example, the formula subtracts the year in B4 from the current year using YEAR(TODAY()). If today is in 2025, it returns 30, representing the age. In the third example, the formula subtracts the year in B4 from the current year using YEAR(TODAY()). If today is in 2025, it returns 30, representing the age. third example TODAY 30 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 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. fourth example “1995-August” 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 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. fifth example 01-Jan-95 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 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. sixth example 31-Dec-95 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 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. seventh example leap year 1995 is not divisible by 4 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 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. eighth example “1995” 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 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. ninth example decade 1990 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”. 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”. tenth example “Year-Quarter” “1995-Q3” That’s it. This tutorial was originally published on How to Use Excel YEAR Function? How to Use Excel YEAR Function

Walkthroughs, tutorials, guides, and tips. This story will teach you how to do something new or how to do something better.

Avoid Data Loss: Merge Excel Cells with TEXTJOIN & CONCAT Functions!

How to Extract the Year From Any Date in Excel Using the YEAR Function Effortlessly?

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

Using the Excel DAYS360 Function for Financial Analysis- A Guide

4 Data Transformations Made Spreadsheet-Easy

5 Excel Features That Can Grow Your Net Worth in No Time

5 Ways to Add Security to Excel & PDF with .NET Document APIs

7 Essential Things in Excel That You Definitely Need to Master

Using the Excel DAYS360 Function for Financial Analysis- A Guide

4 Data Transformations Made Spreadsheet-Easy

5 Excel Features That Can Grow Your Net Worth in No Time

5 Ways to Add Security to Excel & PDF with .NET Document APIs

7 Essential Things in Excel That You Definitely Need to Master

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps