Skip to main content

Numbers

Operation

Format

Description

Example

Add Time

AddTime({Time}, {AmountOfTime}, {UnitOfTime})

Adds units of time to a specified date and time, returning a result in Unix time.

Input: AddTime(“Jan 25 2021”, 3, "Days")

Result: 1611792000000

Array Length

ArrayLength({InputArray}, {Separator})

Returns the number of items in an array for a given input. An optional {Separator} character delineates the items. The default {Separator} is a comma (,).

Input: ArrayLength(“546, Apple, ABC 123”)

Result: 3

Array Sum

ArraySum({InputArray}, {Separator})

Sum of two or more values provided in an array.

Input: ArraySum("1,3,5,7", ",")

Result: 16

Average

Avg([{Number1},{Number2},{Number3}])

Average of the total across the columns. There is no limit to the number of columns that be used in this formula.

Input: Avg(2,4,6)

Result: 4

Convert to Number

ConvertToNumber({Input})

Converts a value from any formatted number field into an integer.

Input: ConvertToNumber("10.0")

Result: 10

Date Diff

DateDiff({StartDate}, {EndDate}, {TimeUnit}, {TimeZone})

Difference in time between two dates, in the time unit specified.

Input: DateDiff("13 Mar 2022", "13 Jul 2022", "Hour", "UTC")

Result: 2928

Days Differential

DaysDiff({StartDate}, {EndDate},{ExcludeWeekends}{TimeZone})

Difference in days between two dates in the specified time zone.

Input: DaysDiff("03 April 2022", "03 May 2022", false, "UTC")

Result: 30

Divide

{A} / {B}

Product of two values divided by one another.

Input: 26/13

Result: 2

Hour

Hour({Date}, {TimeZone})

Returns the hour for a specified time and time zone.

Input: Hour("13 Mar 2022", "America/Phoenix")

Result: 17

Inner Items Count

InnerItemsCount({RootItem})

Returns the count of inner items contained within a root

Input: InnerItemsCount({RootItem})

Result: 3 (the root item had three inner items)

Last Index Of

LastIndexOf({Input},{Find},{n})

Returns the last index of {Find} in the {Input} based on the index {n}. If not found, will return -1. If found, it will return the index value from where the match was found.

Input: LastIndexOf("Building is fun","fun",14)

Result: 12

Maximum

Max({A}, {B}, {C}, ... )

Largest value of all the values across the columns. There is no limit on the number of columns that can be used in this formula.

Input: Max(3, 6, 9)

Result: 9

Minimum

Min({A}, {B}, {C}, ... )

Smallest value of all the values across the columns. There is no limit on the number of columns that can be used in this formula.

Input: Min(3, 6, 9)

Result: 3

Minute

Minute({Date}, {TimeZone})

Returns the minute value of {Date} for the specified {Zone}.

Input: Minute("24/3/2022 10:54:39", "UTC")

Result: 54

Mod

Mod({Dividend}, {Divisor})

Returns the remainder after a division operation.

Input: Mod(10, 100)

Result: 10

Multiply

{A} * {B}

Product of two fields multiplied together.

Input: 3 * 3

Result: 9

Pow

{A} ^ {B}

Product of one value to the power of another value.

Input: 2^3

Result: 8

Random

Random({MinimumNumber},{MaximumNumber})

Returns a random number from within a specified range. The starting number and maximum number are included within the range.

Input: Random(1, 20)

Result: 13

Round

Round({A}, {B})

Rounds {A} to a specified number of places {B}.

Input: Round(“40.73832”, 2)

Result: 40.74

Square Root

Sqrt({A})

Square root of a value.

Input: Sqrt(9)

Result: 3

String Length

Length({A})

Returns the length of the input string as a numeral.

Input: Length("Tonkean")

Result: 7

To Timestamp

ToTimestamp({Input})

Returns the Unix time for a specified date.

Input: ToTimestamp("21 Dec 2021")

Result: 1640044800000

Trunc

Trunc({A}, {B})

Limits the number of digits of {A} to {B} number of places.

Input: Trunc(“40.73832”, 2)

Result: 40.73

Week Number

WeekNum({Date}, {FirstWeekDay}, {MinDaysInFirstWeek}, {TimeZone})

Returns the number of the week in the year (that is, n out of 52 weeks in the year).

  • Date - (Date) Date input field.

  • FirstWeekDay - (Number) The first day in the week (1 = Sun, 2 = Mon, 3 = Tuesday, and so on).

  • MinDaysInFirstWeek - (Number) The minimum number of days required for the first week of the year to be counted. For example, if you want to only count the first full week, use a value of 7. Alternatively, if the first day of the year is Saturday and you want to count that as a week, use value 1. Default value is 1.

  • Zone - (String) The timezone. Default value is UTC.

Input: WeekNum("17 Jun 2022", 2, 1, "UTC")

Result: 25