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 |
Still need help?
Use the Formula Handbook template to find more support resources, and view 100+ formulas, including a glossary of every function that you can practice working with in real time, and examples of commonly used and advanced formulas.
Find examples of how other Smartsheet customers use this function or ask about your specific use case in the Smartsheet online Community.