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

Did this answer your question?