Excel YEAR, MONTH, DAY Functions: Complete Guide (2026)
Home›Functions›Date & Time›YEAR, MONTH, DAY
ExcelFunction Reference⏱ 7 min read

Excel YEAR, MONTH, DAY Functions: Complete Guide

YEAR, MONTH, and DAY extract the components of a date. Simple functions that show up everywhere: filtering data by year, grouping by month, calculating birthdays, and rebuilding dates for custom period math. This guide covers all three together because they work as a family.

Syntax

YEAR(serial_number)
MONTH(serial_number)
DAY(serial_number)
FunctionReturnsRange
YEAR(date)The year as a 4-digit number1900-9999
MONTH(date)The month as a number1-12
DAY(date)The day of the month1-31

All three accept a single argument — a date, or something that resolves to a date. All three return integers.

Basic examples

Extracting parts of September 6, 2026

=YEAR("2026-09-06")   → 2026
=MONTH("2026-09-06")  → 9
=DAY("2026-09-06")    → 6

From a date cell

=YEAR(A1)
=MONTH(A1)
=DAY(A1)
Same result — extracts the components of whatever date is in A1.

Common uses

1. Extract the year from a date column

New column for YoY analysis

=YEAR(A2)
Fills with 2024, 2025, 2026, etc. Now you can group PivotTables by year, or filter to a specific year.

2. Group by month across years

Seasonality analysis

=MONTH(A2)
Returns 1-12. Grouping by this shows monthly patterns regardless of year.

3. Check if a date is in the current year

YTD filter

=YEAR(A2) = YEAR(TODAY())
TRUE for this year's dates, FALSE otherwise. Use in a helper column or COUNTIFS.

4. Birthday reminder

"Birthday this month?"

=MONTH(A2) = MONTH(TODAY())
Rows where the birth month matches the current month.

5. Rebuild date without time

Strip time from a datetime

=DATE(YEAR(A1), MONTH(A1), DAY(A1))
Removes the time component. Same as =INT(A1), but more explicit.

6. Same day next month

Rolling forward by month

=DATE(YEAR(A1), MONTH(A1) + 1, DAY(A1))
Same day of the following month. See EDATE for a cleaner alternative with better edge-case handling.

7. Quarter from a date

Calendar quarter (1-4)

=ROUNDUP(MONTH(A1) / 3, 0)
Jan-Mar → 1, Apr-Jun → 2, Jul-Sep → 3, Oct-Dec → 4

Q + number label

="Q" & ROUNDUP(MONTH(A1) / 3, 0)
Returns "Q1", "Q2", "Q3", or "Q4"

8. Fiscal year (April start)

UK/Australia FY

=IF(MONTH(A1) >= 4, YEAR(A1), YEAR(A1) - 1)
Returns the FY year that A1 falls in.

9. Age at the year mark (rough)

Age by end of year

=YEAR(TODAY()) - YEAR(A1)
Simple year subtraction. Doesn't account for whether the birthday has passed. For exact age, use DATEDIF.

10. Precise age (birthday check)

Correct age today

=YEAR(TODAY()) - YEAR(A1) - (DATE(YEAR(TODAY()), MONTH(A1), DAY(A1)) > TODAY())
Subtracts 1 if the birthday hasn't happened yet this year.

MONTH returns a number — for names use TEXT

MONTH always returns 1-12. For month names:

FormulaResult
=MONTH("2026-09-06")9
=TEXT("2026-09-06", "m")9
=TEXT("2026-09-06", "mm")09 (with leading zero)
=TEXT("2026-09-06", "mmm")Sep
=TEXT("2026-09-06", "mmmm")September

Extracting from datetime values

When a cell contains a datetime like "September 6, 2026 3:47 PM":

=YEAR(A1)   → 2026
=MONTH(A1)  → 9
=DAY(A1)    → 6
=HOUR(A1)   → 15
=MINUTE(A1) → 47

YEAR, MONTH, and DAY ignore the time portion. HOUR, MINUTE, and SECOND handle the time part.

The date-to-parts-to-date roundtrip

A common pattern:

Original date → YEAR/MONTH/DAY → modified components → DATE → new date

Example: shift every date in column A to the 15th of the same month.

Normalize to mid-month

=DATE(YEAR(A1), MONTH(A1), 15)
Any date in September becomes September 15.

Common pitfalls

Text dates fail silently

If A1 contains "2026-09-06" as literal text (not a date), MONTH(A1) usually still works because Excel auto-converts. But formats like "6/September/2026" may fail. Always wrap in DATEVALUE for text sources: =MONTH(DATEVALUE(A1)).

MONTH on an empty cell returns 1

An empty cell has value 0, which is January 0, 1900 in Excel's date system. MONTH(A1) returns 1 for an empty cell. Guard with =IF(A1="", "", MONTH(A1)).

Two-digit years in imported data

If import data has 2-digit years, DAY and MONTH still work but YEAR returns the interpreted 4-digit year (2000-2029 for 00-29, 1930-1999 for 30-99). Verify before assuming YEAR = source year.

YEAR + MONTH for date grouping

To group by year-month (e.g., "2026-09" as a text label), use =YEAR(A1) & "-" & TEXT(MONTH(A1), "00"). Padding with "00" ensures sortable strings.

YEAR, MONTH, DAY in Google Sheets

Identical functions in Google Sheets. Same syntax, same behavior, same integer output. Sheets also has an ISOWEEKNUM function that's occasionally handy alongside YEAR/MONTH/DAY.

Related functions

Related

DATE

Constructs a date from year, month, day — the roundtrip partner.

Related

WEEKDAY

Extracts the day of the week (1-7).

Related

TODAY

Common input for "current year" checks.

Related

EDATE

Cleaner than DATE(YEAR+MONTH+DAY) for month shifting.

Excel Wizard

Date extraction, decoded

YEAR/MONTH/DAY plus TEXT combinations power most date-based grouping and filtering. Excel Wizard figures out the right pattern from your intent — "show me monthly totals" builds MONTH-based grouping; "quarterly view" combines MONTH with ROUNDUP.

Install Excel Wizard →

Frequently asked questions

What do YEAR, MONTH, and DAY do in Excel?

They extract the year, month number (1-12), or day-of-month (1-31) from a date. All three take a single date argument.

How do I get the month name instead of the number?

Use TEXT instead of MONTH. =TEXT(A1, "mmmm") returns 'September'; =TEXT(A1, "mmm") returns 'Sep'.

Can I use YEAR, MONTH, DAY on text dates?

Only if Excel recognizes the text as a date. For unreliable formats, wrap in DATEVALUE first: =YEAR(DATEVALUE(A1)).

How do I rebuild a date from its parts?

Use DATE with YEAR, MONTH, DAY: =DATE(YEAR(A1), MONTH(A1), DAY(A1)). Often used to strip time from a datetime.