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
| App | Support | Syntax & notes |
|---|---|---|
| Excel | ✔ Built in | DATE(year, month, day) |
| Google Sheets | ✔ Built in | DATE(year, month, day) |
| Airtable | ≈ Workaround | DATETIME_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 | ≈ Workaround | dateAdd(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. |
| 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") |
Excel & Sheets use cell ranges. Paste this sample data into cell A1 of a blank sheet, then paste the formula into any empty cell.
Arguments
| Argument | What it means |
|---|---|
year | The 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. |
month | The month, 1–12. 13 is January of the next year, 0 is December of the previous year. |
day | The day of the month. 0 is the last day of the previous month; 32 in January is 1 February. |
Examples
| Formula | Result | Why | Copy |
|---|---|---|---|
=DATE(2024, 3, 15) | 2024-03-15 | Serial number 45366. | |
=DATE(2024, 14, 1) | 2025-02-01 | Month 14 rolls over into the next year. | |
=DATE(2024, 3, 0) | 2024-02-29 | Day 0 is the last day of the previous month — a handy month-end trick. | |
=DATE(A2, B2, C2) | Date from three columns | Typical 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.