Skip to content

Excel function · Date & time

Excel DATE function

DATE turns separate year, month and day numbers into a real date that Excel can calculate with. Use it to join date parts from exported data and to find the start or end of a month.

In Turkish Excel: TARİH

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

English Excel=DATE(year, month, day)
Turkish Excel=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

ArgumentRequiredDescription
yearRequiredA four-digit year, e.g. 2026. Avoid two digits: 26 is read as 1926.
monthRequiredThe month number. Above 12 it rolls into later years and below 1 into earlier ones: DATE(2026,14,1) is 1 February 2027.
dayRequiredThe 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.

D2fx=DATE(A2,B2,C2)
Join year, month and day from separate columns: example table
ABCD
1YearMonthDayDate
220261033-Oct-2026
32026101717-Oct-2026
420261122-Nov-2026
52026122828-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.

B2fx=DATE(YEAR(A2),MONTH(A2),1)
Start of month, end of month and first day of next month: example table
ABCD
1Invoice DateMonth StartMonth EndNext Month Start
231-Jan-20261-Jan-202631-Jan-20261-Feb-2026
314-Feb-20261-Feb-202628-Feb-20261-Mar-2026
45-Nov-20261-Nov-202630-Nov-20261-Dec-2026
520-Dec-20261-Dec-202631-Dec-20261-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 &.

D2fx=SUMIFS(B2:B6,A2:A6,">="&DATE(2026,10,1),A2:A6,"<"&DATE(2026,11,1))
Build a monthly total condition with DATE: example table
ABCD
1DateAmountOctober Total
229-Sep-20261,2003,000
31-Oct-2026800
415-Oct-20261,500
531-Oct-2026700
61-Nov-2026950

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

Type this in Nexell

Combine the year, month and day that arrive in separate columns into a single Date column

Convert to table

Bring 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.

Related functions

All date & time functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free