Applies to
- Pro
- Business
- Enterprise
Capabilities
Who can use this capability
- Owner
- Admin
- Editor
Use formulas to perform calculations with dates
You can enter a formula in a cell as part of a Date column to add or subtract numbers from dates in other cells.
Who can use this?
Plans:
- Pro
- Business
- Enterprise
Permissions:
- Owner
- Admin
- Editor
Find out if this capability is included in Smartsheet Regions or Smartsheet Gov.
Users on an Enterprise plan can Use AI to generate formulas or text.
For more information about creating or editing DATE formulas, visit DATE Function.
The following table contains examples of using dates in formulas. The dates in the table are in mm/dd/yy format. Operations on date values treat numbers as days. Operations in DATE functions treat numbers as the type (day, month, year) they operate on.
| Date | Formula | Description | Result | |
|---|---|---|---|---|
| 1 | 5/19/24 | =[Date]@row - 5 | Subtracts five days from the date | 5/14/24 |
| 2 | 12/10/24 | =[Date]@row + 5 | Adds five days to the date | 12/15/24 |
| 3 | 1/20/24 | =DATE(YEAR(date@row), MONTH(date@row)+1,DAY(date@row)) | Adds one month to the date using the DATE, YEAR, MONTH, and DAY functions. This works for all months except December. | 2/20/24 |
| 4 | 12/15/24 | =IFERROR(MONTH(Date@row)<12, DATE(YEAR(Date@row), MONTH(Date@row)+1, DAY(Date@row)), DATE(YEAR(Date@row)+1, 1, DAY(Date@row)) | Adds one month to a date, even a date in December. If it's a December date, the formula sets the resulting month value to | 1/15/25 |
| 5 | 6/10/24 | =IF(MONTH(Date@row)<7, DATE(YEAR(Date@row), MONTH(Date@row)+6, DAY(Date@row)), DATE(YEAR(Date@row)+1, MONTH(Date@row)-6, DAY(Date@row)) | Adds six months to a date. It supports to types of result dates:
| 12/10/24 |
| 6 | 6/11/24 | =Date@row-Date5 | Returns the number of days difference between the dates. If the first date is later than the second date, the value is positive; otherwise, it's negative. This differs from using NETDAYS, which calculates the total elapsed time from the start of the first date to the end of the second date. | 1 |
Keep in mind
- For exercises 4 and 5 in the table above, you can add an approximate number of days with =(Date@row+183), for example. This applies if precision is not required.
- Use the formula =DATE(@row)-DAY(DATE(@row)) to get the last day of the month preceding the date in the cell.
- Use the function =Date@row-DAY(Date@row)+1 to generate the cell value's first day of the month.
- You can't place formulas in date columns used for dependencies.
- The DATE function restricts the month value to a maximum of 12 and the day value to a maximum of 31.
If you input a day value of 31 in a DATE formula for a month with 30 days, it advances the date to the next month. For example, DATE interprets 11/31/2024 as 12/01/2024.
- Date math for formulas recognizes leap years.
- You can’t add two dates ([Start Date]@row+[Due Date]@row). If you do, you get #INVALID OPERATION.
- You can use the AVG, MIN, or MAX functions on a range of dates to find the average date, earliest date, or latest date.
- A blank value results in an error when using any date function. Use the IFERROR function when working with blank values. For example, =IF((IFERROR(MONTH(Date@row),0) = 12 6
Still need help?
The Formula Handbook describes 100+ formulas and includes a glossary of all Smartsheet functions. Use it to practice working with popular and advanced formulas and functions in real-time.
Find examples of how other Smartsheet customers use this function or ask about your specific use case in the Smartsheet online Community.