DATE, YEAR, MONTH and DAY: build and split dates in Excel

=DATE(year,month,day) builds a real date from 3 numbers. =YEAR(A2), =MONTH(A2) and =DAY(A2) take a date apart and give each part as a number.

Here is an export from a gym booking system. Each member has a join date. The system writes it as 8 digits, year then month then day. Excel sees text, not a date.

Fix a text date with DATE

Column C is what you want. Cut the text into 3 pieces and give them to DATE. In C2, type this and fill it down:

The result is a real date. It sits on the right of the cell, and you can sort it. You can also subtract it: =C5-C2 gives 48 days.

The order inside DATE is always year, month, day. It does not change with the way your country writes dates.

Some systems write 14.12.2026 instead. Then the year is at the end: =DATE(RIGHT(B2,4),MID(B2,4,2),LEFT(B2,2)). It gives the same 14 December 2026.

The same formula works if the export gives the number 20261214, not text. LEFT, MID and RIGHT work on numbers too. For why a date must be a number, see Excel dates are numbers.

Split a date with YEAR, MONTH and DAY

In D2, E2 and F2, type =YEAR(C2), =MONTH(C2) and =DAY(C2). Fill them down. Each one gives a plain number.

MONTH gives 1 to 12, not a name. DAY gives the day of the month, not the day of the week. For Monday or Tuesday you need WEEKDAY, which is a different function.

The first day of any month

The last column above is the month start. Take the year and month from the date, and put 1 as the day:

=C2-DAY(C2)+1 gives the same result. It goes back to day 1 of the month. Use the one you find easier to read.

This column is the one to group a report by, for example in a pivot table. Do not group by MONTH alone. MONTH gives 12 for December 2026 and for December 2027. A report grouped on it adds both years together. The month start keeps them apart.

DATE rolls over, on purpose

DATE does not check that the month is 1 to 12, or that the day exists. It moves the extra into the next month or year:

This is what makes month arithmetic easy. =DATE(YEAR(C2),MONTH(C2)+1,0) gives the last day of the month. For M-105 it gives 28 February 2027, and you did not need to know that February 2027 has 28 days.

Adding a month works the same way. =DATE(YEAR(C2),MONTH(C2)+1,DAY(C2)) gives 30 January 2027 for M-102. The new year was handled for you.

Where the rollover gives the wrong answer

Use the same formula for M-104, who joined on 31 January 2027. DATE is asked for 31 February. It rolls over and gives 3 March 2027. The member has lost February.

For "one month later", use =EDATE(C5,1). It gives 28 February 2027, because EDATE stops at the end of a short month. If the cell shows 46446, format it as a date.

What I do: EDATE for renewals and due dates. DATE with the rollover for month starts and month ends, where the day is 1 or 0.

Two mistakes that do not show an error

The wrong order. =DATE(4,1,2027) is not an error. It takes 4 as the year and 2027 as the day, and gives 19 July 1909. It is a real date, and it is wrong.

A 2-digit year. =DATE(27,1,4) gives 4 January 1927, not 2027. DATE adds 1900 to any year below 1900. Always give the year as 4 digits.

Questions people ask

How do I show the month as a name?

Use TEXT. =TEXT(C2,"mmmm") gives December, and "mmm" gives Dec. The result is text, so use it for labels, not for sorting.

Why does YEAR give 1900 on an empty row?

An empty cell counts as 0, and 0 is a day in January 1900 to Excel. So =YEAR(C9) on an empty C9 gives 1900. Fill the formula only as far as the data goes.

Is there a function for the last day of the month?

Yes. =EOMONTH(C5,0) gives 31 January 2027, the same as the DATE formula with day 0. The working days article shows how to count only Monday to Friday between two dates.

The lessons below use the same functions. Each lesson is a real sheet you type into, and each answer is checked when you press Enter.