# How to extract the year from a date in Excel

Use `=YEAR(B2)`. It returns the four-digit year as a number; MONTH and DAY return the other parts.

**Excel:** `=YEAR(B2)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C |
| --- | --- | --- |
| Employee | Hire date | Hire year |
| J. Patel | 2019-06-30 |  |
| L. Moreau | 2022-01-03 |  |
| S. Kim | 2024-11-18 |  |
| B. Okafor | 2016-04-11 |  |
| E. Larsen | 2026-02-09 |  |

Formula in C2, filled to C6: `=YEAR(B2)`

Result: 2019; 2022; 2024; 2016; 2026

## How it works

YEAR returns the year of a date as a number, ready for grouping, COUNTIF or a pivot table. MONTH and DAY work the same way for the month (1 to 12) and the day (1 to 31).

## Other ways

- `=TEXT(B2,"yyyy")`: The year as text, for labels. (Text, not a number.)

## Common mistakes

- If the result shows as a date like 7/11/1905, the cell has a date format. Format it as a number or General.
- YEAR on text that doesn't look like a date returns #VALUE!. Convert the text first.

## Questions

**How do I get the month or the day?**

`=MONTH(B2)` returns 1 to 12 and `=DAY(B2)` returns 1 to 31.

**How do I count the hires per year?**

`=SUMPRODUCT(--(YEAR(B2:B6)=2024))`, or COUNTIFS between January 1 and December 31.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/extract-the-year-from-a-date