Combines values for a year, month, and day into a date
Sample Usage
DATE(2023, 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(2023, 5, 29) = TODAY(), "Vacation!").
- The month value is restricted to numeric values 1-12.
- The day value is restricted to numeric values 1-31.
- If you enter a day value that’s more than the maximum days in the month and is <=31, the system advances the final date value to the next month to account for the extra days, for example:=DATE(2023,11,31) shows the date December 1st, 2023 . This protects the DATE function in cases where you’re using an existing date in the sheet to create a new date where the original month has more days than the new one.
When using LEFT, RIGHT, or MID inside of DATE, the system considers the results as text. You can add VALUE around each one to change it into a numeric value.
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/23 |
| 2 | Pants | 1,491.00 | 42 | 02/12/23 |
| 3 | Jacket | 812.00 | 217 | 02/15/23 |
Given the table above, here are some examples of using DATE in a sheet:
| Formula | Description | Result |
|---|---|---|
| =DATE(2023, 7, 10) | Combines numbers for the year (2023), month (7), and day (10) into a date | 07/10/23 |
| =INDEX([Clothing Item]:[Clothing Item], MATCH(DATE(2023, 2, 12), [Sold Date]:[Sold Date])) | Returns the value in the Clothing Item column for the row that contains the date 02/12/2023 in the Sold Date column. Row 2 applies. | Pants |
| =SUMIF([Sold Date]:[Sold Date], DATE(2023, 2, 15), [Transaction Total]:[Transaction Total]) | Sums values in the Transaction Total column that have 02/15/2023 in the corresponding Sold Date column. Row 3 applies. | 812.00 |
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.