Skip to main content

Numbers

Operation

Format

Description

Example

Add Time

AddTime({VALUE}. {AMOUNT}, {UNIT})

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 Sum

ArraySum({A,B} {SEPARATOR})

Sum of two or more values provided in an array.

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

Result: 16

Average

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

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({A})

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

Input: ConvertToNumber({10.0})

Result: 10

Date Diff

DateDiff({START_DATE}, {END_DATE}, {TIME_UNIT}, {TIME_ZONE})

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({DATE_A}, {DATE_B},{EXCLUDE_WEEKENDS?}{"TIME_ZONE"})

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({A_COLUMN}, {ZONE})

Returns the hour for a specified time and time zone.

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

Result: 17

Index of

IndexOf({A}, {FIND})

Returns the first index of {Find} in the input {A}. If not found, will return -1. If found it will return the index value from where the match was found.

Example:

Input: IndexOf("abc123", "xyz")

Result: -1

Example:

Input: IndexOf("abc@tonk.com", "@tonk")

Result: 3

Last Index of

LastIndexOf({ABC},{FIND},{n})

Returns the last index of {FIND} in the input {A} 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},{0})

Result: 12

Length

Length({A})

Returns the number of characters in the input string {A}.

Input: Length(tonkean)

Result: 6

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}, {TIME ZONE})

Returns the minute value of {DATE} for the specified {TIME ZONE}.

Input: Minute(21 April 2022, UTC)

Result: 57

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

Percentage

({A} / {B}) * 100

Portion of a whole value expressed as a percentage.

Input: (1/2) * 100

Result: 50%

Pow

{A} ^ {B}

Product of one value to the power of another value.

Input: 2^3

Result: 8

Random

Random({START NUMBER},{MAX NUMBER})

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

Subtract

{A} - {B}

Difference between two or more values.

Input: 9 - 6

Result: 3

Sum

{A} + {B}

Sum of two or more values.

Input: 2 + 3

Result: 5

Take Not Empty

TakeNotEmpty([{A}])

The first value that is not empty from a list of values.

Input: TakeNotEmpty(["hello","world"])

Result: hello

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}, {FIRST WEEK DAY}, {MIN DAYS IN FIRST WEEK}, {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.

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

  • MIN DAYS IN FIRST WEEK - (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.

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

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

Result: 25

In this section: