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

By Ali, Founder, Excel Lessons

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

Grouping by Month: EOMONTH and SUMIFS Dates in Practice
TEXT and VALUE: Converting Between the Two Text: Building and Cleaning
Building and Cleaning: Putting It Together Text: Building and Cleaning
Ages and Years of Service Dates in Practice

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

DATE builds a date from a year, a month and a day.

In `C2`, type:

=DATE(B2,B3,B4)

B2, B3 and B4 hold the year, month and day.

Row	A	B	C
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

DATE builds a date from a year, a month and a day. In C2, type: =DATE(B2,B3,B4). B2, B3 and B4 hold the year, month and day.

Hint. Year, month, day.
2

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

Hint. dddd is the weekday name.
3

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

Hint. It does not refuse.
4

In C5, build the 1st of month B7 in year B2. Shape: =DATE(year,month,1).

Hint. The day is the number 1.
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.