Date formulas allow you to compare, adjust, and format dates dynamically. Use these functions to automate calculations, streamline workflows, and gain insights from date-related data.
Plan Availability | All plan types |
Permissions | Solution Managers: Can create, modify, and manage date formulas |
Related Reading |
Feature Overview Video
Key Capabilities of Date-Based Formulas
Date Manipulation: Add or subtract time to/from dates.
Date Comparison: Calculate differences between dates.
Date Formatting: Convert and format date values for easy readability.
Dynamic Outputs: Create formulas for age, workdays, birthdays, 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 Functions
1. Add or Subtract Time
Function: DATEADD(Date, Count, Unit)
Adds/subtracts time to/from a date.
Units:
minutes,hours,days,weeks,months,years.
Example:
DATEADD([Due], 10, "days")
If [Due] is 1/1/2021, the result is 1/11/2021.
2. Calculate Date Differences
Function: DATEDIFF(Date1, Date2, Unit)
Calculates the difference between two dates.
Units:
minutes,hours,days,weeks,months,years.
Example:
DATEDIFF([Start Date], [End Date], "days")
If [Start Date] is 1/1/2021 and [End Date] is 1/11/2021, the result is 10.
Date Formatting Functions
1. Convert a String to a Date
Function: DATE(string)
Converts text into a date value.
Example:
DATE("1 Jan 2021")Returns: 1/1/2021.
2. Format Dates and Times
Function: DATETIME_FORMAT(Date, Format)
Formats a date and time into a readable string.
Formats: compact (4/14/2021 at 2:00 PM), standard (Apr 14, 2021 at 2:00 PM), long (Sun, Apr 14, 2021 at 2:00 PM).
Example:
DATETIME_FORMAT([Date], "compact")
Returns: 4/14/2021 at 2:00 PM.
3. Extract Specific Date Components
Day:
DAY(Date)– Returns the day of the month as a number.Month:
MONTH(Date)– Returns the month as a number.Quarter:
QUARTER(Date)– Returns the quarter as a number.Year:
YEAR(Date)– Returns the year as a number.
Differentiating DATETIME_FORMAT() and DATE_FORMAT()
Both functions return a formatted date, but they serve slightly different purposes depending on whether your source field includes time values or not.
DATETIME_FORMAT()
Purpose: Formats a date with time into the specified display format.
Example:
Formula:
DATETIME_FORMAT([Created At], "MM/DD/YYYY hh:mm A")
If [Created At] = 2025-08-25 14:30:00
Result:
08/25/2025 02:30 PM
DATE_FORMAT()
Purpose: Formats a date only field (no time component) into the specified display format.
Example:
Formula:
DATE_FORMAT([Due Date], "MM/DD/YYYY")
If [Due Date] = 2025-08-25
Result:
08/25/2025
Key Difference
Use
DATETIME_FORMAT()when working with fields that include both date and time (e.g., Created At, Last Updated, Timestamp fields).Use
DATE_FORMAT()when working with fields that contain date only values (e.g., Due Date, Project Start Date).
Tip: While the arguments are identical, using the correct function ensures your formula behaves consistently with the type of source field.
Format Date + Time in 12-Hour for Automations
When using a Date field with Time, you may notice that:
In the grid view, your workspace locale settings are respected (e.g., 12-hour time format).
However, when the same Date+Time field is passed through automations (such as sending via SmartSuite email actions or Gmail integration), the time is displayed in 24-hour format.
This happens even if the workspace and locale are set to 12-hour time, and the automation is using the correct time zone.
Workaround: Use a Formula Field
To ensure your date values are consistently displayed in 12-hour format (with AM/PM) when sent via automation, you can create a Formula Field that formats both start and end dates.
Here is an example formula you can copy and adapt:
CONCAT( LEFT(DATE_FORMAT([Start Date], "long"), 3), ", ", DATE_FORMAT([Start Date], "standard"), " ",
CASE( HOUR([Start Date]), 0, "12", 1, "1", 2, "2", 3, "3", 4, "4", 5, "5", 6, "6", 7, "7", 8, "8", 9, "9", 10, "10", 11, "11", 12, "12", 13, "1", 14, "2", 15, "3", 16, "4", 17, "5", 18, "6", 19, "7", 20, "8", 21, "9", 22, "10", 23, "11", "?" ), ":",
IF(MINUTE([Start Date]) < 10, CONCAT("0", TEXT(MINUTE([Start Date]))), TEXT(MINUTE([Start Date]))), IF(HOUR([Start Date]) < 12, "AM", "PM"), " - ",
LEFT(DATE_FORMAT([End Date], "long"), 3), ", ", DATE_FORMAT([End Date], "standard"), " at ",
CASE( HOUR([End Date]), 0, "12", 1, "1", 2, "2", 3, "3", 4, "4", 5, "5", 6, "6", 7, "7", 8, "8", 9, "9", 10, "10", 11, "11", 12, "12", 13, "1", 14, "2", 15, "3", 16, "4", 17, "5", 18, "6", 19, "7", 20, "8", 21, "9", 22, "10", 23, "11", "?" ), ":",
IF(MINUTE([End Date]) < 10, CONCAT("0", TEXT(MINUTE([End Date]))), TEXT(MINUTE([End Date]))), IF(HOUR([End Date]) < 12, "AM", "PM"), " Input Your Locale here ie PST (Pacific Time)" )
Example Output
Tue, Jul 29, 2025 3:05PM - Tue, Jul 29, 2025 at 5:00PM PST
This formula:
Formats Start Date and End Date with day, date, and standard 12-hour time.
Ensures leading zeros on minutes (e.g.,
3:05PMinstead of3:5PM).Adds AM/PM markers.
Lets you append your time zone abbreviation manually (e.g., PST, EST).
FAQs
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)