Excel Practice
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

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.

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

Pull the year out of the first invoice date, into C2.

To begin, type it exactly:

=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

    Pull the year out of the first invoice date, into C2. To begin, type it exactly: =YEAR(B2).

    Hint. One argument.

  2. 2

    Now the month, into D2.

    Hint. One argument.

  3. 3

    And the day of the month, into E2.

    Hint. One argument.

  4. 4

    In C5, write a formula using MONTH that says whether the fourth invoice falls in December. TRUE or FALSE.

    Hint. December is month 12.

  5. 5

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

    Hint. Which month is 1 January in?