=DATE(2026,3,15) returns the date March 15, 2026. The three arguments are the year, the month and the day, in that order, as numbers or cell references. The opposite functions, YEAR, MONTH and DAY, take a date apart into those numbers.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Year | Month | Day | Date |
| 2 | 2026 | 3 | 15 | 2026-03-15 |
| 3 | 2026 | 12 | 31 | 2026-12-31 |
| 4 | 2027 | 1 | 1 | 2027-01-01 |
| 5 | 2024 | 2 | 29 | 2024-02-29 |
Each row becomes a real date that Excel can sort, subtract and format. Change the month in B2 to 7 and D2 becomes 2026-07-15. The order is fixed, year first, whatever date order your country writes: =DATE(15,3,2026) is not March 15.
YEAR, MONTH and DAY: take a date apart
Each returns one part of a date as a number. They are the usual way to group or filter by year or month, for example in a helper column that SUMIF or a pivot table reads.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Date | Year | Month | Day | Month name |
| 2 | 2026-03-15 | 2026 | 3 | 15 | March |
| 3 | 2025-11-02 | 2025 | 11 | 2 | November |
| 4 | 2024-02-29 | 2024 | 2 | 29 | February |
The first row gives 2026, 3 and 15. MONTH returns a number; for the month's name use TEXT with "mmmm" (March) or "mmm" (Mar), see TEXT function.
Month 13, day 0: how DATE rolls over
DATE accepts months above 12 and days beyond the end of the month, and carries the extra forward, the way a calendar would. Month 13 is January of the next year, and day 0 is the last day of the previous month. That makes the first and last day of any month a one-line formula.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Formula | Result | Date | |
| 2 | Month 13 | 2027-01-01 | 2026-02-17 | |
| 3 | February 30 | 2026-03-02 | ||
| 4 | Day 0 of March | 2026-02-28 | ||
| 5 | First of the month | 2026-02-01 | ||
| 6 | Last of the month | 2026-02-28 |
DATE(2026,13,1) is 2027-01-01, February 30 becomes 2026-03-02, and day 0 of March is 2026-02-28. Rows 5 and 6 use the date in D2: change it to any date and you get the first and last day of its month, leap years included (try 2024-02-17). EOMONTH does the same job for month ends, see add days and months.
Convert text to a date
Dates imported from other systems often arrive as text. Excel cannot do date maths on text, so convert it first. DATEVALUE reads text in a format Excel recognises, such as 2026-03-15. Codes like 20260315 and European dates like 15.03.2026 need cutting up with LEFT, MID and RIGHT and putting back together with DATE.
| A | B | |
|---|---|---|
| 1 | Text | Date |
| 2 | 2026-03-15 | 2026-03-15 |
| 3 | 20260315 | 2026-03-15 |
| 4 | 15.03.2026 | 2026-03-15 |
All three rows return 2026-03-15. LEFT, MID and RIGHT return text, and DATE converts "2026" and "03" to numbers on its own. DATEVALUE depends on your system's date settings: "3/15/2026" is read as March 15 with US settings and fails with #VALUE! where dates are written day first. For a one-off column, Data > Text to Columns converts in place without formulas: in step 3, choose Date and the order the text uses (DMY for 15.03.2026).
Practice: build the date
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Year | Month | Day | Date |
| 2 | 2026 | 4 | 17 |
Your turn: In D2, build the date from the year in A2, the month in B2 and the day in C2.
| A | B | |
|---|---|---|
| 1 | Code | Date |
| 2 | 20260917 |
Your turn: A2 holds the date as text in the form yyyymmdd. In B2, turn it into a real date.
Common mistake: two-digit years
DATE reads a year from 0 to 1899 as an offset from 1900, so a two-digit year lands in the wrong century without any error.
| A | B | C | |
|---|---|---|---|
| 1 | Year | DATE | Fixed |
| 2 | 26 | 1926-03-15 | 2026-03-15 |
=DATE(26,3,15) returns 1926-03-15, not 2026. Add 2000 when the source data has two-digit years, as in C2, or better, fix the source so it holds the full year.
Frequently Asked Questions
How does the DATE function work in Excel?
=DATE(year,month,day) returns a date from three numbers: =DATE(2026,3,15) is March 15, 2026. The arguments can be cell references, such as =DATE(A2,B2,C2).
How do I get the year, month or day from a date?
=YEAR(A2), =MONTH(A2) and =DAY(A2) return each part as a number. For the month name use =TEXT(A2,"mmmm").
How do I get the first day of the month in Excel?
=DATE(YEAR(A2),MONTH(A2),1) returns the first day of A2's month. =DATE(YEAR(A2),MONTH(A2)+1,0) returns the last day, because day 0 of a month is the last day of the month before.
How do I convert text to a date in Excel?
If the text is in a format Excel recognises, =DATEVALUE(A2) converts it. For codes like 20260315, cut the parts out and rebuild the date: =DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2)).
What happens if the month in DATE is greater than 12?
DATE carries it into the next year: =DATE(2026,13,1) is 2027-01-01 and =DATE(2026,14,1) is 2027-02-01. Days roll over the same way, and zero or negative values go back.
