Feature Overview Video
A date formula works out a date, or a number of days, from the dates already in a record. Use one to set the next review date, count how late something is, or show a date in words.
Here is the test: is the date you need always a fixed distance from another date, or a count between two? If yes, a formula can keep it right for you.
Example: a Controls table in a compliance program. Next Test Date adds one year to Last Tested. Days Overdue counts the days since Control Test Due passed. Test Quarter shows which quarter the test fell in.
The Advanced Editor of a Days Overdue formula on the Controls table, with DATEDIFF and TODAY in the editor.
Plan Availability | All plan types. |
Permissions | Administrators and Solution Managers create and change formulas in the solutions they manage. |
Platform | Web |
Related Reading |
Adding or subtracting time
DATEADD( Date, Count, Unit ) moves a date forward or back. The unit is minutes, hours, days, weeks, months or years. A negative count moves it back.
DATEADD([Last Tested], 1, "years")
For Next Test Date, a control last tested on 15 Jan 2026 gets 15 Jan 2027. Product's own example: DATEADD([Due], 10, "days") turns 1/1/2021 into 1/11/2021.
Counting time between two dates
DATEDIFF( Date1, Date2, Unit ) counts from the first date to the second, in minutes, hours, days, weeks, months or years. Months count as 30 days.
DATEDIFF([Control Test Due], TODAY(), "days")
For Days Overdue, a test due 10 days ago gives 10. A test due in the future gives a negative number.
Note: TODAY() is midnight of the current day. If the other date has a time, the count can be off by one day. Use a date with no time, or strip the time first (see "Avoiding off-by-one days" below).
Using today's date
TODAY() returns today's date.
NOW() returns today's date and the current time. It works during a trial or on a paid plan.
Days since a record was created:
DATEDIFF([First Created], TODAY(), "days")
Reading part of a date
These functions return one part of a date as a number.
DAY( Date ): the day of the month.
MONTH( Date ): the month, 1 to 12.
QUARTER( Date ): the quarter, 1 to 4.
YEAR( Date ): the year.
WEEKDAY( Date ): the day of the week, 1 to 7, with weeks starting on Monday unless you add "Sunday".
For Test Quarter: "Q" + TEXT(QUARTER([Last Tested])) shows Q1 for a test in January.
Turning text into a date
DATE( Text ) turns text into a date, so other date functions can use it.
DATE("1 Jan 2021")
Returns 1/1/2021.
Showing a date as text
Two functions turn a date into text in a format you choose.
DATE_FORMAT( Date, Format ) is for a date with no time, such as a Due Date.
DATETIME_FORMAT( Date, Format ) is for a date with a time, such as First Created.
The format can be "compact" (4/14/2021 at 2:00 PM), "standard" (Apr 14, 2021 at 2:00 PM) or "long" (Sun, Apr 14, 2021 at 2:00 PM). It can also be a pattern:
DATE_FORMAT([Due Date], "MM/DD/YYYY")
DATETIME_FORMAT([Created At], "MM/DD/YYYY hh:mm A")
Tip: The two functions take the same arguments. Pick the one that matches the field, with or without a time, so the result stays consistent.
Working days
WORKDAYS( Start Date, Days ) returns the date a number of working days after the start.
WORKDAYS_DIFF( Start Date, End Date ) counts the working days between two dates, both included.
Working days and holidays come from your SmartSuite profile.
Date ranges
A formula cannot return a date range. It can read one end of a Date Range field:
[Date Range].[Date Range End]
[Date Range].[Date Range Start]
For an Audit Fieldwork Window, [Audit Fieldwork Window].[Date Range End] gives the last day of fieldwork.
More date formulas
Avoiding off-by-one days. When the other date has a time, take its year, month and day parts to make a date with no time. Then run DATEDIFF on that date and TODAY().
The day of the week a date falls on
LEFT(DATETIME_FORMAT([Date], "long"), 3)
The name of the month
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")
The last day of the month. This version does not count leap years.
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])))
The last working day before a weekend date. If the date is a Sunday, go back 2 days. If it is a Saturday, go back 1 day.
DATEADD([Date], -NUMBER(CASE(LEFT(DATE_FORMAT([Date], "long"), 3), "Sun", "2", "Sat", "1")), "days")
A birthday or anniversary this year
DATE(CONCAT(MONTH([Date]),"/", DAY([Date]), "/", YEAR(TODAY())))
Minutes since midnight from a Time field. Use it to compare a Time field with a Date field, with DATEADD and DATEDIFF.
ROUND(NUMBER([Time]) * 60 + NUMBER(MID(TEXT([Time]), FIND(":", TEXT([Time])) + 1, 2)), 0)Showing 12-hour times in automations
In Grid View, a date with a time follows your locale, such as 12-hour time. When an automation sends the same field, for example in an email, the time shows in 24-hour format. A Formula field can format it as 12-hour time with AM and PM:
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"))
Use the formula field in the automation instead of the date field. Add your time zone as text at the end, such as " PST".
Frequently asked questions
How do I count the days since a record was created?
How do I count the days since a record was created?
DATEDIFF([First Created], TODAY(), "days").
How do I work out someone's age?
How do I work out someone's age?
DATEDIFF([Birthdate], TODAY(), "years") can be off before this year's birthday has passed. For an exact age, use ROUND(DATEDIFF([Birthdate], TODAY(), "days")/365.25-0.49, 0).
Can a formula return a date range?
Can a formula return a date range?
No. It can read the start or the end of a Date Range field.
Why is my day count off by one?
Why is my day count off by one?
The other date has a time, and TODAY() is midnight. Use a date with no time.
Can a formula date start an automation?
Can a formula date start an automation?
Yes. A formula date can drive a "record matches a condition" trigger, such as Next Review Date is a week from now.
Last verified 1 Nov 2026.