Dates play an important role in SmartSuite Tables, 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 10
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 and time. Supported formats include:
compact ( 4/14/2021 at 2:00PM )
standard ( Apr 14, 2021 at 2:00PM)
long ( Sun, Apr 14, 2021 at 2:00PM)
Example:
DATETIME_FORMAT([Date],"compact") ---> 4/14/2021 at 2:00PM as a date
DATE_FORMAT
DATE_FORMAT(Date, Format)
Formats the date (time ignored). 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
YEAR(Date)
Returns the Date's year as a number.
FAQ
How can I use a formula to calculate someone's birthday for this year?
How can I use a formula to calculate someone's birthday for this year?
This can be done by combining a few different functions to create a new date for the current year. The formula would look like this:
DATE(CONCAT(MONTH([Date]),"/", DAY([Date]), "/", YEAR(TODAY())))
You can then use this new date value in an automation of
When a record matches a condition Where [Next Birthday] is A week from now.
What if I want the name of the month instead of the number?
What if I want the name of the month instead of the number?
This can be done with our CASE() function.
CASE( MONTH([Date]),
1, "January",
2, "February",
3, "March",
4, "April",
5, "May",
6, "June",
7, "July",
8, "August",
9, "September",
10, "October",
11, "November",
12, "December")
How can I return the last day of the month based on a date field?
How can I return the last day of the month based on a date field?
Since months often have the same number of days (excluding leap years), this can be done with a case function to create a new date.
DATE(CONCAT(
MONTH([Date]), "-",
CASE( MONTH([Date]), 1, 31, 2, 28, 3, 31, 4, 30, 5, 31, 6, 30, 7, 31, 8, 31, 9, 30, 10, 31, 11, 30, 12, 31), "-",
YEAR([Date])))
How can I get the day of the week a date falls on?
How can I get the day of the week a date falls on?
This can be done using our DATETIME_FORMAT() function, and we're able to pick just the name of the day by combining it with our LEFT() function.
LEFT(DATETIME_FORMAT([Date], "long"), 3)
How do I return the latest workday before a given date?
How do I return the latest workday before a given date?
It's a bit more complex, but let's take the last day of the month example and build upon it. So if we assume we need to action on the last workday of the month instead of the last day of the month, the formula looks something like this:
DATE_FORMAT(
DATEADD(
DATE(CONCAT(
MONTH([Date]), "-",
CASE( MONTH([Date]), 1, 31, 2, 28, 3, 31, 4, 30, 5, 31, 6, 30, 7, 31, 8, 31, 9, 30, 10, 31, 11, 30, 12, 31), "-",
YEAR([Date]))),
-NUMBER(CASE(LEFT(DATE_FORMAT(DATE(CONCAT(
MONTH([Date]), "-",
CASE( MONTH([Date]), 1, 31, 2, 28, 3, 31, 4, 30, 5, 31, 6, 30, 7, 31, 8, 31, 9, 30, 10, 31, 11, 30, 12, 31), "-",
YEAR([Date]))), "long"), 3),
"Sun", "2",
"Sat", "1")), "days"), "long")
This may look confusing, but really the formula is just saying "If the last day of the month is on Sunday, subtract 2 days. If the last day of the month is on Saturday, subtract 1 day. If it falls on any other day, just return the day."
Here is a simplified version based on just a date:
DATEADD([Date],
-NUMBER(CASE(LEFT(DATE_FORMAT([Date], "long"), 3),
"Sun", "2",
"Sat", "1")), "days")
How do I return the number of days that have passed since a record was created?
How do I return the number of days that have passed since a record was created?
DATEDIFF([First Created], TODAY(), "days")
How do I calculate someone's age accurately?
How do I calculate someone's age accurately?
While you can just do DATEDIFF([Birthday], TODAY(), "years"), that's not always the most accurate since their specific birthday may not have passed yet. To achieve this, you'd instead want to use a method where you're rounding down.
ROUND(DATEDIFF([Birthdate], TODAY(), "days")/365.25-0.49, 0)
