Spreadsheet formulas explained, side by side

DATE Function in Excel, Google Sheets, Airtable & Notion

DATE turns three numbers — year, month and day — into a real date value that you can format, sort and calculate with. Months and days that overflow simply roll over into the next month or year.

Syntax in each app

AppSupportSyntax & notes
Excel✔ Built inDATE(year, month, day)
Google Sheets✔ Built inDATE(year, month, day)
Airtable≈ WorkaroundDATETIME_PARSE({Year} & "-" & {Month} & "-" & {Day}, "YYYY-M-D")
Airtable has no DATE constructor; build the text and parse it. That does not roll over a month of 13 or a day of 0, so the copy-ready formula switches to DATEADD steps when the month or day is out of range.
Notion≈ WorkarounddateAdd(dateAdd(parseDate(format(year) + "-01-01"), month - 1, "months"), day - 1, "days")
Notion's date() returns the day of the month, not a new date. Start from 1 January of the year and add months and days; this rolls over exactly like Excel.
Try DATE with your own values
Try 14 or 0 to see the roll-over
Try 0 for the last day of the previous month
Result (as Excel & Google Sheets return it)
Fri, Mar 15, 2024 2024-03-15 · stored as serial number 45366 in Excel and Sheets
Your formula, ready to paste
Excel=DATE(2024, 3, 15) open excel.new ↗
Google Sheets=DATE(2024, 3, 15) open sheets.new ↗
Airtable
workaround
DATETIME_PARSE(2024 & "-" & 3 & "-" & 15, "YYYY-M-D")
Notion
workaround
dateAdd(dateAdd(parseDate(format(2024) + "-01-01"), 2, "months"), 14, "days")

Arguments

ArgumentWhat it means
yearThe year. In Excel and Google Sheets a value from 0 to 1899 is added to 1900, so 24 means 1924, not 2024. Always use four digits.
monthThe month, 1–12. 13 is January of the next year, 0 is December of the previous year.
dayThe day of the month. 0 is the last day of the previous month; 32 in January is 1 February.

Examples

FormulaResultWhyCopy
=DATE(2024, 3, 15)2024-03-15Serial number 45366.
=DATE(2024, 14, 1)2025-02-01Month 14 rolls over into the next year.
=DATE(2024, 3, 0)2024-02-29Day 0 is the last day of the previous month — a handy month-end trick.
=DATE(A2, B2, C2)Date from three columnsTypical use: year, month and day stored in separate cells.

Examples use Excel / Google Sheets syntax unless noted.

How DATE works

Excel and Google Sheets store a date as a serial number: the count of days since 30 December 1899. 1 January 1900 is 1 in Excel (2 in Google Sheets, see below), 15 March 2024 is 45366, and a time of day is the fractional part, so noon is 0.5. That is why you can subtract two dates to get the days between them, and why a date sometimes appears as a plain number like 45366 — the cell just has the wrong number format. DATE is the reliable way to create that number from parts, much safer than typing "3/15/2024", which is read differently depending on the regional settings.

Excel's day count carries an old bug inherited from Lotus 1-2-3: it treats 1900 as a leap year and has a fake 29 February 1900. Google Sheets does not, which is why its serial numbers start at 30 December 1899 = 0 and the two programs only agree from 1 March 1900 onwards. For modern dates the numbers are identical. (Old Mac workbooks may use the 1904 date system, which shifts every serial by 1462 days.)

DATE rolls over out-of-range values instead of complaining, which makes it useful for date maths: =DATE(YEAR(A2), MONTH(A2) + 1, 1) is the first of next month and =DATE(YEAR(A2), MONTH(A2) + 1, 0) the last day of this month (the same as EOMONTH). Airtable and Notion have no constructor, so their formulas build the date from text or from 1 January plus months and days.

Frequently Asked Questions

Why does my DATE formula show a number like 45366?
That is the date's serial number. Format the cell as a date (Format → Number → Date in Google Sheets, Ctrl+1 in Excel).
Why does DATE(24, 1, 1) give 1924?
Years from 0 to 1899 are added to 1900. Always pass a four-digit year.

Part of the Date & Time functions. Results in the tester follow Excel and Google Sheets behaviour; always double-check important work in your own file.