Skip to main content

Search

Let us guide you!

Glossary: Operators · Functions

Updated on

Find all the operators and functions available in TimeTonic formulas, sorted by category. Each name links to its detailed entry.

 

Referencing a field in a formula: write $field_name, a single dollar sign followed by the exact field name (e.g. $Status, $Creation Date). There is no brace notation ({field}), no double dollar sign ($$field$$), and no dot notation (Table.Column): all three are invalid. Tip: typing $ directly in the formula editor shows the list of available fields (see Formula column).

Operators

Name Syntax Example Description
EQUAL (==) == 5 == 5 → true Checks if two values are equal.
NOT EQUAL TO (!=) != 5 != 3 → true Checks if two values are different.
LESS THAN (<) < 3 < 5 → true Checks if one value is less than another.
MORE THAN (>) > 5 > 3 → true Checks if one value is greater than another.
LESS THAN OR EQUAL TO (<=) <= 3 <= 5 → true Checks if one value is less than or equal to another.
MORE THAN OR EQUAL TO (>=) >= 5 >= 5 → true Checks if one value is greater than or equal to another.
AND (and) and age > 18 and city == "Paris" Logical AND operator: true if all conditions are true.
PLUS (+) + 5 + 3 → 8 Adds two values.
MINUS (-) - 5 - 3 → 2 Subtracts one value from another.
MULTIPLY (*) * 5 * 3 → 15 Multiplies two values.
DIVIDE (/) / 15 / 3 → 5 Divides one value by another.
MODULO (%) % 10 % 3 → 1 Returns the remainder of a division.
POWER (**) ** 2 ** 3 → 8 Raises a value to the power of another.
OR (or) or age > 18 or city == "Paris" Logical OR operator: true if at least one condition is true.
NOT (not) not not age > 18 Negates a condition.
TERNARY CONDITION ( ? : ) condition ? success : failure X > 5 ? "Yes" : "No" Returns one of two values depending on the condition.

Date Functions

Name Syntax Example Description
MIN_DATE_ELEMENTS MIN_DATE_ELEMENTS(date1, date2, ...) → 2023-01-01 Returns the earliest date.
MAX_DATE_ELEMENTS MAX_DATE_ELEMENTS(date1, date2, ...) → 2023-06-01 Returns the most recent date.
DATE_ADD DATE_ADD(date, number) DATE_ADD(TODAY(), 7) Adds X days to a date.
DATE_ADDNB DATE_ADDNB(date, number) → +10 working days Adds X working days to a date.
DATEHOUR_ADD DATEHOUR_ADD(date, number) DATEHOUR_ADD(date, 2) → +2 h Adds X hours to a date/time.
DATEMINUTE_ADD DATEMINUTE_ADD(date, number) DATEMINUTE_ADD(date, 30) → +30 min Adds X minutes to a date/time.
TIME TIME(date and time) TIME('12:30') Displays the time.
TIME_DELTA TIME_DELTA(dh1, dh2) TIME_DELTA('14:30','12:30') → 120 Subtracts two date/times, result in minutes.
HOUR_DELTA HOUR_DELTA(dh1, dh2) → 2 hours Subtracts two date/times, result in hours.
TODAY TODAY() TODAY() Returns today's date.
DAY DAY(date) → 17 Day number of the month.
DAY_IN_WEEK DAY_IN_WEEK(date) → 2 (Tuesday) Day number in the week.
DAY_DELTA DAY_DELTA(date1, date2) → 7 days Subtracts two dates, result in days.
DAY_OPEN_DELTA DAY_OPEN_DELTA(date1, date2) DAY_OPEN_DELTA('2024-09-17','2024-09-10') → 5 working days Subtracts two dates, result in working days.
DAY_DELTA_TODAY DAY_DELTA_TODAY(date) → 7 Number of days since a date.
WEEK WEEK(date) → 38 Week number.
WEEK_DELTA WEEK_DELTA(date1, date2) → 7 weeks Subtracts two dates, result in weeks.
WEEK_ADD WEEK_ADD(date, number) WEEK_ADD('2024-09-17', 2) → 2024-10-01 Adds X weeks to a date.
WEEK_DELTA_TODAY WEEK_DELTA_TODAY(date) → 7 weeks Number of weeks since a date.
MONTH MONTH(date) → 9 Month number.
MONTH_DELTA MONTH_DELTA(date1, date2) → 12 months Subtracts two dates, result in months.
MONTH_ADD MONTH_ADD(date, number) → +3 months Adds X months to a date.
YEAR YEAR(date) → 2024 Returns the year.
YEAR_WEEK YEAR_WEEK(date) → 2024-38 Year and week number.
YEAR_MONTH YEAR_MONTH(date) → 2024-09 Year and month.
YEAR_QUARTER YEAR_QUARTER(date) → 2024-Q3 Year and quarter.
YEAR_DELTA_TODAY YEAR_DELTA_TODAY(date) → 1 year Number of years since a date.
YEAR_ADD YEAR_ADD(date, number) → +1 year Adds X years to a date.
QUARTER QUARTER(date) → 3 Quarter number.

Text Functions

Name Syntax Example Description
UPPER UPPER(text) → "LOREM IPSUM" Converts text to uppercase.
LOWER LOWER(text) → "hello world" Converts text to lowercase.
REGEXEXTRACT REGEXEXTRACT(text, regex) → "24158" Extracts the first match of a regular expression.
REGEXREPLACE REGEXREPLACE(text, regex, replacement) → "Hypermarché" Replaces using a regular expression.
REGEXMATCH REGEXMATCH(text, regex) → true / false Tests a regular expression match.
TRIM TRIM(text) → "Hello world" Removes extra spaces.
LENGTH LENGTH(text) → 7 Returns the length of the text.
SEARCH SEARCH(find, in, start) → 1 Position of text (case-insensitive).
FIND FIND(find, in, start) → 2 Position of text (case-sensitive).
LEFT LEFT(text, n) → "ABC" Returns the first n characters.
RIGHT RIGHT(text, n) → "1234" Returns the last n characters.
REPLACE REPLACE(text, start, n, new) → "DemoTonic" Replaces characters by position.
SUBSTITUTE SUBSTITUTE(text, old, new, n°) → "TM-2026-01" Replaces occurrences of text.
TEXT_DAY TEXT_DAY(date) → "Tuesday" Returns the day of the week as text.
EXTRACT_ELEMENT EXTRACT_ELEMENT(value, number, text) → "banana" Extracts an element from a list.
APPLY_MASK APPLY_MASK(text, mask) APPLY_MASK('1234567890', 'XXXXXX@@@@') → 7890 Masks (X) or shows (@) characters.
HTML_TO_TEXT HTML_TO_TEXT(text) → "Hello world!" Converts HTML to readable text.
SUBSTR SUBSTR(text, start, length) SUBSTR('Hello world', 0, 5) → "Hello" Extracts a substring.
FORMAT_NUMBER FORMAT_NUMBER(value, format) FORMAT_NUMBER(12345.678, "0,0.00") Formats a number (result as text).
FORMAT_DATE FORMAT_DATE(value, format) FORMAT_DATE(TODAY(), "DD/MM/YYYY") Formats a date (result as text).
READ_DATE READ_DATE(text, format, "classic|extended") READ_DATE("25-12-2024", "DD-MM-YYYY", "classic") Converts text to date.

Numeric Functions

Name Syntax Example Description
INT INT(value) INT(3.7) → 3 ; INT(-3.7) → -4 Integer part (rounds down).
AVERAGE AVERAGE(v1, v2, ...) → 15 Average of values.
MEDIAN MEDIAN(v1, v2, ...) → 2.5 Median of values.
STDEV STDEV(v1, v2, ...) → 2.75 Standard deviation.
CEILING CEILING(value) CEILING(4.3) → 5 Rounds up to the nearest integer.
FLOOR FLOOR(value) FLOOR(4.3) → 4 ; FLOOR(-2.1) → -3 Rounds down to the nearest integer.
ROUND ROUND(value, precision) ROUND(3.14159, 2) → 3.14 Rounds to n decimal places.
SUM SUM(v1, v2, ...) → 60 Adds columns.
SUBSTRACT SUBSTRACT(colNum1, colNum2, ...) SUBSTRACT(100, 45) → 55 Subtracts columns.
MIN MIN(colNum1, ...) → 5 Smallest numeric value.
MAX MAX(colNum1, ...) → 25 Largest numeric value.
NB_ELEMENTS NB_ELEMENTS(v1, v2, ...) → 3 Number of elements.
SUM_ELEMENTS SUM_ELEMENTS(v1, v2, ...) → 60 Sum (multiple values).
MIN_NUM_ELEMENTS MIN_NUM_ELEMENTS(v1, v2, ...) → 5 Smallest value (multi-values).
MAX_NUM_ELEMENTS MAX_NUM_ELEMENTS(v1, v2, ...) → 20 Largest value (multi-values).

Other Functions

Name Syntax Example Description
ROWID ROWID() → "jklm1234" Unique technical identifier of the row.
SWITCH SWITCH(source, value_1, result_1, ...) SWITCH(Status, "New", "Urgent", ...) Replaces nested IF() statements.
ISO_WEEKNUM ISO_WEEKNUM(date) ISO_WEEKNUM(2026-01-01) → 1 ISO week number (Monday, 1st week = first Thursday).
WEEKNUM WEEKNUM(date) WEEKNUM(2026-01-10) → 2 Week number (Sunday, 1st week = January 1st).
COUNTIF COUNTIF(value, criteria, separator) COUNTIF("101 205 301 50", ">100", " ") → 3 Counts elements according to a criterion.
IF IF(condition, true_value, false_value) IF(age > 18, "adult", "minor") Returns a value based on a condition.
CONCAT CONCAT(param, ...) → "Martin Simon" Concatenates multiple values.
CONCAT_URL CONCAT_URL(param1, param2, ...) → "https://example.com/user/123" Builds a URL.
URL_TRIGGER URL_TRIGGER(text) URL_TRIGGER('https://example.com') Triggers a URL.
GEOLOC GEOLOC(text) GEOLOC('12 Rue de Rivoli, Paris') Returns the coordinates of an address.
COORDINATES COORDINATES(column1, column2) COORDINATES(123, 456) Creates an X/Y coordinate pair.
COORDX COORDX(param1) COORDX(COORDINATES(123, 456)) → 123 Extracts the X part of a coordinate pair.
COORDY COORDY(param1) COORDY(COORDINATES(123, 456)) → 456 Extracts the Y part of a coordinate pair.
DISTANCE DISTANCE(coord_a, coord_b) → distance in meters Straight-line distance between two points.
IS_EMPTY IS_EMPTY(field) IS_EMPTY(Name) → 1 if empty Tests if a field is empty.
EMPTY EMPTY() → empty value Returns an empty value.

Advanced Automation

These operators and functions are particularly useful for building automation scenarios: trigger conditions, dynamic values, and personalized messages.

Function Role in Automation Example
IF Simple condition in an action or calculated field IF(Amount > 1000, "To validate", "OK")
SWITCH Replace multiple nested IFs based on a status SWITCH(Status, "New", "Urgent", …)
AND / OR / NOT Combine multiple trigger conditions age > 18 and city == "Paris"
IS_EMPTY Act only if a field is filled (or empty) IS_EMPTY(Email)
TODAY Timestamp an action with a dynamic date TODAY()
DATE_ADD Calculate a due date (reminder at D+X) DATE_ADD(TODAY(), 7)
DAY_DELTA Measure a delay between two dates (follow-up) DAY_DELTA(Due date, TODAY())
CONCAT Build a dynamic message or label CONCAT(First name, " ", Last name)
FORMAT_DATE Format a date in an email FORMAT_DATE(TODAY(), 'DD/MM/YYYY')
FORMAT_NUMBER Format an amount (separators, decimals) FORMAT_NUMBER(Total, '0,0.00')
REGEXMATCH Validate a format (email, code) in a condition REGEXMATCH(Email, "@")
CONCAT_URL Build a permalink or action URL CONCAT_URL(base, ROWID())