Excel Practice
Lessons Lesson 76

DATE: Building One from Pieces

About this lesson

DATE takes a year, a month and a day as three separate numbers. It returns the date they make.

By the end you can

  • build a date from a year, a month and a day with DATE.
  • add months to a date without counting days.

Practises DATE YEAR MONTH DAY TEXT EOMONTH

The idea

Year first, then month, then day. That is not the order most people would say it out loud. It is the only thing to remember. It is the exact opposite of YEAR, MONTH and DAY, which take a date apart.

What makes it more than a convenience is that it does not insist the numbers are valid. Month 13 becomes January of the following year. Day 0 becomes the last day of the previous month. Day 32 rolls into the next one. That is deliberate. It is the neatest way to do arithmetic in months. Plain addition cannot do that, because months are not all the same length. Want the same day next month? Add one to the month and let DATE sort it out. Want the last day of a month? Build the first of the next one and subtract a day. That is exactly what EOMONTH does. It is good to be able to do it by hand first.

The mistake to watch for

Putting the arguments in the order people say a date. DATE takes the year first, then the month, then the day. DATE(14,3,2024) is not an error. It is a real date in the year 14, with a month rolled over many times. It looks like a date on screen and it is nonsense. Year, month, day, every time.

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

The three parts of a date are sitting in B2, B3 and B4. Build the date itself in C2.

To begin, type it exactly:

=DATE(B2,B3,B4)

C2
Row A B C D E F G
1 Field Value Built
2 Year 2024
3 Month 3
4 Day 14
5
6 Month number 13
7 Quarter starts 4
8

Every step

  1. 1

    The three parts of a date are sitting in B2, B3 and B4. Build the date itself in C2. To begin, type it exactly: =DATE(B2,B3,B4).

    Hint. Year, month, day.

  2. 2

    Show what that actually is. Read =TEXT(DATE(B2,B3,B4),"dddd d mmmm yyyy") and say what date it names.

    Hint. dddd is the weekday name.

  3. 3

    DATE copes with numbers that do not exist. Read =TEXT(DATE(2024,B6,1),"mmm yyyy"), month thirteen of 2024, and say what comes out.

    Hint. It does not refuse.

  4. 4

    Use that. In C5, build the first day of the quarter that starts in the month given in B7. Use the year in B2.

    Hint. The day is the number 1.

  5. 5

    In C6, use DATE to build the last day of February 2024. Get it right for a leap year.

    Hint. Go forward to a date you are sure of, then step back.