Help center

    Start for free    Sign in search

      Get Started Add Content Billing FAQs

      Formulas

      Thy Nguyen

      Updated on February 23rd, 2024

      Concepts

      A formula is an expression that is combined between column/numbers/string values and operations/functions.

      Value can be a number or a string, the string value must be surrounded by double quotes “Hello World“ or column value.

      Operation is a math operation such as +, -, *, /, %, ^

      Function is a custom function, example: Sum, Len, Concat, If, …

      For example, if you wanted a formula that calculated the total revenue based on the revenue of the January column and February column, the formula would look like:e=

      January + February

      or

      Sum(January, February)

      Available functions (alphabetical)  

      Press Ctrl+F to find a function by typing the first few letters or a descriptive word.

      Function Description Examples

      And(logical1, [logical2, ...])

      Return true if all the arguments are true, returns false otherwise.

      And(Logical 1, Logical 2)

      => True (if both logical values is truth)

      Average(value1, [value2, ...])

      Return the average of the numbers.

      Average(1, 2, 3)

      => 2

      Ceil(value)

      Round a number up to the nearest integer.

      Ceil(-7.1)

      => -7

      Ceil(5.6)

      => 6

      Concat(text1, [text2, ...])

      Join several text items into a single text value.

      Concat("Hello", "-", "world")

      => Hello-world

      Contains(MultipleSelectionColumn, "Item 1")

      Check for the existence of a value in a list.

      Contains(["Hello", "World"], "Hello") 

      => true

      Count(value1, [value2, ....])

      Count the number of items without null items.

      Count(1,2,3,"","four")

      => 4

      Color (value) 

      Return color based on the color names at HTML Color Names

      Note that this function is meant to use with ResultWithColor function

      If(len(column1) > 10, ResultWithColor(Color("DarkTurquoise"), "Too long"), "Ok")
      DateTimeAdd(datetime column, valueToAdd, unit) Add specified "count" units to a datetime. Possible unit value are: years, months, weeks, days, hours, minutes, seconds

      DateTimeAdd("2021-04-15T09:51:00Z", 1, "days")

      => 2021-04-16T09:51:00Z

      DateTimeDiff(datetime1, datetime2, unit)

      Return the difference between datetimes in specified units. The difference between datetimes is determined by subtracting [datetime1] from [datetime2]. This means that if [datetime1] is later than [datetime2], the resulting value will be negative. 

      Possible unit value are: years, months, weeks, days, hours, minutes, seconds

      DateTimeDiff("2023-04-11T04:11:18Z", “2023-04-11T04:11:17Z”, “days”)

      => -16

      DateTimeFormat(value, format, timezone)

      Format a datetime into a specified string. 

      List of tz database time zones

      DateTimeFormat("2021-07-08T00:00:00Z[UTC]", "yyyy-MM-dd", “Asia/Tokyo”

      => 2021-07-08 08:00

      DateTimeTruncate(value, unit)

      Get a datetime value based on the unit.

      DateTimeTruncate("2021-07-08T09:10:11Z[UTC]", "DayOfMonth")

      => 8

      Day(value)

      Returns the day of the month of a datetime in the form of a number between 1-31.

      Day("2021-07-08T09:10:11Z[UTC]")

      => 8

      EditDistance (value1, value2)

      Find the Levenshtein distance between two strings 

      Levenshtein distance: the minimum number of single-character edits (insertions, deletions or substitutions) required to change one word into the other.

      editDistance("Hallo", "Hello") → 1

       

      EncodeAsBase64(value)

      Encode string as base64.

      EncodeAsBase64("Hello world")

      => SGVsbG8gd29ybGQ=

      EndsWith(value, suffix)

      Checks if a string ends with a specified characters

      EndsWith("Hello world", "world")

      => True

      ExtractDigits(value)

      Extract digits from string ExtractDigits("V12") → 12
      ExtractJsonPath(value, jsonPath) Extract content from JSON string

      value = {“data“: “Hello World“}

      Extract JsonPath(value, “$.data”)

      => Hello world

      False()

      Return false value

      False ()

      => False

      Floor(value)

      Rounds a number down to the nearest integer.

      Floor(-7.1)

      => -8

      Floor(5.6)

      => 5

      Format(value, arguments)

      Returns formatted string by given arguments.

      Format("Hello $s", "world")

      => Hello world

      FuzzyMatching(value1, value2)

       

      Return match scores that can range from 0 through 100% based on how close the value1 and value2 match FuzzyMatching("Hello", "Hallo") → 88

      Hour([datetime])

      Returns the hour of a datetime as a number between 0 (12:00am) and 23 (11:00pm).

      Hour("2021-07-08T09:10:11Z[UTC]")

      => 9

      If(logical, value1, value2)

      Returns value1 if the logical argument is true, otherwise it returns value2.

      If(Amount > 50, "True", "False")

      => True (if the logical value over 50)

      IfBlank(value, fallbackValue)

      Returns fallbackValue if the value is blank, otherwise return the value.

      IfBlank("", "Blank")

      => Blank

      IfBlank("Not blank", "Blank")

      => Not blank

      IsAfter(value1, value2)

      Checks if a datetime is after another datetime.

      IsAfter(Now(), "2021-07-08T09:10:11Z[UTC]")

      => False

      IsBefore(value1, value2)

      Checks if a datetime is before another datetime.

      IsBefore(Now(), "2021-07-08T09:10:11Z[UTC]")

      => True

      IsBlank(value)

      Checks if a string value is blank.

      IsBlank("Not blank")

      => False

      IsNotBlank (value)

      Check if a string value is not blank

      IsNotBlank("Not blank")

      => True

      IsChecked (value) 

      Check if the value on the boolean column is checked

      IsChecked(column1)

      => True (given column1’s value is checked)

      IsUnchecked (value)

      Check if the value on the boolean column is unchecked

      IsUnchecked(column1)

      => False (given column1’s value is checked)

      IsEqual(value1, value2)

      Check if two values are equal.

      IsEqual("Hello", "Hello")

      => True

      IsNotEqual(value1, value2)

      Check if two values are not equal

      IsNotEqual("Hello", "Hello")

      => False

      IsEven(value)

      Check if a value is even.

      IsEven(6)

      => True

      IsOdd(value)

      Check if a value is odd.

      IsOdd(9)

      => True

      IsStatus(column, status)

      Check if the dependency status of a column field equals to a specified value (Up-to-date, Out-of-date, Unset) IsStatus(English, “Out-of-date”) → true (supposed dependency status is Out-of-date)

      IsStatusOutOfDate

       

      Check if the dependency status of a column is "Out-of-date" IsStatusOutOfDate(English) → true (supposed dependency status is Out-of-date)

      IsStatusUnset

      Check if the dependency status of a column is "Unset" IsStatusUnset(English) → false (supposed dependency status is Out-of-date)

      IsStatusUpToDate

      Check if the dependency status of a column is "Up-to-date" IsStatusUpToDate(English) → false

      Join(delimiter, value1, [value2, ...])

      Combine multiple string values with a delimiter.

      Join("-", "Hello", "world", "!")

      => Hello-world-!

      LastModifiedBy(column)

      Get email of the member who last modified a record or a specified column fields.

      LastModifiedBy() 

      => a@gridly.com

       

      LastModifiedBy(column1) 

      => a@gridly.com (given column1’s modified person is “th@gridly.com“)

      LastModifiedTime(column)

       

      Get last modified time of record or a specified column field

      LastModifiedTime() → 2021-07-08T09:10:11Z[UTC]

      LastModifiedTime(column1) => 2021-07-08T09:10:11Z[UTC] (given column1’s data modified time is “2021-07-08T09:10:11Z[UTC]“)

      Left(value, howMany)

      Extract howMany characters from the beginning of the string.

      Left("Hello world", 5)

      => Hello

      Len(value)

      Returns the length of a string.

      Len("Hello")

      => 5

      List(value1, [value2, ...])

      Makes a list of values.

      List("Hello", "world")

      => ["Hello", "world"]

      Log(number, [base])

      Computes the logarithm of the value in provided base. The base defaults to 10 if not specified.

      Log(1024, 2)

      => 10

      Longest(list)

      Gets longest text from the list

      Longest(split(column1,"\n")

      => World! (given column1’s value is “Hello\nWorld!“)

      Lower(value)

      Makes a string lowercase.

      Lower("Hello world")

      => hello world

      Max(value1, [value2, ...])

      Return the largest of the given numbers.

      Max(10, 100)

      => 100

      Mid(string, whereToStart, count)

      Extract a substring of count characters starting at whereToStart.

      Mid("Hello world", 0, 5)

      => Hello

      Min(value1, [value2, ...])

      Return the smallest of the given numbers.

      Min(10, 100)

      => 10

      Minute([datetime])

      Return the minute of a datetime as an integer between 0 and 59.

      Minute("2021-07-08T09:10:11Z[UTC]")

      => 10

      Month([datetime])

      Return the month of a datetime as a number between 1 (January) and 12 (December).

      Month("2021-07-08T09:10:11Z[UTC]")

      => 7

      Now()

      Return the current date AND time.

      Now()

      => 2021-08-08T09:10:11Z[UTC]

      Or(logical1, [logical2, ...])

      Return true if any one of the arguments is true.

      Or({Field1}, {Field2})

      => 1 (if one field value is truthy)

      PathAsList()

      Convert Path to strings

      PathAsList()

      => Apple,Iphone,13 (given record’s path is Apple/Iphone/13)

      PathStartsWith (column)

      Check if path starts with a tag or sub path

      PathStartsWith("Apple/Iphone")

      => True (given record’s path is Apple/Iphone/13)

      PathEndsWith (column)

      Check if path ends with a tag or sub path

      PathEndsWith("13")

      => True (given record’s path is Apple/Iphone/13)

      PathContains (column)

      Check if path contains a tag

      PathContains("Iphone")

      => True (given record’s path is Apple/Iphone/13)

      PathLastModifiedTime() Return the last modified time when updating the path for a record

      PathLastModifiedTime() 

      => 2021-07-08T09:10:11Z[UTC]

       

      PathLen ()

      Return the length of a path PathLen() → 3 (given record’s path is Apple/Iphone/13)

      Power(base, power)

      Compute the specified base to the specified power.

      Power(2, 3)

      => 8

      RegexMatch(value, regex)

      Return whether the input text matches a regular expression.

      RegexMatch("Hello", "[0-9\\s+\\+]+")

      => False

      RegexReplace(value, regex, replacementText)

      Substitute all matching substrings with a replacement string value.

      RegexReplace("To9", "0-9s+++", "night")

      => Tonight

      Replace(value, start_character, number_of_characters, replacement)

      Replace the number of characters beginning with the start character with the replacement text.

      Replace("Hello", 1, 5, "i")

      => Hi

      ResultWithColor (color, value)

      Return value and fills color based on the value 

      If(len(column1) > 10, ResultWithColor(Color("Red"), "Too long"), "Ok")

      => Too long (given column1's value is "Hello world")

      Right(value, howMany)

      Extract howMany characters from the end of the string.

      Right("Hello world", 5)

      => world

      Round(value, precision)

      Round the value to the number of decimal places given by "precision".

      Round(2.3456, 2)

      => 2.35

       RoundDown(value, precision)

      Round the value down to the number of decimal places given by "precision".

      RoundDown(2.3456, 2)

      => 2.34

      RoundUp(value, precision)

      Round the value up to the number of decimal places given by "precision".

      RoundUp(2.3456, 2)

      => 2.35

      Second([datetime])

      Return the second of a datetime as an integer between 0 and 59.

      Second("2021-07-08T09:10:11Z[UTC]")

      => 11

      Shortest(list)

      Get shortest text from the list

      Shortest(split(column1,"\n")

      => “Hello“ (given column1’s value is “Hello\nWorld!“)

      Split(value, delimiter)

      Split string with delimiter (default is " " if not specified)

      Split("Hello world", “  ”)

      => ["Hello", "world"]

      Sqrt(value)

      Return the square root of a nonnegative number.

      Sqrt(100)

      => 10

      StartsWith(value, prefix)

      Check if a string starts with a specified character.

      StartsWith("Hello world", "Hello")

      => True

      Status(column)

      Get the dependency status of a column Status(English) → Out-of-date (supposed dependency status is Out-of-date)

      Substitute(string, old_text, new_text)

      Replace occurrences of old_text with new_text.

      Substitute("Hello world", "Hello", "Hi")

      => Hi world

      Sum(number1, [number2, ...])

      Sum together the numbers. Equivalent to number1 + number2 + ...

      Sum(1, 2, 3)

      => 6

      ToDateTime(value, format)

      Convert a string to datetime format

      ToDateTime("2021-07-08", "yyyy-MM-dd")

      => 2021-07-08T00:00:00Z[UTC]

      Today()

      Return the current date with zero time

      Today()

      => 2021-08-08T00:00:00Z[UTC]

      Trim(value)

      Remove whitespace at the beginning and end of the string.

      Trim("  Hello  ")

      => Hello

      True()

      Return true value

      True ()

      => True

      Upper(value)

      Make string uppercase.

      Upper("Hello")

      => HELLO

      ValueAt(list, position)

      Get the value at position of the list

      ValueAt(column1, 1)

      => "Hello" (given column1’s value is [“Hello“, “World“])

      ValueAt(split(column1,"-"), 2)

      => “World“ (given column1’s value is “Hello-World“)

      ValueFirst(list)

      Get the first value of the list

      ValueFirst(column1)

      => "Hello" (given column1’s value is [“Hello“, “World“])

      ValueFirst(split(column1,"-"))

      => “Hello“ (given column1’s value is “Hello-World“)

      ValueLast(list)

      Get the last value of the list

      ValueLast(column1)

      => "World" (given column1’s value is [“Hello“, “World“])

      ValueLast(split(column1,"-"))

      => “World“ (given column1’s value is “Hello-World“)

      WordCount(value)

      Count the number of words in the string (available to language column) 

      WordCount("Hello world")

      => 2

      Year([date])

      Return the four-digit year of a datetime.

      Year("2021-07-08T09:10:11Z[UTC]")

      => 2021

       

      Was this article helpful?


      11 out of 11 found this helpful

      Still need some help?

      Contact Support