What it does
=DATE(2026,11,5) produces 5 November 2026. The result is a real date, not text: it can be sorted, subtracted from another date and used as a criterion in functions like SUMIFS. When a system exports the year, month and day in separate columns, this is the safest way to turn them into one date.
DATE corrects values that overflow: month 13 is January of the next year, and day 0 is the last day of the previous month. So =DATE(YEAR(A2),MONTH(A2)+1,1) gives the first day of the next month and =DATE(YEAR(A2),MONTH(A2)+1,0) the last day of A2's month.
Typing a date into a formula as text, such as "10/01/2026", depends on the computer's regional settings; DATE gives the same result everywhere. To take a date apart use YEAR, MONTH and DAY; to add months use EDATE; for month ends you can also use EOMONTH.
Syntax
=DATE(year, month, day)=TARİH(yıl; ay; gün)Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
year | Required | A four-digit year, e.g. 2026. Avoid two digits: 26 is read as 1926. |
month | Required | The month number. Above 12 it rolls into later years and below 1 into earlier ones: DATE(2026,14,1) is 1 February 2027. |
day | Required | The day of the month. Past the end of the month it rolls into the next month; 0 is the last day of the previous month. |
Examples
1Join year, month and day from separate columns
In a file exported from an accounting system, the date is split across three columns. The formula in D2 is filled down and turns each row into a real date.
=DATE(A2,B2,C2)| A | B | C | D | |
|---|---|---|---|---|
| 1 | Year | Month | Day | Date |
| 2 | 2026 | 10 | 3 | 3-Oct-2026 |
| 3 | 2026 | 10 | 17 | 17-Oct-2026 |
| 4 | 2026 | 11 | 2 | 2-Nov-2026 |
| 5 | 2026 | 12 | 28 | 28-Dec-2026 |
- D2 (filled down to D5)
=DATE(A2,B2,C2)
D2 shows 3-Oct-2026 and D5 shows 28-Dec-2026. Column D can now be sorted and used to work out the days between dates.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Start of month, end of month and first day of next month
All three formulas take the date apart with YEAR and MONTH and rebuild it. For the month end, ask for day 0 of the next month; for the next month's start, month + 1 and day 1.
=DATE(YEAR(A2),MONTH(A2),1)| A | B | C | D | |
|---|---|---|---|---|
| 1 | Invoice Date | Month Start | Month End | Next Month Start |
| 2 | 31-Jan-2026 | 1-Jan-2026 | 31-Jan-2026 | 1-Feb-2026 |
| 3 | 14-Feb-2026 | 1-Feb-2026 | 28-Feb-2026 | 1-Mar-2026 |
| 4 | 5-Nov-2026 | 1-Nov-2026 | 30-Nov-2026 | 1-Dec-2026 |
| 5 | 20-Dec-2026 | 1-Dec-2026 | 31-Dec-2026 | 1-Jan-2027 |
- B2 (filled down to B5)
=DATE(YEAR(A2),MONTH(A2),1) - C2 (filled down to C5)
=DATE(YEAR(A2),MONTH(A2)+1,0) - D2 (filled down to D5)
=DATE(YEAR(A2),MONTH(A2)+1,1)
The month end for February 2026 is 28-Feb-2026. For the December invoice the month rolls over to 13 and the next month's start moves into the next year: 1-Jan-2027.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
3Build a monthly total condition with DATE
Totalling October sales needs two conditions: the date is on or after 1 October and before 1 November. The boundary dates are built with DATE and joined to the comparison operator with &.
=SUMIFS(B2:B6,A2:A6,">="&DATE(2026,10,1),A2:A6,"<"&DATE(2026,11,1))| A | B | C | D | |
|---|---|---|---|---|
| 1 | Date | Amount | October Total | |
| 2 | 29-Sep-2026 | 1,200 | 3,000 | |
| 3 | 1-Oct-2026 | 800 | ||
| 4 | 15-Oct-2026 | 1,500 | ||
| 5 | 31-Oct-2026 | 700 | ||
| 6 | 1-Nov-2026 | 950 |
Three rows are added, including those on the first and last day of October: 3,000. Because the condition is built with DATE, the formula gives the same result on a computer with different regional settings.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- #NUM!
Cause: The year is below 0 or above 9999, or the result falls before 1 January 1900.
Fix: Check the year value and the formula that produces it.
- #VALUE!
Cause: One of the arguments is text that cannot be read as a number, for example "November" in the month column.
Fix: Turn the month name into a number, or find its position in a list of months with MATCH.
- Wrong century
Cause: The year was given with two digits;
DATE(26,1,1)produced 1926.Fix: Write four-digit years, or add 2000 to two-digit ones.
Good to know
- In functions that need a date criterion, DATE removes the regional format problem:
">="&DATE(2026,10,1). - If the result shows as a number such as 46331, apply a date format to the cell.
In Nexell
Get Nexell to do it from a sentence
Combine the year, month and day that arrive in separate columns into a single Date column
Convert to tableBring your table into the tool and type this. Nexell adds the Date column with real Excel formulas and shows it in the preview; in the downloaded file, the date changes when one of its parts does.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
How do I find the last day of a month in Excel?
Ask for day 0 of the next month: =DATE(YEAR(A2),MONTH(A2)+1,0). =EOMONTH(A2,0) gives the same result (EOMONTH).
How do I turn day, month and year in separate columns into a date?
With the year in A2, the month in B2 and the day in C2, use =DATE(A2,B2,C2). The result is a real date you can sort and calculate with.