Lessons Lesson 75

YEAR, MONTH and DAY

About this lesson

YEAR, MONTH and DAY each take a date and return one part of it as a plain number.

By the end you can

  • pull the year, month or day out of a date.
  • group or filter by month using MONTH.

Practises YEAR MONTH DAY TEXT

By Ali, Founder, Excel Lessons

The idea

One argument each, one number back. MONTH returns 1 to 12, not a name. If you want the name, that is TEXT with an mmmm format, and it is a different job.

The reason to use these is almost never display. It is grouping. A column of dates is too specific to summarise. Every value is different, so counting by date tells you nothing. Pull the year or the month into its own column, and suddenly COUNTIF, SUMIF and every pivot have something to group on. The one to be careful with is DAY. It gives the day of the month, not the day of the week. That is WEEKDAY. Mixing them up produces a report that looks right and means nothing.

The mistake to watch for

Grouping a report by MONTH alone. Month numbers repeat every year. So the moment the data crosses a year end, two Januaries are added together, with no error to show it. Group by year and month together, or by the month's start date. And DAY is the day of the month, not the day of the week. That is WEEKDAY. Mixing them up produces a report that looks right and means nothing.

Read more about this

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

YEAR takes a date and returns the year as a number.

In C2, type:

=YEAR(B2)

C2
Row A B C D E F G
1 Invoice Date Year Month Day
2 INV-0091 45000
3 INV-0092 45291
4 INV-0093 45658
5 INV-0094 44927
6
7
8

Every step

  1. 1

    YEAR takes a date and returns the year as a number. In C2, type: =YEAR(B2).

    Hint. It takes one argument: the date.

  2. 2

    In D2, pull the month from B2 with MONTH.

    Hint. It takes one argument: the date.

  3. 3

    In E2, pull the day from B2 with DAY.

    Hint. It takes one argument: the date.

  4. 4

    In C5, say if the fourth invoice falls in December. Shape: =MONTH(cell)=n. It returns TRUE or FALSE.

    Hint. December is month 12.

  5. 5

    INV-0093 in B4 is dated 1 January 2025. Read =MONTH(B4) and say what it returns.

    Hint. Which month is that date in, and what is its number?