Breaking Apart Dates With YEAR, MONTH, DAY

A date like 2026-06-18 packs three facts into one cell: a year, a month, and a day. Often you need just one piece. Maybe you want to group sales by month, or filter records to a single year.

Three small functions pull each part out as a plain number: YEAR, MONTH, and DAY. Each takes a single date and returns its corresponding component.

In this lesson you will learn all three and how to use them together.

The YEAR function takes a date and returns its four-digit year as a number. If cell A2 holds 2026-06-18, then =YEAR(A2) returns 2026.

The result is a regular number, not a date, so you can compare it, sum it, or use it inside other formulas. For instance, you can test whether a record belongs to a target year.

The MONTH function returns the month as a number from 1 to 12. For 2026-06-18, =MONTH(A2) returns 6 because June is the sixth month.

Note that it gives the number, not the name. You will see how to turn 6 into the word June in a later scene using the TEXT function.

The DAY function returns the day of the month, a number from 1 to 31. For 2026-06-18, =DAY(A2) returns 18.

Do not confuse this with the day of the week. DAY gives the calendar date number. To get the weekday (Monday, Tuesday, and so on) you use a different function called WEEKDAY.

Often you place these three in adjacent columns to break a date table into parts. Suppose dates live in column A starting at A2:

• =YEAR(A2) in column B
• =MONTH(A2) in column C
• =DAY(A2) in column D

Now you can sort, filter, or pivot by any single component. This is a classic first step before building monthly or yearly summaries.

A bare 6 is not very friendly in a report. The TEXT function can format a date directly into a month name without extracting a number first.

The pattern "MMMM" spells the full month name, and "MMM" gives the short form. So =TEXT(A2,"MMMM") turns 2026-06-18 into June.

This is cleaner than mapping MONTH numbers to names by hand.

To find which day of the week a date falls on, use WEEKDAY. By default it returns 1 for Sunday through 7 for Saturday.

To show the name instead of a number, reach for TEXT again with the pattern "DDDD" for the full weekday or "DDD" for the short form.

So =TEXT(A2,"DDDD") might return Thursday.

Say you have orders with dates in column A and amounts in column B. You want a helper column that labels each row with its month name so you can summarize sales per month.

Put this in column C and fill it down. Every row now carries a readable month label that you can group on.

Later you could feed this label into SUMIF to total sales for each month.

There is no built-in QUARTER function, but you can build one from MONTH. A quarter is the month number divided by three, rounded up.

The ROUNDUP function rounds toward the next whole number. So months 1 to 3 give quarter 1, months 4 to 6 give quarter 2, and so on.

This shows the real power of extraction: once you have the raw number, you can compute anything from it.

These functions need a real date, not text that merely looks like one. If a cell holds "2026-06-18" typed as text, YEAR may throw a #VALUE! error.

You can spot the difference: real dates usually align to the right, while text aligns to the left. To convert text into a true date, wrap it in the DATEVALUE function first.

So =YEAR(DATEVALUE(A2)) safely handles a text date.

Extraction also lets you reset a date to the first day of its month, handy for grouping. Take the year and month from a date, then force the day to 1 with the DATE function.

So =DATE(YEAR(A2), MONTH(A2), 1) turns any date in A2 into the first of that same month. Every date in June becomes June 1.

Now a whole column of mixed dates collapses to one value per month, perfect for a monthly summary.

Test your understanding of date parts.

You learned to pull components out of a date:

• YEAR(date) returns the four-digit year.
• MONTH(date) returns 1 to 12.
• DAY(date) returns the day of the month, 1 to 31.
• Use TEXT(date,"MMMM") for a month name and TEXT(date,"DDDD") for a weekday name.
• Watch for text that only looks like a date; convert it with DATEVALUE first.

Next you will go the other way and build dates from parts.