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.
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)