Formula syntax
Sign in

Values

  • Numbers: 1.1.

  • Strings: 'abc', "abc".

  • Boolean values: true, false.

  • Dates: date('2012-01-01').

  • Empty cell: null.

  • Value in another column in the same record: [Column name].

    Checkbox column contains boolean values. A reference to a nested table produces the number of records in that nested table.

    If the column name changes, don't worry - the formula will be updated automatically.

Operators

  • ^, *, /, +, - for numbers.

  • + for strings.

  • =, != for all value types, including null. Types of values on two sides, if other than null, should match.

  • >, >=, <, <= for number and date. Value types on two sides should match.

  • and, or for booleans.

  • Unary - and not.

Functions: text

  • number_to_string(<number>)

    Converts a number to a string.

  • string_to_number(<string>)

    Converts a string to a number.

  • contains(<string>, <string>)

    Returns a boolean value indicating whether the first string contains the second string (this function is case-sensitive).

  • starts_with(<string>, <string>)

    Returns a boolean value indicating whether the first string starts with the second string (this function is case-sensitive).

  • ends_with(<string>, <string>)

    Returns a boolean value indicating whether the first string ends with the second string (this function is case-sensitive).

  • to_lower_case(<string>)

    Converts a string to lower case.

  • to_upper_case(<string>)

    Converts a string to upper case.

  • slice(<string>, <number>), slice(<string>, <number>, <number>)

    Returns the part of the string between the starting index (first number) and ending index (second number, optional). slice('abc', 0) will return 'abc'. Negative indexes are counted from the end of the string: slice('abc', 1, -1) will return 'b'.

Functions: mathematical

  • abs(<number>)

    Absolute value.

  • ceil(<number>)

    Returns the value rounded upwards to the nearest integer.

  • exp(<number>)

    e in power of argument.

  • floor(<number>)

    Returns the value rounded downwards to the nearest integer.

  • log(<number>)

    Natural logarithm (base of e).

  • max(<number>, <number>, ...), max(<date>, <date>, ...)

    Returns the highest/latest of the arguments.

  • min(<number>, <number>, ...), min(<date>, <date>, ...)

    Returns the lowest/earliest of the arguments.

  • round(<number>)

    Returns the value rounded to the nearest integer.

Functions: date

  • date(<string>)

    Returns a date. The argument should be a string in ISO 8601 format, such as '2012-01-01' or '20120101'.

  • add_years(<date>, <number>)

    Adds the supplied number of years to a date. The number can be either positive or negative.

  • add_months(<date>, <number>)

    Adds the supplied number of months to a date. The number can be either positive or negative.

  • add_days(<date>, <number>)

    Adds the supplied number of days to a date. The number can be either positive or negative.

  • add_business_days(<date>, <number>)

    Adds the supplied integer number of days to a date, skipping over the weekends. The number can be either positive or negative. Holidays are not taken into account.

  • diff_days(<date>, <date>)

    Returns the number of days between two dates.

Functions: other

  • if(<boolean>, <any value (returned if true)>, <any value (returned if false)>)

    If the first argument is true, returns the second argument, otherwise returns the third argument.

  • map(<base value>, <choice 1>, <result 1>, <choice 2>, <result 2>, ..., <default result>)

    If <base value> equals <choice 1>, returns <result 1>, so on for other choices. If none of the choices match, returns <default result>. Example: map([My text column], 'a', 1, 'b', 2, null).

Case sensitivity and escaping

The syntax isn't case-sensitive.

The delimiters ] (for column names) and ', " (for strings) are escaped by doubling them like so: 'guv''nor' = guv'nor.