Function guides · Date
DATE function
Builds a date from separate year, month and day numbers.
Syntax
=DATE(year, month, day)
| Argument | What it is |
|---|---|
year | The year as a four-digit number. |
month | The month, 1 to 12. Numbers outside that range roll into the next or previous year. |
day | The day of the month. Numbers outside the month roll into the next or previous month. |
What DATE does
DATE builds a date from three numbers: the year, the month and the day. The result is a proper Excel date that sorts, subtracts and formats like any other.
Most dates are typed rather than built, so why does DATE matter? Because data arrives in pieces, and because dates inside criteria must not depend on regional settings. ">="&DATE(2026,1,1) means the same thing on every computer.
It is also forgiving. Months and days outside their normal ranges roll over, so DATE(2026,13,1) is January 1, 2027, and a day of 0 is the last day of the previous month.
The cell format decides how the result looks. Without a date format you see a five-digit number.
One more habit: when you need a date in the middle of a formula, build it with DATE instead of typing a text date. DATE(2026,12,31) cannot be misread. The text “12/31/2026” is read differently on a computer set to day-month order, and “1/2/2026” is read as January 2 or as February 1 depending on the settings.
DATE examples
1. Assemble a date from three cells
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Year | Month | Day | |
| 2 | 2026 | 3 | 14 | 03/14/2026 |
Imports often store the year, month and day in separate columns. DATE joins them into a real date that sorts and subtracts correctly. The cell is formatted as mm/dd/yyyy so the serial number reads as a date.
2. Months roll over
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Year | Month | Day | |
| 2 | 2026 | 14 | 1 | 02/01/2027 |
A month of 14 is accepted and moves into the next year. DATE(2026,14,1) is February 1, 2027. This makes it easy to add months, though EDATE is clearer for that job.
3. Day zero is the last day of the previous month
| A | B | C | |
|---|---|---|---|
| 1 | Year | Month | |
| 2 | 2026 | 3 | 02/28/2026 |
A day of 0 steps back from the first of the month. DATE(2026,3,0) is the last day of February 2026, the 28th. Using month plus one with day 0 is a classic way to find the end of any month.
Common DATE mistakes
- Typing a date as text.
="3/14/2026"is text, not a date. Use DATE, or enter 3/14/2026 without a formula. - Two-digit years. DATE(26,3,14) is read as 1926. Always use four digits.
- Seeing a number instead of a date. The cell shows 46095 because it is formatted as General. Apply a date format.
DATE on Excel job tests
Use DATE to build thresholds and to reassemble split dates: "create a date from the year, month and day columns", "return the first day of the month for each order", "flag orders before January 1, 2026".
Inside criteria, DATE keeps the formula independent of regional settings: ">="&DATE(2026,1,1) means the same on every computer. For the first of a month use DATE(YEAR(A2),MONTH(A2),1). For the last use EOMONTH or DATE(YEAR(A2),MONTH(A2)+1,0).
A correct result with a General format looks like 46095. Apply a date format and confirm one row by eye.
Related functions
DATE questions
- What does DATE return?
- A date serial number, displayed as a date when the cell has a date format.
- How do I get the first day of a month?
=DATE(YEAR(A2),MONTH(A2),1).- How do I get the last day of a month?
=EOMONTH(A2,0)or=DATE(YEAR(A2),MONTH(A2)+1,0).- Why does DATE accept a month of 13?
- It rolls forward into the next year, so 13 becomes January of the following year.
Practice DATE
Try it in a working grid: the free lesson DATE, YEAR, MONTH and DAY (module 10 of Everyday Excel) has checked DATE exercises.
Practice this function