Functions produce an output called a return value (which could be a number, text, true/false value or date type, depending on the function). Most functions require parameters. Parameters are the inputs into the function, and are specified in parentheses after the function name and separated by commas. The order of parameters and data format of each parameter is important. When you insert a function from a menu, placeholder text for each parameter will be inserted. You must replace the placeholder text for each parameter to complete the function. Functions with '...' can accept any number of additional parameters.
For instructions on how to use functions in custom columns, read Create custom columns.
| Category | Return type | Function | Description |
| Arithmetic | Number | round(x) |
Rounds [x] to the nearest integer |
| Arithmetic | Number | floor(x) |
Returns the greatest integer less than or equal to [x] |
| Arithmetic | Number | ceiling(x) |
Returns the smallest integer greater than or equal to [x] |
| Logic | Varies | if(condition, if_true, if_false) |
Evaluates [condition] and returns [if_true] if the condition is true and [if_false] if the condition is false. The return value will be the same type as [if_true] and [if_false]. |
| Logic | True/False | or(x, y, ...) |
Returns True if one of the parameters is true |
| Logic | True/False | not(x) |
Returns True if [x] is false and returns False if [x] is true |
| Logic | True/False | and(x, y, ...) |
Returns True if all parameters are true |
| Text | Text | concat(text1, text2, ...) |
Combines two or more text strings |
| Text | True/False | is_empty(text) |
Returns True if [text] has no characters |
| Text | Text | trim(text) |
Removes whitespace around a text string |
| Text | True/False | starts_with(needle, haystack) |
Returns True if [haystack] text string begins with [needle] |
| Text | True/False | ends_with(needle, haystack) |
Returns True if [haystack] text string ends with [needle] |
| Text | Number | search(needle, haystack) |
Returns the starting position of the first instance of [needle] in [haystack] |
| Text | True/False | contains(needle, haystack) |
Returns True when [haystack] text string contains [needle] |
| Text | Text | right(text, length) |
Returns the last [length] characters of [text] |
| Text | Text | left(text, length) |
Returns the first [length] characters of [text] |
| Text | Text | substitute(haystack, needle, replacement) |
Substitutes all instances of [needle] in [haystack] with [replacement] |
| Text | Text | upper(text) |
Converts all characters in [text] to UPPERCASE |
| Text | Text | lower(text) |
Converts all characters in [text] to lowercase |
| Text | Number | length(text) |
Return the number of characters in [text] |
| Conversion | Text | to_text(value) |
Converts [value] to text |
| Conversion | Number | to_number(value) |
Converts [value] to a number |
| Date | Date | date(year, month, day) |
Returns a date with the specified year, month and day |
| Date | Number | day(date) |
Returns the day of the month from [date] |
| Date | Number | month(date) |
Returns the month of the year (1 to 12) from [date] |
| Date | Number | quarter(date) |
Returns the quarter of the year (1 to 4) from [date] |
| Date | Number | year(date) |
Returns the year of [date] as a number |
| Date | Number | weekday(date) |
Returns the day of the week (1 to 7) for [date] where Sunday is 1 |
| Date | Number | weeknum(date) |
Returns the current week number in a year (1 to 54) for [date] where weeks start on Sunday |
| Date | Number | daynum(date) |
Returns the day of the year (1 to 355 or 356) for [date] |
| Date | Number | days_between(date1, date2) |
Returns the number of days between [date1] and [date2] |
| Date | Date | offset_days(date, days) |
Add [days] days to [date], counting forwards/backwards days on a calendar |
| Date | Date | offset_months(date, months) |
Add [months] to the month component of [date]; if the resulting month doesn't have enough days for the day component, the last day of the new month is returned |
| Date | Date | offset_years(date, years) |
Adds [years] to the year component of [date] |
| Date | Date | today() |
Returns the current date in custom column owner's account time zone |
| Date | Date | yesterday() |
Returns the previous day's date in custom column owner's account time zone |
| Date | Date | week_start_sunday() |
Returns the date of the most recent Sunday in custom column owner's account time zone |
| Date | Date | week_start_monday() |
Returns the date of the most recent Monday in custom column owner's account time zone |
| Date | Date | month_start() |
Returns the first date in the current month in custom column owner's account time zone |
| Date | Date | year_start() |
Returns the first date in the current year (1 January) in custom column owner's account time zone |
| Date | Date | report_range_start() |
Returns the date at the beginning of the report’s current time range. |
| Date | Date | report_range_end() |
Returns the date at the end of the report’s current time range |
| Date | Number | report_days_count() |
Returns the number of days covered by the report based on its start and end date |
