Dates play an important role in SmartSuite Apps, and formulas are equipped to help Solution Managers evaluate them in a variety of ways. You can create formulas that return dates - and those values can be used just like an actual date field's value - in filters, chart group-by's and more.
Note that formulas cannot return date ranges (at least not yet!) You can use a date range value in a formula if you convert it to a single date. You can do that like this:
[Date Range].[Date Range End] ---> returns the end date
[Date Range].[Date Range Start] ---> returns the start date
Date Manipulation
The following date manipulation functions are available:
DATEADD
DATEADD(Date, Count, Unit)
Adds a specified number of hours, days, weeks, months or years to a date value. Unit is one of the following:
minutes
hours
days
weeks
months
years
Example:
DATEADD([Due],10,"days") -> if [Due] is 1/1/2021, returns 1/11/2021 as date
DATEDIFF
DATEDIFF(date1, date2, unit)
Returns the number of date "units" between the first and second dates. Unit is one of the following:
minutes
hours
days
weeks
months
years
Example:
DATEDIFF([day1],[day2],"days") -> if [day1] is 1/1/2021, [day2] is 1/11/2021, returns the number 1
Date Formatting
The following date formatting functions are available:
DATE
DATE(string)
Converts a string to a date value, allowing it to be used as a date type in SmartSuite formulas.
Example:
DATE("1 Jan 2021") ---> returns 1/1/2021 as a date value
DATETIME_FORMAT
DATETIME_FORMAT(Date, Format)
Formats the date. Supported formats include:
compact ( 4/14/2021 )
standard ( Apr 14, 2021 )
long ( Sun, Apr 14, 2021 )
Example:
DATETIME_FORMAT([Date],"compact") ---> 4/14/2021 as a date
DAY
DAY(Date)
Returns the Date's day of the month as a number.
MONTH
MONTH(Date)
Returns the Date's month of the year as a number.
QUARTER
QUARTER(Date)
Returns the Date's quarter as a number.
YEAR
DATE(String)
Returns the Date's year as a number.
Related Articles