Skip to main content
Date Based Formulas

Compare or adjust dates in your SmartSuite formulas

Peter Novosel avatar
Written by Peter Novosel
Updated yesterday

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?

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?

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?


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?

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?

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?

DATEDIFF([First Created], TODAY(), "days")

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)

Did this answer your question?