Practice free

DATE function

DATE builds a real date from separate year, month and day numbers and returns a date serial number. It is handy for assembling dates from split columns or shifting a date by adding to the month or day.

Example

Read it as a sentence: turn the year 2024, month 1, and day 31 into a date, which shows as the serial number 45322 until you format it.

Practice this in the app

Syntax

DATE(year, month, day)

year (required)
Year number; 1900 to 9999.
month (required)
Month number; values over 12 roll into later years, values below 1 roll back.
day (required)
Day of the month; overflow or zero rolls into neighbouring months.

Returns: A date serial number (format the cell as a date to see it).

What Excel actually returns

Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.

FormulaExcel showsCase
=DATE(2026,1,9) 1/9/2026 Basic
=DATE(2026,13,1) 1/1/2027 Month past 12 rolls into next year
=DATE(2026,1,0) 12/31/2025 Day 0
=DATE(2026,0,1) 12/1/2025 Month 0
=DATE(2026,2,30) 3/2/2026 Day past month end
=DATE(1900,2,29) 2/29/1900 Leap-year bug date
=DATE(1900,1,-1) #NUM! Before serial 0
=DATE(-1,1,1) #NUM! Negative year

Watch out for

  • Result shows as a plain number until the cell is formatted as a date.
  • Months and days out of range roll over instead of erroring, so DATE(2024,14,1) is February 2025.
  • A year between 0 and 1899 is added to 1900, which gives odd results.

Where it works

Excel
DATE is in every version of Excel from 2007 on.
Google Sheets
Same name, same behaviour for everyday use.

Part of Date and time functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .