DATE function
DATE builds a real date from a year, month and day. Use it when the parts sit in separate columns, or to calculate dates like the first of a month. Because it rolls overflow forward, month 13 becomes January of the next year and day 0 becomes the last day of the previous month.
- Available in
- Every current version
- Category
- Date functions
- Returns
- A date serial number. Format the cell as a date to see it as one.
- Google Sheets
- Works the same in Google Sheets.
Syntax
=DATE(year, month, day)
| Argument | Required? | What it is |
|---|---|---|
year | Required | The year as a four-digit number, such as 2026. |
month | Required | The month number. Values above 12 or below 1 roll into the next or previous year. |
day | Required | The day of the month. Values past the month's end roll into the next month; 0 gives the last day of the previous month. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| F2 | =DATE(A2,B2,C2) | 7/15/2026 | Builds July 15, 2026 from three numbers |
| F3 | =DATE(A3,B3,C3) | 2/1/2027 | Month 14 rolls over into February of the next year |
| F4 | =DATE(YEAR(E2),MONTH(E2),1) | 9/1/2026 | First day of the month for the order date in E2 |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Year | Month | Day | Order date | Result | |
| 2026 | 7 | 15 | 9/18/2026 | 7/15/2026 | |
| 2026 | 14 | 1 | 2/1/2027 | ||
| 2026 | 3 | 0 | 9/1/2026 |
Practice DATE
From lesson 17, DATE, YEAR, MONTH & TEXT. Graded instantly, then re-checked on changed data.
Build due dates
I2, build a real date from the year, month and day in F2:H2. Fill down to I6.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| A number like 46218 instead of a date | The formula is right; the cell is formatted as General. Apply a date format. |
| Wrong date when the year is two digits | DATE(26,7,15) means the year 1926, because Excel adds 1900 to years from 0 to 1899. Always pass four-digit years. |
| #VALUE! | One of the parts is text that isn't a number, such as "July". Pass the month as a number from 1 to 12. |
Tips
- DATE(year, month + 1, 0) returns the last day of a month, a handy alternative to EOMONTH.
- Build dates from text parts with DATE(LEFT(A2,4), MID(A2,5,2), RIGHT(A2,2)) when an export gives you 20260715.
Questions
How do I combine year, month and day columns into one date?
Use =DATE(A2,B2,C2) with the year, month and day cells in that order, then format the result as a date.
Why does DATE return a number?
Excel stores dates as numbers counting days from January 1, 1900. The value is correct; change the cell format to Date to display it.
Can DATE handle a month greater than 12?
Yes. Extra months roll into the next year, so DATE(2026,14,1) returns February 1, 2027. This makes date arithmetic like adding months reliable.
Lessons that use DATE
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.