Use Formulas to Perform Calculations With Dates

Applies to

Smartsheet
Enterprise

Capabilities

Who can use this capability

The Owner,  Admins, and Editors can modify a formula in a sheet. 

You can place a formula in one cell (formatted with a Date column type) that adds or subtracts numbers from dates in other cells. Numbers are treated as days, unless date functions are used.

The following table contains examples of using dates in formulas. (The dates in the table below are in mm/dd/yy format.) 

  Date Formula Description Result
1 5/19/17 =Date1 - 5 Subtracts 5 days from the date 5/14/17
2 12/10/17 =Date2 + 5 Adds 5 days to the date 12/15/17
3 1/20/18 =DATE(YEAR(date12), MONTH(date12) + 1, DAY(date12)) Add a month to the date with use of the DATE, YEAR, MONTH, and DAY functions 2/20/18

 

More information on date functions can be found in the Functions List.

NOTES:

  • You can't place formulas in date columns being used for dependencies. See Enabling Dependencies & Using Predecessors for more on dependencies.
  • The month and day value used in the DATE Function are restricted. For more information, see Date Function.