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
Sample usage
DATE(2023, 10, 14)
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.
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.
The system considers the results as text when using LEFT, RIGHT, or MID inside of DATE. 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 |
Still need help?
If you have an Enterprise plan, you can use AI to help write and troubleshoot formulas.
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.