DATE function in Excel

DATE builds a date from a year, a month and a day given as three numbers.

DATE assembles a real date from three separate numbers. It is the safe way to put a date into a formula, and the tool for building one date from the parts of another.

Syntax

=DATE(year, month, day)

Argument	What it is
`year`	The year, as four digits.
`month`	The month number, 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.

Examples

Each result below is what Excel itself showed for the formula on the sample sheet.

Sample sheet: Staff with their date of birth and date of joining, and a short list of holidays.

	A	B	C	D
1	Name	Born	Joined	Holiday
2	Anita	19-Jul-1988	01-Apr-2019	26-Jan-2026
3	Rohit	30-Nov-1992	15-Jun-2020	04-Mar-2026
4	Deepa	29-Feb-1996	01-Sep-2021	15-Aug-2026
5	Manoj	05-Dec-1999	10-Jan-2022	02-Oct-2026

=DATE(2026,11,12)
Result: 12-Nov-2026
12 November 2026, built from three numbers.
=DATE(YEAR(C2),MONTH(C2)+6,DAY(C2))
Result: 01-Oct-2019
Six months after the joining date in C2. Month 10 of 2019.
=DATE(2026,14,1)
Result: 01-Feb-2027
Month 14 does not exist, so DATE rolls over into February of the next year.
=DATE(2026,3,0)
Result: 28-Feb-2026
Day 0 of March is the day before 1 March: the last day of February.
=DATE(2026,MONTH(B2),DAY(B2))
Result: 19-Jul-2026
Anita's birthday in 2026: her own day and month with the year changed.
=DATE(YEAR(C2)+5,MONTH(C2),DAY(C2))
Result: 01-Apr-2024
The fifth anniversary of joining.
=DATE(2026,1,1)+99
Result: 10-Apr-2026
The 100th day of 2026.
=DATE(2026,11,12)-DATE(2026,1,1)
Result: 315
Days from the start of the year. Subtracting dates gives a number.

Why not just type the date?

A date typed inside a formula as text, such as "12/11/2026", is read according to the regional settings of whichever computer opens the file. On an Indian system it is 12 November. On an American one it is 11 December. =DATE(2026,11,12) means the same thing everywhere, because year, month and day each have their own place.

Use DATE whenever a formula needs a fixed date: in the conditions of SUMIFS and COUNTIFS, in comparisons, and in date arithmetic.

How Excel stores a date

A date is a whole number: the count of days since the start of 1900. 1 January 2026 is 46023. The date you see is that number wearing a date format. This is why dates can be subtracted, compared and added to, and why a date cell sometimes shows a five-digit number: its format has been set to General. Change the format back to Date.

Rolling over

DATE does not complain about a month above 12 or a day beyond the end of the month. It carries the extra forward, as an odometer does.

Formula	Result	Why
`=DATE(2026,14,1)`	1 February 2027	month 14 is February of the next year
`=DATE(2026,1,32)`	1 February 2026	the day after 31 January
`=DATE(2026,3,0)`	28 February 2026	the day before 1 March
`=DATE(2026,0,15)`	15 December 2025	the month before January

This behaviour is useful, not a fault. "Day 0 of next month" is the classic way to find the last day of a month: =DATE(YEAR(A2),MONTH(A2)+1,0).

Building one date from another

Take a date apart with YEAR, MONTH and DAY, change one part, and put it back together with DATE.

First day of the month: =DATE(YEAR(A2),MONTH(A2),1)
Last day of the month: =DATE(YEAR(A2),MONTH(A2)+1,0)
First day of the year: =DATE(YEAR(A2),1,1)
Same day next year: =DATE(YEAR(A2)+1,MONTH(A2),DAY(A2))
Start of the Indian financial year: =DATE(YEAR(A2)-(MONTH(A2)<4),4,1)

In the last one, the test MONTH(A2)<4 counts as 1 for January to March, which belong to the financial year that began the previous April.

Adding months safely

=DATE(YEAR(A2),MONTH(A2)+1,DAY(A2)) adds one month, but beware the end of the month: one month after 31 January comes out as 3 March, because February has no 31st and the extra days roll over. EDATE handles this correctly and returns the last day of February.

Dates from text pieces

When a date arrives as text such as 20261112, cut out the pieces and rebuild it: =DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2)). DATE converts the text digits to numbers for you.

Common mistakes

A two-digit year. =DATE(26,11,12) is the year 1926, not 2026. Always give four digits.
The arguments in the wrong order. It is always year, month, day.
Reading the result as a number because the cell is not formatted as a date.
#NUM! for years below 1900 or above 9999.

In Google Sheets

DATE is the same in Google Sheets, including the rolling over of months and days.

Related functions

Every result on this page was calculated in Microsoft Excel (version 16.0, build 20326) on 12 November 2026, not typed by hand.

This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.