Function
|
Description |
Examples |
CREATED_TIME()
|
Returns the date and time a given record was created. |
CREATED_TIME()
=> 2015-11-11T22:18:17
|
DATEADD([date], [#], 'units') |
Adds specified "count" units to a datetime. (See list of shared unit specifiers here. For this function we recommend using the full unit specifier for your desired unit.) |
DATEADD(Date, 10, 'days')
=> 10/9/2015 12:00am
|
DATESTR([date]) |
Formats a datetime into a string (YYYY-MM-DD). |
DATESTR({Date})
=> 2015-11-12
|
DATETIME_DIFF([date1], [date2], 'units') |
Returns the difference between datetimes in specified units. The difference between datetimes is determined by subtracting [date2] from [date1]. This means that if [date2] is later than [date1], the resulting value will be negative.
Default units are seconds. (See list of unit specifiers here.)
Note: The DATETIME_DIFF() formula will return whole integers for any unit specifier.
|
DATETIME_DIFF({Date}, TODAY(), 'days')
=> 15
DATETIME_DIFF({Date Bought}, {Date Sold}, 'minutes')
=> 30
|
DATETIME_FORMAT([date], '[specified output format]') |
Formats a datetime into a specified string. For an explanation of how to use this function with date fields, click here. For a list of supported format specifiers, please click here. |
DATETIME_FORMAT(TODAY(), 'DD-MM-YYYY')
=> 10-11-2015
|
DATETIME_PARSE(date, ['input format'], ['locale']) |
Interprets a text string as a structured date, with optional input format and locale parameters. The output format will always be formatted 'M/D/YYYY h:mm a'. |
DATETIME_PARSE("4 Mar 2017 23:00", 'D MMM YYYY HH:mm')
=> 3/4/2017 11:00pm
|
DAY([date]) |
Returns the day of the month of a datetime in the form of a number between 1-31. |
DAY({Completion date})
=> 24
|
HOUR([datetime]) |
Returns the hour of a datetime as a number between 0 (12:00am) and 23 (11:00pm). |
HOUR({Completion date})
=> 9
|
IS_AFTER([date1], [date2]) |
Determines if [date1] is later than [date2]. Returns 1 if yes, 0 if no. |
IS_AFTER({Deadline}, TODAY())
=> 0
|
IS_BEFORE([date1], [date2]) |
Determines if [date1] is earlier than [date2]. Returns 1 if yes, 0 if no. |
IS_BEFORE({Deadline}, TODAY())
=> 1
|
IS_SAME([date1], [date2], [unit]) |
Compares two dates up to a unit and determines whether they are identical. Returns 1 if yes, 0 if no. |
IS_SAME({Date 1}, {Date 2}, 'hour')
=> 0
|
LAST_MODIFIED_TIME([{field1},{field2}, ...]) |
Returns the date and time of the most recent modification made by a user in a non-computed field in the table. If you only care about changes made to specific fields, you can include one or more field names, and the function will just return the date and time of the most recent change made to any of the specified fields. |
LAST_MODIFIED_TIME()
=> 5/9/2019 1:27 a.m.
LAST_MODIFIED_TIME({Due Date})
=> 3/16/2019 6:45 p.m.
|
MINUTE([datetime]) |
Returns the minute of a datetime as an integer between 0 and 59. |
MINUTE(NOW())
=> 31
|
MONTH([date]) |
Returns the month of a datetime as a number between 1 (January) and 12 (December). |
MONTH({Completion date})
=> 10
|
NOW() |
While similar to the TODAY() function, NOW() returns the current date AND time.
This function updates when the formula is recalculated, when a base is loaded, or otherwise roughly every 15 minutes when a base is open. If the base is closed, it will update approximately every hour only when the base has time-dependent automation triggers or actions, or sync dependencies.
|
The following examples demonstrate the return if you were to enter NOW() and TODAY() at the same time, 08/06/2020 8:03am formatted as GMT on a 12 hour clock:
You can toggle time formatting (GMT/Local, 12/24 hour) on the formatting tab
NOW()
=> 08/06/2020 8:03am
TODAY()
=> 08/06/2020 12:00am
|
SECOND([datetime]) |
Returns the second of a datetime as an integer between 0 and 59. |
SECOND(CREATED_TIME())
=> 53
|
SET_LOCALE([date], [locale_modifier]) |
Sets a specific locale for a datetime. Must be used in conjunction with DATETIME_FORMAT. A list of supported locale modifiers can be found here. |
DATETIME_FORMAT(SET_LOCALE(NOW(), 'ru'), 'lll')
=> 9 июня 2016 г., 23:49
|
SET_TIMEZONE([date], [tz_identifier]) |
Sets a specific timezone for a datetime. Must be used in conjunction with DATETIME_FORMAT. A list of supported timezone identifiers can be found here. |
DATETIME_FORMAT(SET_TIMEZONE(NOW(), 'Australia/Sydney'), 'M/D/YYYY h:mm')
=> 11/12/2015 7:16pm
|
TIMESTR([date/timestamp]) |
Formats a datetime into a time-only string (HH:mm:ss). |
TIMESTR(NOW())
=> 04:52:12
|
TONOW([date]), FROMNOW([date]) |
Calculates the number of days between the current date and another date. |
TONOW({Date})
=> 25 days
|
TODAY() |
While similar to the NOW() function: TODAY() returns the current date (not the current time, if formatted, time will return 12:00am).
This function updates when the formula is recalculated, when a base is loaded, or otherwise roughly every 15 minutes when a base is open. If the base is closed, it will update approximately every hour only when the base has time-dependent automation triggers or actions, or sync dependencies.
|
The following examples demonstrate the return if you were to enter NOW() and TODAY() at the same time, 08/06/2020 8:03am formatted as GMT on a 12 hour clock:
You can toggle time formatting (GMT/Local, 12/24 hour) on the formatting tab
NOW()
=> 08/06/2020 8:03am
TODAY()
=> 08/06/2020 12:00am
|
WEEKDAY(date, [startDayOfWeek]) |
Returns the day of the week as an integer between 0 and 6, inclusive. You may optionally provide a second argument (either "Sunday" or "Monday") to start weeks on that day. If omitted, weeks start on Sunday by default. Example: WEEKDAY(TODAY(), "Monday") |
WEEKDAY({Date})
=> 4
|
WEEKNUM(date, [startDayOfWeek]) |
Returns the week number in a year. You may optionally provide a second argument (either "Sunday" or "Monday") to start weeks on that day. If omitted, weeks start on Sunday by default. Example:
WEEKNUM(TODAY(), "Monday")
|
WEEKNUM({Date})
=> 46
|
WORKDAY(startDate, numDays, [holidays]) |
Returns a date that is numDays working days after startDate. Working days exclude weekends and an optional list of holidays, formatted as a comma-separated string of ISO-formatted dates. |
WORKDAY({Launch date}, 100, '2017-09-04, 2017-10-09, 2017-11-10')
=> 6/20/2017
|
WORKDAY_DIFF(startDate, endDate, [holidays]) |
Counts the number of working days between startDate and endDate. Working days exclude weekends and an optional list of holidays, formatted as a comma-separated string of ISO-formatted dates. |
WORKDAY_DIFF({Assignment date}, {Due Date}, '2017-09-04, 2017-10-09, 2017-11-10')
=> 8
|
YEAR([date]) |
Returns the four-digit year of a datetime. |
YEAR({Completion date})
=> 2015
|