Excel YEAR, MONTH and DAY Functions — Extract Date Parts (with Examples)

What does it do?

YEAR, MONTH and DAY extract individual date components from a date value. YEAR returns the four-digit year, MONTH returns a number from 1 to 12, and DAY returns the day number from 1 to 31. Use them to group transactions by period, filter data by month, build dynamic labels, and create date-based conditions.

Syntax

=YEAR(date) =MONTH(date) =DAY(date)

Argument	Description
=YEAR(date) =MONTH(date) =DAY(date)
date required	Any date value, cell reference, or formula returning a date.

Real examples

Example 1

Extract the year

=YEAR(A2) ← 14/06/2026 → 2026

Example 2

Extract month number

=MONTH(A2) ← 14/06/2026 → 6

Example 3

Get month name from a date

=TEXT(A2, "mmmm") ← June

Use TEXT for the month name rather than MONTH which only returns a number.

Example 4

Group sales by year

=SUMIF(helper_col, 2026, sales_col)

Create a helper column with =YEAR(date_col), then SUMIF by year.

Example 5

Flag dates in the current month

=AND(YEAR(A2)=YEAR(TODAY()), MONTH(A2)=MONTH(TODAY()))

Returns TRUE if A2 is in the current month and year.

FAQ

Why use YEAR() instead of just formatting a cell?

Formatting only changes display. YEAR() extracts the year as a number you can use in calculations, SUMIF, COUNTIF, and IF conditions.

Can I reconstruct a date from YEAR, MONTH and DAY?

Yes — =DATE(YEAR(A2), MONTH(A2), DAY(A2)) reconstructs the original date. Useful for building new dates based on components of existing ones.

How do I get the quarter from a date?

=ROUNDUP(MONTH(A2)/3,0) returns 1, 2, 3 or 4 — the quarter number.

Grouping data by year and month

YEAR and MONTH are most useful as helper columns for grouping and summarising time-series data. Add a column with =YEAR(date_column) and another with =MONTH(date_column), then use SUMIFS, COUNTIFS or PivotTables to group by those values. This is often cleaner than using Excel's date grouping in PivotTables, which can behave unexpectedly.

For dynamic labels in charts and reports, combine YEAR and TEXT: ="Q"&ROUNDUP(MONTH(A2)/3,0)&" "&YEAR(A2) produces "Q2 2026" from any date. This label updates automatically as dates change — no manual editing needed.

To compare the same month across years (year-on-year analysis), use MONTH to match months: =SUMPRODUCT((YEAR(dates)=2026)*(MONTH(dates)=MONTH(TODAY()))*values) sums values in the current month of 2026. Change the year to compare 2025 vs 2026 for the same month.

Practise this formula live

ExcelPro has exercises covering this formula across multiple tracks. Free to start.

Try exercises →

Related formulas

DATEDIF EOMONTH EDATE TEXT WORKDAY TODAY

Excel YEAR, MONTH & DAYexplained simply