Combines values for a year, month, and day into a date.
Sample Usage
DATE(2013, 10, 14)
Syntax
DATE(
-
year
-
month
-
day
- year—The year component of the date, provided in YYYY format.
- month—The month component of the date, provided in MM format.
- day—The day component of the date, provided in DD format.
Usage Notes
- You must use DATE in a Date column unless you're nesting it within another function, for example =IF(DATE(2017, 5, 29) = TODAY(), "Vacation!"). For more information, see the Column Types article.
- The month value is restricted to 1-12 and the day value is limited to the maximum amount of days in the given month.
Examples
This example references the following sheet information:
| Clothing Item | Transaction Total | Units Sold | Sold Date | |
|---|---|---|---|---|
| 1 | T-Shirt | 1,170.00 | 78 | 07/10/19 |
| 2 | Pants | 1,491.00 | 42 | 02/12/19 |
| 3 | Jacket | 812.00 | 217 | 02/15/19 |
Given the table above, here are some examples of using DATE in a sheet:
| Formula | Description | Result |
|---|---|---|
| =DATE(2019, 7, 10) | Combines numbers for the year (2019), month (7), and day (10) into a date. | 07/10/19 |
| =INDEX([Clothing Item]:[Clothing Item], MATCH(DATE(2019, 2, 12), [Sold Date]:[Sold Date])) | Returns the value in the “Clothing Item” column for the row that contains the DATE “02/12/2019” in the “Sold Date” column. Row 2 applies. | Pants |
| =SUMIF([Sold Date]:[Sold Date], DATE(2019, 2, 15), [Transaction Total]:[Transaction Total]) | Sums values in the “Transaction Total” column that have “02/15/2019” in the corresponding “Sold Date” column. Row 3 applies. | 812.00 |