The YEAR function returns the year of a given date as a four-digit number. It takes one argument, a date or serial number, and extracts just the year portion.
=YEAR(serial_number)What does YEAR do?
The YEAR function in Excel is a simple but powerful tool that pulls the year out of any date. Give it a date, and it tells you which year that date falls in, as a four-digit number like 2024. It lives in the Date & Time category and works with any valid Excel date, whether you type it, reference a cell, or produce it with another formula.
Why does this matter? Dates in Excel are stored as serial numbers, so a cell showing 15/03/2024 is really a number underneath. When you need to group records by year, calculate age, or filter a list to a single year, you need a way to isolate the year. YEAR does exactly that, cleanly and reliably, without text parsing or manual work.
Imagine you run a small business and have a sheet of invoices with dates. You want to know total sales per year, or which customers bought in 2023. Instead of sorting and squinting at dates, you add a helper column with =YEAR(B2) and suddenly you can pivot, filter, or sum by year. Students can use it to work out academic years, and office workers use it for anniversary dates, contract years, and reporting periods.
When to use YEAR
- Grouping sales or expenses by calendar year for reports.
- Calculating a person's age or years of service from a start date.
- Filtering a list to show only records from a specific year.
- Building year labels for charts and dashboards.
- Checking whether a date falls in the current year with
=YEAR(A2)=YEAR(TODAY()). - Extracting the year from imported text dates after converting them with DATEVALUE.
YEAR syntax
=YEAR(serial_number)| Argument | Required? | What to enter |
|---|---|---|
serial_number | Required | The date you want the year from. It can be a date typed directly like DATE(2024,3,15), a cell reference like B2, or a number that Excel recognises as a date serial. For example, =YEAR(DATE(2024,3,15)) returns 2024. |
What it returns: A four-digit whole number representing the year of the supplied date, such as 2024. If the argument is not a valid date, Excel returns an error.
YEAR examples
1 Extract the year from a single date
The goal is to see just the year part of a date typed into a cell. The formula =YEAR(B2) looks at the date in B2 and returns only the year as a number. Excel reads 2023-05-14 as a date and pulls out 2023. The result is a plain number you can use in calculations or comparisons.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Event | Date | |||
| 2 | Conference | 2023-05-14 | 2023 | ||
| 3 | Workshop | 2024-01-09 | |||
| 4 | Webinar | 2022-11-30 | |||
| 5 |
2 Calculate age in years from a birth date
Here we want each person's age as of today. The formula =YEAR(TODAY())-YEAR(B2) finds the current year with YEAR(TODAY()), subtracts the birth year from B2, and gives an approximate age. It does not account for whether the birthday has passed this year, so it may be one year high for some people. For exact age, use =DATEDIF(B2,TODAY(),"Y").
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | Birth Date | |||
| 2 | Alice | 1990-06-15 | 36 | ||
| 3 | Ben | 1985-02-20 | |||
| 4 | Chloe | 2000-11-05 | |||
| 5 |
3 Flag dates that fall in the current year
Suppose you want to know which orders were placed this year. The formula =YEAR(B2)=YEAR(TODAY()) compares the year of the order date with the current year. It returns TRUE if they match and FALSE otherwise. You can then filter or count the TRUE values to focus on this year's activity.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order ID | Order Date | |||
| 2 | 1001 | 2024-03-10 | FALSE | ||
| 3 | 1002 | 2023-12-01 | |||
| 4 | 1003 | 2024-07-22 | |||
| 5 |
4 Sum sales for a specific year
You have a list of sales with dates and want the total for 2023 only. The formula =SUMPRODUCT((YEAR(B2:B5)=2023)*C2:C5) checks each date in B2:B5, creates an array of TRUE/FALSE for whether the year is 2023, multiplies by the sales amounts, and adds them up. It returns the combined sales for 2023 without needing a helper column. This works because SUMPRODUCT handles arrays natively.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Sale Date | Amount | |||
| 2 | Widget | 2023-04-12 | 1200 | 3650 | ||
| 3 | Gadget | 2024-02-18 | 800 | |||
| 4 | Widget | 2023-09-30 | 1500 | |||
| 5 | Gizmo | 2023-11-05 | 950 | |||
| 6 |
5 Build a year label for a report
For a dashboard, you might want a text label like "Year: 2024". The formula ="Year: "&YEAR(B2) takes the year from B2 and joins it with the text "Year: ". The ampersand combines text and numbers, so Excel converts the year number to text automatically. The result is a friendly label you can place in a chart title or report header.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Project | Start Date | |||
| 2 | Alpha | 2024-01-15 | Year: 2024 | ||
| 3 | Beta | 2023-08-01 | |||
| 4 | Gamma | 2024-05-20 | |||
| 5 |
Common YEAR errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #VALUE! | The serial_number argument is text that Excel cannot interpret as a date, such as "hello" or an empty string. | Make sure the cell contains a valid date or a number. If the date is stored as text, convert it with DATEVALUE or re-enter it in a recognised date format. |
| #NUM! | The serial number is negative or beyond Excel's date range (before 1900 or after 9999). | Check that the date is within Excel's supported range. Correct the date value or use a different calculation. |
| #NAME? | The function name is misspelled, for example =YER(B2). | Type =YEAR( and let Excel suggest the function, or correct the spelling. |
| Wrong year shown | The cell contains a date that Excel interprets differently due to regional settings, or the value is a text date. | Verify the date format and use ISNUMBER to check if the cell holds a real date. Convert text dates with DATEVALUE. |
Tips for using YEAR
- Use
=YEAR(TODAY())to get the current year dynamically; it updates automatically each year. - Combine YEAR with SUMPRODUCT or SUMIFS to total values by year without helper columns.
- Remember that YEAR returns a number, not text, so you can use it in arithmetic and comparisons.
- If your dates are stored as text, convert them first with
DATEVALUEor by using Text to Columns. - Use
=YEAR(A2)=YEAR(B2)to check if two dates fall in the same year. - Format the result as General or Number; applying a date format to a year number will show a strange date.
Frequently asked questions
What does the YEAR function do in Excel?
It extracts the year from a date and returns it as a four-digit number, such as 2024. It is useful for grouping, filtering, and calculating with dates.
Why does YEAR return a wrong number like 1905?
The cell probably contains a number that Excel treats as a date serial, or the date is formatted incorrectly. Check that the cell holds a real date and not text.
Can YEAR handle dates before 1900?
No. Excel's date system starts on 1 January 1900, so earlier dates are not supported and may cause errors.
How do I get the year from a text date?
Convert the text to a real date first using DATEVALUE, then wrap it in YEAR: =YEAR(DATEVALUE("2024-03-15")).
Does YEAR work with TODAY()?
Yes. =YEAR(TODAY()) returns the current year and updates automatically when the workbook recalculates.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. YEAR reference