Part 1: Introduction to the YEAR Function in Microsoft Excel
Definition
The YEAR function in Excel is a built-in function that allows you to extract the year from a given date as a four-digit number. It’s beneficial when isolating the year component from a complete date.
Purpose
The primary purpose of the YEAR function is to return the year corresponding to a date. The year is returned as an integer in the range of 1900-9999.
Syntax & Arguments
The syntax for the YEAR function is as follows:
YEAR(serial_number) The YEAR function has one argument:
- Serial_number: This is a required argument. It’s the date of the year you want to find. Dates should be entered using the DATE function or as a result of other formulas or functions. Problems can occur if dates are entered as text.
Return Value
The YEAR function returns the year of a date as a four-digit number.
Remarks
Microsoft Excel stores date as sequential serial numbers to be used in calculations. By default, January 1, 1900, is serial number 1, and January 1, 2008, is serial number 39448 because it is 39,448 days after January 1, 1900. The values returned by the YEAR function will be Gregorian regardless of the display format for the supplied date value.
Part 2: Examples of the YEAR Function in Microsoft Excel
Example 1: Extracting Year from Employee Start Date
Purpose of Example
This example demonstrates using the YEAR function to extract the year from an employee’s start date.
Data Tables and Formulas
| A | B | |
|---|---|---|
| 1 | Start Date | Start Year |
| 2 | 2020-01-01 | =YEAR(A2) |
| 3 | 2021-02-15 | =YEAR(A3) |
| 4 | 2022-03-20 | =YEAR(A4) |
Explanation
In this example, column A shows the employee’s start dates. The start year is extracted by using the YEAR function. The formula in cell B2 removes the year from the start date in cell A2. The same procedure is used in cells B3 and B4 to extract the year from the other start dates.
Example 2: Calculating Years of Service
Purpose of Example
This example demonstrates how to use the YEAR function nested with the TODAY function to calculate an employee’s years of service based on their start date.
Data Tables and Formulas
| A | B | |
|---|---|---|
| 1 | Start Date | Years of Service |
| 2 | 2020-01-01 | =YEAR(TODAY()) – YEAR(A2) |
| 3 | 2021-02-15 | =YEAR(TODAY()) – YEAR(A3) |
| 4 | 2022-03-20 | =YEAR(TODAY()) – YEAR(A4) |
Explanation
In this example, column A shows the employee’s start dates. The years of service are calculated by subtracting the start year from the current year. The formula in cell B2 calculates the years of service for the employee who started on the date in cell A2. The same procedure is used in cells B3 and B4 to calculate the other employees’ service years.
Example 3: Grouping Sales Data by Year
Purpose of Example
This example demonstrates how to use the YEAR function to group sales data by year.
Data Tables and Formulas
| A | B | C | |
|---|---|---|---|
| 1 | Date | Sales | Sales Year |
| 2 | 2020-01-01 | 1000 | =YEAR(A2) |
| 3 | 2021-02-15 | 2000 | =YEAR(A3) |
| 4 | 2022-03-20 | 3000 | =YEAR(A4) |
Explanation
In this example, we have the sales dates in column A and the sales amounts in column B. The sales year is extracted by using the YEAR function. The formula in cell C2 is, which removes the year from the date of the sale in cell A2. The same procedure is used in cells C3 and C4 to extract the year from the other sales dates.
Example 5: Calculating the Year of the Next Fiscal Year
Purpose of Example
This example demonstrates how to use the YEAR function nested with the IF function to calculate the year of the next fiscal year for a given date, assuming the fiscal year ends on June 30.
Data Tables and Formulas
| A | B | |
|---|---|---|
| 1 | Date | Next Fiscal Year |
| 2 | 2023-01-01 | =IF(MONTH(A2)<=6, YEAR(A2), YEAR(A2)+1) |
| 3 | 2023-07-15 | =IF(MONTH(A3)<=6, YEAR(A3), YEAR(A3)+1) |
| 4 | 2023-03-20 | =IF(MONTH(A4)<=6, YEAR(A4), YEAR(A4)+1) |
Explanation
In this example, we have the dates in column A. The year of the next fiscal year is calculated using the YEAR function to extract the year and then using the IF function to check if the current date is in the first or second half of the fiscal year. If the current date is in the first half of the fiscal year, the next fiscal year is the current year. If the current date is in the second half of the fiscal year, the next fiscal year is the following year.
Example 6: Calculating Employee’s Years of Service
Purpose of Example
This example demonstrates using the YEAR function nested with the TODAY and IF functions to calculate an employee’s years of service based on their start and current dates.
Data Tables and Formulas
| A | B | |
|---|---|---|
| 1 | Start Date | Years of Service |
| 2 | 2020-01-01 | =IF(MONTH(TODAY())>=MONTH(A2), YEAR(TODAY()) – YEAR(A2), YEAR(TODAY()) – YEAR(A2) – 1) |
| 3 | 2021-02-15 | =IF(MONTH(TODAY())>=MONTH(A3), YEAR(TODAY()) – YEAR(A3), YEAR(TODAY()) – YEAR(A3) – 1) |
| 4 | 2022-03-20 | =IF(MONTH(TODAY())>=MONTH(A4), YEAR(TODAY()) – YEAR(A4), YEAR(TODAY()) – YEAR(A4) – 1) |
Explanation
In this example, column A shows the employee’s start dates. The years of service are calculated by subtracting the start year from the current year using the YEAR function and then adjusting for the start month using the IF function. If the current month is greater than or equal to the start month, the years of service differ in years. Otherwise, one year is subtracted from the difference in years.
Example 7: Calculating Total Sales for a Specific Year
Purpose of Example
This example demonstrates using the YEAR function nested with the SUM and IF functions to calculate total sales for a specific year.
Data Tables and Formulas
| A | B | C | |
|---|---|---|---|
| 1 | Date | Sales | Total Sales for 2023 |
| 2 | 2022-01-01 | 1000 | =SUM(IF(YEAR(A2:A4)=2023, B2:B4, 0)) |
| 3 | 2023-02-15 | 2000 | =SUM(IF(YEAR(A2:A4)=2023, B2:B4, 0)) |
| 4 | 2023-03-20 | 3000 | =SUM(IF(YEAR(A2:A4)=2023, B2:B4, 0)) |
Explanation
In this example, we have the sales dates in column A and the sales amounts in column B. The total sales for 2023 are calculated by using the SUM and IF functions to sum the sales amounts where the year matches 2023.
Example 8: Finding Employee Information Based on Year of Hire
Purpose of Example
This example demonstrates how to use the YEAR function nested with the VLOOKUP function to find employee information based on their year of hire.
Data Tables and Formulas
| A | B | C | |
|---|---|---|---|
| 1 | Year of Hire | Employee Name | Employee Hired in 2023 |
| 2 | 2022 | John Doe | =VLOOKUP(2023, A2:B4, 2, FALSE) |
| 3 | 2023 | Jane Doe | =VLOOKUP(2023, A2:B4, 2, FALSE) |
| 4 | 2023 | Bob Smith | =VLOOKUP(2023, A2:B4, 2, FALSE) |
Explanation
In this example, we have column A’s years of hire and column B’s employee names. The employees hired in 2023 are found using the VLOOKUP function with the YEAR function to look up the year of hire in the table and return the corresponding employee names.
Example 9: Calculating the Year of the Next Fiscal Year
Purpose of Example
This example demonstrates how to use the YEAR function nested with the IF function to calculate the year of the next fiscal year for a given date, assuming the fiscal year ends on June 30.
Data Tables and Formulas
| A | B | |
|---|---|---|
| 1 | Date | Next Fiscal Year |
| 2 | 2023-01-01 | =IF(MONTH(A2)<=6, YEAR(A2), YEAR(A2)+1) |
| 3 | 2023-07-15 | =IF(MONTH(A3)<=6, YEAR(A3), YEAR(A3)+1) |
| 4 | 2023-03-20 | =IF(MONTH(A4)<=6, YEAR(A4), YEAR(A4)+1) |
Explanation
In this example, we have the dates in column A. The year of the next fiscal year is calculated using the YEAR function to extract the year and then using the IF function to check if the current date is in the first or second half of the fiscal year. If the current date is in the first half of the fiscal year, the next fiscal year is the current year. If the current date is in the second half of the fiscal year, the next fiscal year is the following year.
Example 10: Calculating the Year of the Previous Fiscal Year
Purpose of Example
This example demonstrates how to use the YEAR function nested with the IF function to calculate the year of the previous fiscal year for a given date, assuming the fiscal year ends on June 30.
Data Tables and Formulas
| A | B | |
|---|---|---|
| 1 | Date | Previous Fiscal Year |
| 2 | 2023-01-01 | =IF(MONTH(A2)>6, YEAR(A2), YEAR(A2)-1) |
| 3 | 2023-07-15 | =IF(MONTH(A3)>6, YEAR(A3), YEAR(A3)-1) |
| 4 | 2023-03-20 | =IF(MONTH(A4)>6, YEAR(A4), YEAR(A4)-1) |
Explanation
In this example, we have the dates in column A. The year of the previous fiscal year is calculated using the YEAR function to extract the year and then using the IF function to check if the current date is in the first or second half of the fiscal year. If the current date is in the second half of the fiscal year, the previous fiscal year is the current year. If the current date is in the first half of the fiscal year, the previous fiscal year is the last.
Example 12: Calculating the Year of the Previous Anniversary
Purpose of Example
This example demonstrates how to use the YEAR function nested with the TODAY, and IF functions to calculate the year of the previous anniversary for a given date.
Data Tables and Formulas
| A | B | |
|---|---|---|
| 1 | Date | Previous Anniversary Year |
| 2 | 2020-01-01 | =IF(MONTH(TODAY())<MONTH(A2) OR (MONTH(TODAY())=MONTH(A2) AND DAY(TODAY())<DAY(A2)), YEAR(TODAY())-1, YEAR(TODAY())) |
| 3 | 2021-02-15 | =IF(MONTH(TODAY())<MONTH(A3) OR (MONTH(TODAY())=MONTH(A3) AND DAY(TODAY())<DAY(A3)), YEAR(TODAY())-1, YEAR(TODAY())) |
| 4 | 2022-03-20 | =IF(MONTH(TODAY())<MONTH(A4) OR (MONTH(TODAY())=MONTH(A4) AND DAY(TODAY())<DAY(A4)), YEAR(TODAY())-1, YEAR(TODAY())) |
Explanation
In this example, we have the dates in column A. The year of the previous anniversary is calculated using the YEAR function to extract the current year and then using the IF function to check if the current date is before the anniversary date. If the current date is before the anniversary, the previous year is the last. If the current date is after or on the anniversary date, the previous anniversary year is the current year.
Example 13: Calculating the Year of the Next Leap Year
Purpose of Example
This example demonstrates how to use the YEAR function nested with the TODAY, and IF functions to calculate the year of the next leap year.
Data Tables and Formulas
| A | B | |
|---|---|---|
| 1 | Date | Next Leap Year |
| 2 | 2023-01-01 | =IF(MOD(YEAR(A2), 4)=0 AND (MOD(YEAR(A2), 100)<>0 OR MOD(YEAR(A2), 400)=0), YEAR(A2), IF(MOD(YEAR(A2)+1, 4)=0 AND (MOD(YEAR(A2)+1, 100)<>0 OR MOD(YEAR(A2)+1, 400)=0), YEAR(A2)+1, IF(MOD(YEAR(A2)+2, 4)=0 AND (MOD(YEAR(A2)+2, 100)<>0 OR MOD(YEAR(A2)+2, 400)=0), YEAR(A2)+2, YEAR(A2)+3))) |
| 3 | 2023-07-15 | =IF(MOD(YEAR(A3), 4)=0 AND (MOD(YEAR(A3), 100)<>0 OR MOD(YEAR(A3), 400)=0), YEAR(A3), IF(MOD(YEAR(A3)+1, 4)=0 AND (MOD(YEAR(A3)+1, 100)<>0 OR MOD(YEAR(A3)+1, 400)=0), YEAR(A3)+1, IF(MOD(YEAR(A3)+2, 4)=0 AND (MOD(YEAR(A3)+2, 100)<>0 OR MOD(YEAR(A3)+2, 400)=0), YEAR(A3)+2, YEAR(A3)+3))) |
| 4 | 2023-03-20 | =IF(MOD(YEAR(A4), 4)=0 AND (MOD(YEAR(A4), 100)<>0 OR MOD(YEAR(A4), 400)=0), YEAR(A4), IF(MOD(YEAR(A4)+1, 4)=0 AND (MOD(YEAR(A4)+1, 100)<>0 OR MOD(YEAR(A4)+1, 400)=0), YEAR(A4)+1, IF(MOD(YEAR(A4)+2, 4)=0 AND (MOD(YEAR(A4)+2, 100)<>0 OR MOD(YEAR(A4)+2, 400)=0), YEAR(A4)+2, YEAR(A4)+3))) |
Explanation
In this example, we have the dates in column A. The year of the next leap year is calculated using the YEAR function to extract the current year and then using the IF and MOD functions to check if the current year or any of the next three years is a leap year. The next leap year is the first year that meets the conditions for a leap year: it is divisible by 4, and if it is divisible by 100, it must also be divisible by 400.
Part 3: Tips and Tricks
Use Four-Digit Years: Always use four digits for the year argument in the YEAR function to prevent confusion between different centuries. For example, “07” could mean “1907” or “2007”. Four-digit years avoid this confusion.
Handling Out-of-Range Arguments: If the month argument is greater than 12, Excel adds that number of months to the first month in the year specified. Similarly, if the day argument is greater than the number of days in the month specified, Excel adds that number of days to the first day in the month.
Opposing Arguments: If the month or day argument is less than 1, Excel subtracts the magnitude of that number, plus 1, from the first month or day in the year specified.
Error Values: If the year argument is less than 0 or is 10000 or more excellent, Excel returns the #NUM! Error value.
Date Formatting: Remember that Excel stores date as sequential serial numbers. To display the result of the YEAR function as a proper date, you may need to change the number format (Format Cells).
Combining with Other Functions: The YEAR function can be combined with tasks like DATE, MONTH, and DAY to create more complex date calculations. For example, you can use these functions to create an anniversary date based on another cell’s date.