Expr Function Reference

All standard Expr functions are listed below, grouped by category. Use the right navigation panel to scroll through functions, or Ctrl+F to find a specific function.

Notes about functions

A function may take zero, one or more arguments. Some functions can take an unlimited number of arguments.

Parameter Types

Functions expect certain value types for each parameter. When an unexpected type is provided, Structure will attempt to convert it to an acceptable type. If it can't, the function may consider the value undefined and ignore the value, or it may return an error.

For example, the MIN function is used to select the smallest number from a series; if the text value "10" is passed as of the parameters, it will convert that to the number 10. If "ABC" is passed, it won't be able to convert that to a number and will return an error.

For more information about how Structure converts values, see Value Conversions.

Undefined Values

A variable used in a formula may have undefined value. Usually it means that the value for an issue is not set – for example, Resolution field will produce undefined value until the issue is resolved. When a function that manipulates values receives an undefined value as its primary argument, the return value will also typically be undefined.

Arrays

Many functions can also be applied to arrays. When the argument is an array, it will be treated in one of the following ways:

Numbers and Text - If the function is expecting a number or text, it will attempt to convert the first element of the array into the appropriate type. If it fails, it will return an error. If there are more than one elements in the array, it will also return an error.
Text/Joined - If an array is passed as the parameter, each element in the array will be converted to text and joined together with a comma. (See Text vs. Text/Joined)
/Each - If an array is passed as the parameter, the function will be applied separately to each element in the array. The result will be an array with the function applied to each value.

Array Functions

ALL

ALL(A, $)

Checks that $ returns a truthy value for all elements in the array.

Parameter	Type	Description
`A`	`Array`	Array of elements to be used in $.
`$`	`User Function`	Function to be applied to each element.
→ Result	`Boolean`	Returns true if $ returns a truthy value for all elements in the array. Otherwise, returns false. If the array is empty, returns true.

Examples:

ALL(fixVersions, $.startDate > TODAY())→ Returns true if all fixVersions start after the current date.

ANY

ANY(A, $)

Checks if the array has at least one element for which $ returns true.

Parameter	Type	Description
`A`	`Array`	Array of elements to be used in $.
`$`	`User Function`	Function to be applied to each element.
→ Result	`Boolean`	Returns true if $ returns a truthy value for at least one elements in the array. Otherwise, returns false. If the array is empty, returns false.

Examples:

ANY(fixVersions, $.startDate < TODAY())→ Returns true if any fixVersions started before the current date.

ARRAY

ARRAY(Element1, Element2, ..., ElementN)

Creates an array from a list of elements.

Parameter	Type	Description
`Element1, Element2, ..., ElementN`	`Any`	Elements to be added to the array.
→ Result	`Array`	Array containing all of the elements.

Example:

ARRAY(1, 2, 3) → ARRAY(1, 2, 3)

COMPACT

COMPACT(A)

Removes all undefined values from the array.

Parameter	Type	Description
`A`	`Array`	Array to be compacted.
→ Result	`Array`	Compacted array.

Note: error values and empty arrays are preserved.

Examples:

COMPACT(ARRAY(1, 2, undefined, 3)) → ARRAY(1, 2, 3)

CONTAINS

CONTAINS(A, Element)

Searches an array for a specified element. The comparison is done in the same way function EQ (=) works.

Parameter	Type	Description
`A`	`Array`	Array to search in.
`Element`	`Any`	Element to look for.
→ Result	`Boolean`	Returns true if the array contains the specified element. Otherwise, returns false.

Examples:

CONTAINS(ARRAY(1, 2, 3), 2) → 1
CONTAINS(ARRAY(1, 2, 3), 5) → 0
fixVersions.CONTAINS("v1") → returns 1 if one of the versions in the Fix Version/s field is "v1"

Please note that CONTAINS does not perform text matching.For example, if Fix Version/s field contains version "v1.1", the above example – fixVersion.CONTAINS("v1") – will return 0. You can apply text matching to look for elements that match a particular pattern, for example: fixVersion.ANY($.MATCH("v1*"))

CONTAINS_ALL

CONTAINS_ALL(A, Elements_Array)

Searches an array for all of the elements passed in the Elements_Array parameter.

Parameter	Type	Description
`A`	`Array`	Array to search in.
`Elements_Array`	`Array`	Array of elements to look for.
→ Result	`Boolean`	Returns true (1) if `A` contains all elements contained in `Elements)Array`. Otherwise, returns false (0).

Duplicates are not taken into account, so CONTAINS_ALL(ARRAY(1), ARRAY(1,1)) → 1 .

Examples:

CONTAINS_ALL(ARRAY(1, 2, 3), ARRAY(1, 2, 3)) → 1
CONTAINS_ALL(ARRAY(1, 2, 3), ARRAY(1, 2, 4)) → 0

CONTAINS_ANY

CONTAINS_ANY(A, Elements_Array)

Searches an array for any of the elements passed in the Elements_Array parameter.

Parameter	Type	Description
`A`	`Array`	Array to search in.
`Elements_Array`	`Array`	Array of elements to look for.
→ Result	`Boolean`	Returns true (1) if `A` contains any elements contained in `Elements_Array`. Otherwise, returns false (0).

Examples:

CONTAINS_ANY(ARRAY(1, 2, 3), ARRAY(2, 9, 7)) → 1
CONTAINS_ANY(ARRAY(1, 2, 3), ARRAY(4, 9, 7)) → 0

FILTER

FILTER(A, $)

Filters the values in an array and produces a new array that retains only elements for which the user function $ returns a true value (considered as a Boolean).

Parameter	Type	Description
`A`	`Array`	Array of elements to be filtered.
`$`	`User Function`	Function to be applied to each element.
→ Result	`Array`	New filtered array.

Example:

ARRAY(100, 200, 300).FILTER(x -> x < 250) → ARRAY(100, 200)
worklogs.FILTER($.author = ME()) → Returns an array containing worklog values created by the current user

FIRST

FIRST(A)

Returns first element of the array, or undefined if the array is empty.

Parameter	Type	Description
`A`	`Array`	Array of elements.
→ Result	`Any`	First element contained in the array. If empty, returns undefined.

Example:

FIRST(ARRAY(1, 2,3)) → 1

FLATTEN

FLATTEN(A)

Given an array of arrays, makes a single array, using one-step flattening (each element in the sub-arrays is added to the top array as a single element).

Parameter	Type	Description
`A`	`Array`	Array of arrays, or array containing arrays and non-array values.
→ Result	`Array`	A single array, containing all elements.

Example:

FLATTEN(ARRAY(ARRAY(1, 2), 100, ARRAY(2, 3), 10)) → ARRAY(1, 2, 100, 2, 3, 10)

GET

GET(A, Index)

Retrieves an element from an array based on its index. Array indexes are 0-based. Returns undefined if index is out of array bounds.

Parameter	Type	Description
`A`	`Array`	Array to search.
`Index`	`Integer`	Numeric index, 0-based: 0 corresponds to the first value in `A`, 1 corresponds to the second, and so on.
→ Result	`Any`	The value corresponding to `Index`.

Example:

GET(ARRAY(1, 25, 2, 18, 100), 1) → 25

GROUP

GROUP(A, $)

Groups array elements into buckets based on the value produced by the user function $ for each element. The result is an array of groups (key-value maps).

Parameter	Type	Description
`A`	`Array`	Array of elements to be grouped.
`$`	`User Function`	Function to be applied to each element.
→ Result	`Array`	Array of groups. Each group G has G.group which contains the value that the user function produced, and G.elements, which contains an array of all elements that produced that value. The order of groups corresponds to the order of the grouping values as they first appear in the source array.

Example:

Suppose the worklogs attribute contains the following work logs for the issue:

Author: Bob, Time Spent: 1 hour, Date: Feb-1
Author: Alice, Time Spent: 2 hours, Date: Feb-1
Author: Bob, Time Spent: 3 hours, Date: Feb-2

Let's write this using the following pseudo-formula:

worklogs = ARRAY( (author: Bob, timeSpent: 1h, startDate: Feb-1), (author: Alice, timeSpent: 2h, startDate: Feb-1), (author: Bob, timeSpent: 3h, startDate: Feb-2) )

Then the following examples show grouping these work logs by author and by date:

worklogs.GROUP($.author) → ARRAY( (group: Bob, elements: ARRAY( (author: Bob, timeSpent: 1h, startDate: Feb-1), (author: Bob, timeSpent: 3h, startDate: Feb-2) )), (group: Alice, elements: ARRAY( (author: Alice, timeSpent: 2h, startDate: Feb-1) )) )
worklogs.GROUP($.startDate) → ARRAY( (group: Feb-1, elements: ARRAY( (author: Bob, timeSpent: 1h, startDate: Feb-1), (author: Alice, timeSpent: 2h, startDate: Feb-1) )), (group: Feb-2, elements: ARRAY( (author: Bob, timeSpent: 3h, startDate: Feb-2) )) )

The expressions above show the key-value map values using a pseudo-formula. It is used only to demonstrate the values; Expr currently does not support this language or any way to create arbitrary key-value maps.

INDEX_OF

INDEX_OF(A, Element)

Finds the first occurrence of an element in the array. The comparison is done in the same way function EQ (=) works.

Parameter	Type	Description
`A`	`Array`	Array to be searched.
`Element`	`Any`	Element to search for.
→ Result	`Integer`	Returns an index of a first occurrence of a specified element in an array. If the element is not found, returns undefined.

Note: the array is zero-based, so the first element is at Index = 0.

Example:

INDEX_OF(ARRAY(1,3,3,3,5), 3) → 1

INDEXES

INDEXES(A)

Creates an array of indexes of A, starting with 0. For example, an array with 3 elements would return ARRAY(0, 1, 2).

Parameter	Type	Description
`A`	`Array`	Array of elements.
→ Result	`Array`	Array of indexes, starting at 0.

Example:

INDEXES(ARRAY("Cat","DOG","BIRD")) → ARRAY(0,1,2)

IS_ARRAY

IS_ARRAY(Value)

Returns true if the value is an array.

Parameter	Type	Description
`Value`	`Any`	Value to check.
→ Result	`Boolean`	Returns true (1) if the Value is an array; false (0) if the Value is not not an array.

Examples:

IS_ARRAY(ARRAY(1,2,3)) → 1

IS_EMPTY

IS_EMPTY(A)

Returns true if the array is empty.

Parameter	Type	Description
`A`	`Array`	Array of elements.
→ Result	`Boolean`	Returns true (1) if the array is empty; false (0) if the array is not empty. Note: will return true for undefined, but false for an empty text string ("").

Examples:

IS_EMPTY(ARRAY("Cat","DOG","BIRD")) → 0
IS_EMPTY(ARRAY()) → 1

JOIN

JOIN(A)

JOIN(A, Sep, Gs, Ge)

Produces a text string representing any value. If the given value is an array, the text representation is composed by converting each element to text (per Text parameter conversion) and joining them together using ", " as a separator. Then the joined text is put into group parentheses Gs and Ge. If an array element is an array itself, the procedure repeats recursively.

A non-default separator may be passed as the Sep parameter.

Parameter	Type	Description
`A`	`Any`	Value to convert to text.
`Sep` (Optional)	`Text`	Optional separator to replace ", ".
`Gs` (Optional)	`Text`	Optional separator to replace "(".
`Ge` (Optional)	`Text`	Optional separator to replace ")".
→ Result	`Text`	Text comprised of all values in the array.

Example:

JOIN(ARRAY("Cat","Dog","Bird")) → (Cat, Dog, Bird)
JOIN(ARRAY(ARRAY("Cat","Dog","Bird")), ARRAY("Sheep", "Pig")), " + " , "{" , "}" ) → {{Cat + Dog + Bird} + {Sheep + Pig}}
JOIN("Cat") → (Cat)

LAST

LAST(A)

Returns the last element of the array, or undefined if the array is empty.

Parameter	Type	Description
`A`	`Array`	Array of any type.
→ Result	`Any`	Last element contained in the array. If empty, returns undefined.

Example:

LAST(ARRAY(1, 2, 3)) → 3

LAST_INDEX_OF

LAST_INDEX_OF(A, Element)

Finds the last occurrence of an element in the array.

Parameter	Type	Description
`A`	`Array`	Array to be searched.
`Element`	`Any`	Element to search for.
→ Result	`Integer`	Returns an index of a last occurrence of a specified element in an array. If the element is not found, returns undefined.

Note: the array is zero-based, so the first element is at Index = 0.

Example:

LAST_INDEX_OF(ARRAY(1,2,2,2,3), 2) → 3

MAP

MAP(A, $)

Applies the user function to every element of the array.

Parameter	Type	Description
`A`	`Array`	Array of elements to be mapped.
`$`	`User Function`	Function to be applied to each element.
→ Result	`Array`	Array containing the results.

Example:

ARRAY(1, 2, 3).MAP(x -> x * 100) → ARRAY(100, 200, 300)
affectedVersions.MAP($.releaseDate - $.startDate)

MERGE_ARRAYS

MERGE_ARRAYS(Array1, Array2, ..., ArrayN)

Produces a single array with the elements of all parameter arrays. Equal to ARRAY(a1, a2, ...).FLATTEN().

Parameter	Type	Description
`Array1, Array2, ..., ArrayN`	`Array`	Arrays to be grouped.
→ Result	`Array`	Single array containing all elements.

Example:

MERGE_ARRAYS(ARRAY(1, 2, 3),ARRAY(4,5,6),ARRAY(7)) → ARRAY(1,2,3,4,5,6,7)

NONE

NONE(A, $)

Checks that $ returns false for all elements in the array.

Parameter	Type	Description
`A`	`Array`	Array of elements to be used in $.
`$`	`User Function`	Function to be applied to each element.
→ Result	`Boolean`	Returns `true` (1) or `false` (0). If the array is empty, returns `true` (1).

This function is the inverse of ANY()

Examples:

NONE(fixVersions, $.startDate < TODAY())→ Returns true if no fixVersions started before the current date.

RECURSIVE_FLATTEN

RECURSIVE_FLATTEN(A)

Performs recursive flattening and compacting of the array. The resulting array is guaranteed to be flat and not contain undefined values.

Parameter	Type	Description
`A`	`Array`	Array of arrays.
→ Result	`Array`	A single array, containing all elements with no undefined values.

Examples:

RECURSIVE_FLATTEN(ARRAY(ARRAY(1, undefined, 2), ARRAY(2, 3), 100)) → ARRAY(1, 2, 2, 3, 100)

REDUCE

REDUCE(A, $)

Reduces an array to a single value based on $.

Parameter	Type	Description
`A`	`Array`	Array to be reduced.
`$`	`User Function`	Function containing two parameters.
→ Result	`Any`	Applies the function to the first two elements in the array. Then applies the function again, using the resulting value and the third element. And so on. For an empty array, returns undefined. For an array with one element, returns that element.

Examples:

ARRAY(2, 3, 2, 1, 2).REDUCE((a, b) -> a * b) → 24

REVERSE

REVERSE(A)

Reverses the order of elements in the array.

Parameter	Type	Description
`A`	`Array`	Array of elements.
→ Result	`Array`	Array with elements in reverse order.

Example:

REVERSE(ARRAY(1, 2, 3, 4)) → ARRAY(4, 3, 2, 1)

SEQUENCE

SEQUENCE(from, to)

Creates an array of integer numbers, starting with from and ending with to (inclusive). If to is less than from, the sequence will be descending.

Parameter	Type	Description
`from`	`Integer`	Starting integer.
`to`	`Integer`	Ending integer.
→ Result	`Array`	Array of integers.

Examples:

SEQUENCE(3, 6) → ARRAY(3, 4, 5, 6)
SEQUENCE(6, 3) → ARRAY(6, 5, 4, 3)

SIZE

SIZE(A)

Returns the number of elements in the array.

Parameter	Type	Description
`A`	`Array`	Array of elements.
→ Result	`Integer`	Number of elements contained in the array.

Example:

SIZE(ARRAY(1, 2, 3, 4)) → 4
SIZE(ARRAY(1, ARRAY(2, 3, 4), undefined)) → 3

SORT

SORT(A)

Sorts the array using a natural order of elements: numbers, text values, item values, and then array values (which are compared with respect to all the non-array elements). Item values are compared first by item type (lexicographically, so a user would come before a version because "u" comes before "v") and then either by the item's natural order, if it exists, or by the item's text representation, if it doesn't.

Parameter	Type	Description
`A`	`Array`	Array of elements to be sorted.
→ Result	`Array`	Sorted array.

Example:

SORT(ARRAY(3,1,2)) → ARRAY(1,2,3)

SORT_BY

SORT_BY(A, $)

Sorts the array by comparing the values produced by calling the user function $ for each array element. Results of the user function $ calls are compared the same way as the SORT function.

Parameter	Type	Description
`A`	`Array`	Array of elements to be sorted.
`$`	`User Function`	Function to be applied to each element.
→ Result	`Array`	Sorted array.

Example:

SORT_BY(fixVersions, $.releaseDate)

SUBARRAY

SUBARRAY(A, from, to)

Produces an array with elements from the given array. Parameters from (inclusive) and to (exclusive) define the range. Indexes are zero-based.

Parameter	Type	Description
`A`	Array	Array of elements.
`from`	`Integer`	Starting index (inclusive).
`to`	`Integer`	Ending index (exclusive).
→ Result	`Array`	Array containing elements between `from` and `to`.

Examples:

SUBARRAY(ARRAY("Cat", "Dog", "Mouse", "Bird", "Sheep"), 1, 3) → ARRAY("Dog", "Mouse")

UNIQUE

UNIQUE(A)

Removes duplicates from the array. The order of non-duplicate elements is preserved.

Parameter	Type	Description
`A`	`Array`	Array of elements.
→ Result	`Array`	Array with duplicates removed.

Note: the equality is strict, so "0" and 0 would be different elements.

Example:

UNIQUE(ARRAY(1, 2, 1, 3, 3, 4)) → ARRAY(1, 2, 3, 4)

WITHOUT

WITHOUT(A, Value)

Returns a new array with all the elements from the input array except those equal to Value.

Parameter	Type	Description
`A`	`Array`	Array of elements.
`Value`	`Any`	Element to be removed.
→ Result	`Array`	New array with `Value` removed.

Equality is done using the same logic as the = operator and CONTAINS.

Example:

WITHOUT(ARRAY(1, 2, 1, 3, 3, 4), 1) → ARRAY(2, 3, 3, 4)

Conditional Functions

CASE

CASE(Value, Match1, Result1, Match2, Result2, ..., Default)

Checks if the Value matches against several checks and returns a corresponding result.

Parameter	Type	Description
`Value`	`Text/Joined`	Value to check.
`Match1`, `Match2`, ..., `MatchN`	`Text/Joined`	Text patterns to check against. The first matching pattern will define the result. A pattern can be an exact value, a wildcard expression or a regular expression. See Expr Pattern Matching for details.
`Result1`, `Result2`, ..., `ResultN`	`Any`	Values to return from the function, each value corresponds to the preceding `Match` parameter.
`Default` (Optional)	`Any`	Optional default value, to be returned if none of the patterns match. If not specified, `undefined` is returned.
→ Result	`Any`	Result1, Result2, etc. depending on which pattern matched, or Default, or undefined.

This function is typically used to map text values to numbers.

Examples:

CASE(Priority; "Highest"; 10; "High"; 5; "Medium"; 3; 1)
CASE(Version; "V1*"; 1; "V2*"; 2)

If the Value is undefined, the function immediately returns the Default result (or undefined if there's no default), so there is usually no need to use undefined as one of the matches.

CHOOSE

CHOOSE(Index; Value1; Value2; ...)

Based on the value of Index, returns the corresponding value from the argument list.

Parameter	Type	Description
`Index`	`Number`	Numeric index, with 1 corresponding to `Value1`, 2 corresponding to `Value2` and so on.
`Value1`, `Value2`, ..., `MatchN`	`Any`	The values to pick from.
→ Result	`Any`	The Value corresponding to Index.

Examples:

CHOOSE(1; "A"; "B"; "C") → "A"
CHOOSE(2; "A"; "B"; "C") → "B"

DEFINED

DEFINED(Value)

Checks if the value is defined.

Parameter	Type	Description
`Value`	`Any`	Value to check.
→ Result	`Boolean`	Returns false (0) if `Value` is `undefined` and true (1) otherwise.

Example:

IF(DEFINED(Resolution); ...)

DEFAULT

DEFAULT(Value; DefaultValue)

Substitutes DefaultValue if the Value is undefined.

Parameter	Type	Description
`Value`	`Any`	Value to check.
`DefaultValue`	Any	Value to be returned if `Value` is `undefined`.
→ Result	`Any`	If `Value` is defined, returns `Value`. Otherwise, returns `DefaultValue`.

Examples:

DEFAULT(100; 500) → 100
DEFAULT(undefined; 500) → 500

IF

IF(Condition1; Result1; Condition2; Result2; ...; Default)

Checks one or several conditions and returns the result associated with the first true condition.

Parameter	Type	Description
`Condition1, Condition2, ..., Condition3`	`Any`	Value to check. The values are evaluated using "truthfulness check" – the first value that is "truthy" (not undefined, not zero and not an empty string), will define the returned value.
`Result1`, `Result2`, ..., `ResultN`	`Any`	Results to be returned, each result corresponding to the preceding check.
`Default` (Optional)	`Any`	Optional default value, to be returned if none of the patterns match. If not specified, `undefined` is returned.
→ Result	`Any`	Result1, Result2, etc. depending on which pattern matched, or Default, or undefined.

Examples:

IF(Estimate > 0; Duration / Estimate; 0)
IF(N = 0; "No apples"; N = 1; "One apple"; CONCAT(N; " apples"))

IFERR

IFERR(Value; FallbackValue)

Checks if calculating Value produced an error and substitutes FallbackValue instead of the error value.

Parameter	Type	Description
`Value`	`Any`	Value to check.
`FallbackValue`	`Any`	Value to be returned if calculating `Value` produces an error.
→ Result	`Any`	If `Value` calculated without an error, returns `Value`. Otherwise, returns `DefaultValue`.

Normally, if an error occurs while calculating a formula, it is propagated upwards, and the result of the whole expression will be an error. This function helps circumvent that.

Example:

IFERR(100 / 0; 100) → 100

ISERR

ISERR(Value; ErrorCode)

Checks if calculating value produced an error.

Parameter	Type	Description
`Value`	`Any`	Value to check.
`ErrorCode` (Optional)	`Integer`	Optional error code. See Expr Error Codes for a list.
→ Result	`Boolean`	Returns true (1) if there was an error. If `ErrorCode` is specified, returns true only if the error was of the specified error code.

Examples:

ISERR("Ham") → 0
ISERR(1 / 0) → 1
ISERR(1 / 0, 4) → 1 //Note: Error code 4 is an Arithmetic Error

Date and Time Functions

Date/time functions operate with a numeric representation of time. A moment in time is represented as a number of milliseconds since midnight, January 1st 1970, GMT. Negative values are allowed, representing times prior to January 1st 1970.

To display a result of a date/time calculation in a readable way, you need to either configure the Formula to use a date/time format, or use one of the conversion functions to turn the value into a human-readable text.

Many of the date / time functions depend on the current user's time zone.

DATE

DATE(Text; Locale; TimeZone)

Converts a text representation of a date to a number.

Parameter	Type	Description
`Text`	`Text/Each`	The text value to convert.
`Locale` (Optional)	`Text`	Optional locale identifier, such as "fr_FR". If not specified, user's locale is used.
`TimeZone` (Optional)	`Text`	Optional time zone identifier, such as "America/New_York".
→ Result	Number	Timestamp, corresponding to midnight of the specified date at the specified time zone. If conversion is unsuccessful, returns an error.

The conversion uses the standard formats for representing dates:

Format "yyyy-MM-dd", like "2017-04-15".
Standard formats for the specified locale.
Jira formats, as specified in the Jira's system settings.

Examples:

DATE("2016-01-01")
DATE("31/Dec/16")
DATE("12/31/2016", "en_US", "America/New_York")

DATE_ADD

DATE_ADD(DateTime, Number, Unit)

Adds the specified integer number of seconds, minutes, hours, days, months or years to the date or date/time value.

Parameter	Type	Description
`DateTime`	`Date/Each`	Date or date/time value.
`Number`	`Integer`	The integer number of units of time to add.
`Unit`	`Text`	A text value specifying the unit of time: `"seconds"`, `"minutes"`, `"hours"`, `"days"`, `"months"`, `"years"`
→ Result	`Date`	Adjusted `DATETIME`.

Examples:

DATE_ADD(DATE("2016-01-31"), 1, "day") → DATE("2016-02-01")
DATE_ADD(DATE("2016-01-31"), 1, "month") → DATE("2016-02-29")
DATE_ADD(DATE("2016-02-29"), 1, "year") → DATE("2017-02-28")
DATE_ADD(DATETIME("2016-01-31 10:30:00"), 3, "hours") → DATETIME("2016-01-31 13:30:00")
DATE_ADD(DATETIME("2016-01-31 23:59:59"), 2, "minutes") → DATETIME("2016-02-01 00:01:59")

The second parameter (Number) must be an integer.

DATE_SET

DATE_SET(DateTime, Number, Unit)

Sets the specified part of the date or date/time to the specific integer value. Note that unlike DATE_ADD and DATE_SUBTRACT, you can specify additional units like "day_of_week".

Parameter	Type	Description
`DateTime`	`Date/Each`	Date or date/time value.
`Number`	`Integer`	The integer number to be set as the unit value in this date/time.
`Unit`	`Text`	A text value specifying the unit of time: `"second"`, `"minute"`, `"hour"`, `"day"`, `"month"`, `"year", "day_of_week".`
→ Result	`Date`	Adjusted `DATETIME`.

Examples:

DATE_SET(DATE("2016-01-31"), 2017, "year") → DATE("2017-01-31")
DATE_SET(DATE("2016-01-31"), 2, "month") → DATE("2016-02-29")
DATE_SET(DATETIME("2016-02-29 15:30"), 10, "day") → DATETIME("2016-02-10 15:30")
DATE_SET(DATE("2017-04-01"), 7, "day_of_week") → DATE("2017-04-02")
DATE_SET(DATETIME("2016-01-31 10:30:00"), 0, "hour") → DATETIME("2016-01-31 00:30:00")

DATE_SUBTRACT

DATE_SUBTRACT(DateTime, Number, Unit)

Subtracts the specified integer number of seconds, minutes, hours, days, months or years from the date or date/time value.

Parameter	Type	Description
`DateTime`	`Date/Each`	Date or date/time value.
`Number`	`Integer`	The integer number of units of time to subtract.
`Unit`	`Text`	A text value specifying the unit of time: `"seconds"`, `"minutes"`, `"hours"`, `"days"`, `"months"`, `"years"`
→ Result	`Date`	Adjusted `DATETIME`.

Examples:

DATE_SUBTRACT(DATE("2016-02-01"), 1, "day") → DATE("2016-01-31")
DATE_SUBTRACT(DATE("2016-02-29"), 1, "month") → DATE("2016-01-29")
DATE_SUBTRACT(DATE("2017-02-28"), 1, "year") → DATE("2016-02-28")
DATE_SUBTRACT(DATETIME("2016-01-31 10:30:00"), 3, "hours") → DATETIME("2016-01-31 07:30:00")
DATE_SUBTRACT(DATETIME("2016-02-01 00:01:59"), 2, "minutes") → DATETIME("2016-01-31 23:59:59")

DAY

DAY(DateTime)

Returns the day of the month for the given date or date/time value.

Parameter	Type	Description
`DateTime`	`Date/Each`	Date/time value.
→ Result	Number	Numeric value for the day of the month. The result is calculated using the current user's time zone.

Example:

DAY(DATE("2017-04-15")) → 15

DAYS_BETWEEN

DAYS_BETWEEN(DateTime1, DateTime2)

Calculates the number of full days (24 hour periods) between two date or date/time values.

Parameter	Type	Description
`DateTime1`	`Date`	First date or date/time value to compare.
`DateTime2`	`Date`	Second date or date/time value to compare.
→ Result	Number	The number of full days between the dates. Returns a negative value if `DateTime2` occurs earlier than `DateTime1`.

Examples:

DAYS_BETWEEN(DATE("2017-01-01"), DATE("2017-02-01")) → 31
DAYS_BETWEEN(DATE("2017-01-01"), DATE("2017-01-01")) → 0
DAYS_BETWEEN(DATE("2017-01-01"), DATE("2016-01-01")) → -366
DAYS_BETWEEN(DATETIME("2017-01-01 00:00"), DATETIME("2017-01-01 23:59")) → 0
DAYS_BETWEEN(DATETIME("2017-01-01 23:59"), DATETIME("2017-01-02 23:58")) → 0
DAYS_BETWEEN(DATETIME("2017-01-01 23:59"), DATETIME("2017-01-02 23:59")) → 1

DATETIME

DATETIME(Text; Locale; TimeZone)

Converts a text representation of a date and time to a numeric representation of that date and time. The resulting timestamp will correspond to the specified date and time at the specified time zone. If seconds are omitted, they will be set to zero.

Parameter	Type	Description
`Text`	`Text/Each`	The text value to convert.
`Locale` (Optional)	`Text`	Optional locale identifier, such as "fr_FR". If not specified, user's locale is used.
`TimeZone` (Optional)	`Text`	Optional time zone identifier, such as "America/New_York".
→ Result	Number	Timestamp, corresponding to the specified date and time at the specified time zone. If seconds are omitted, they will be set to zero. If conversion is unsuccessful, returns an error.

The conversion uses the standard formats for representing dates:

Format "yyyy-MM-dd HH:mm:ss" and the same without seconds, like "2017-04-15 15:00" or "2017-12-31 23:59:59" (using 24-hour clock).
Standard formats for the specified locale.
Jira formats, as specified in the Jira's system settings.

Examples:

DATETIME("2016-01-01 00:01")
DATETIME("31/Dec/16 3:15 pm")
DATETIME("12/31/2016, 3:15 PM", "en_US", "America/New_York")

END_OF_MONTH

END_OF_MONTH(DateTime)

Sets the day in the date/time value to the end of the month. Does not change the time value.

Parameter	Type	Description
`DateTime`	`Date/Each`	Date or date/time value.
→ Result	`Date`	Date is set to end of the month. Time value is unchanged.

Example:

END_OF_MONTH(DATE("2017-04-15")) → DATE("2017-04-30")

FORMAT_DATETIME

FORMAT_DATETIME(DateTime, Format, Locale, TimeZone)

Advanced function to convert a date/time value into a text. Accepts an arbitrary format text and, optionally, locale and time zone settings. Does not depend on the current user's locale nor time zone.

Parameter	Type	Description
`DateTime`	`Date/Each`	The value to convert.
`Format`	`Text`	The format string. For all the options, please see Java documentation for SimpleDateFormat.
`Locale` (Optional)	`Text`	Optional locale identifier. If omitted or `undefined`, will use Jira's system locale. (Not the user's locale!)
`TimeZone` (Optional)	`Text`	Optional time zone identifier. If omitted or `undefined`, will use Jira's system time zone. (Not the user's time zone!)
→ Result	Text	DateTime converted to text.

Examples:

FORMAT_DATETIME(DATE("2017-04-15"), "EEE, MMM d, `yy", "fr_FR") → "sam., avr. 15, `17"
FORMAT_DATETIME(DATETIME("2016-12-31 23:59"), "yyyy-MM-dd'T'HH:mm:ss") → "2016-12-31T23:59:00"

HOUR

HOUR(DateTime)

Returns the hour in the specified date/time value (from 0 to 23).

Parameter	Type	Description
`DateTime`	`Date/Each`	Date or date/time value.
→ Result	`Number`	Numeric value for the hour (0 to 23).

Example:

HOUR(DATETIME("2017-01-01 20:15")) → 20

HOURS_BETWEEN

HOURS_BETWEEN(DateTime1, DateTime2)

Calculates the number of full hours between two date/time values.

Parameter	Type	Description
`DateTime1`	`Date`	First date/time value to compare.
`DateTime2`	`Date`	Second date/time value to compare.
→ Result	`Number`	The number of full hours between the date/time values. Returns a negative value if `DateTime2` occurs earlier than `DateTime1`.

Examples:

HOURS_BETWEEN(DATE("2017-01-01"), DATE("2017-01-02")) → 24
HOURS_BETWEEN(DATETIME("2017-01-01 15:00"), DATETIME("2017-01-01 16:30")) → 1
HOURS_BETWEEN(DATETIME("2017-01-01 23:59"), DATETIME("2017-01-02 00:58")) → 0
HOURS_BETWEEN(DATETIME("2017-01-01 23:59"), DATETIME("2017-01-02 00:59")) → 1

MAKE_DATE

MAKE_DATE(Year, Month, Day)

Creates a date value based on the numbers defining year, month and day. The time is set to midnight in the user's time zone.

Parameter	Type	Description
`Year`	`Number`	Year.
`Month`	`Number`	Number corresponding to the month.
`Day`	`Number`	Number corresponding to the day of the month.
→ Result	`Date`	Date/time set to midnight in the current user's time zone.

Example:

MAKE_DATE(2017, 12, 31)

MAKE_DATETIME

MAKE_DATETIME(Year, Month, Day, Hour, Minute, Second)

Creates a date/time value based on the numbers defining year, month, day, hour, minute and second.

Parameter	Type	Description
`Year`	`Number`	Year.
`Month`	`Number`	Number corresponding to the month.
`Day`	`Number`	Number corresponding to the day of the month.
`Hour`	`Number`	Hour (0-23).
`Minute`	`Number`	Minutes.
`Second`	`Number`	Seconds.
→ Result	`Date`	Date/time based on the provided values. Set to the current user's time zone.

Example:

MAKE_DATETIME(2017, 12, 31, 23, 59, 59)

MINUTE

MINUTE(DateTime)

Returns the minutes in the specified date/time value.

Parameter	Type	Description
`DateTime`	`Date/Each`	Date or date/time value.
→ Result	`Number`	Minutes (from 0 to 59)

Example:

MINUTE(DATETIME("2017-01-01 20:15")) → 15

MONTH

MONTH(DateTime)

Returns the month in the specified date/time value.

Parameter	Type	Description
`DateTime`	`Date/Each`	Date or date/time value.
→ Result	`Number`	Numeric value for the month (1 to 12).

Example:

MONTH(DATE("2017-04-15")) → 4

MONTHS_BETWEEN

MONTHS_BETWEEN(DateTime1, DateTime2)

Calculates the number of months between two date or date/time values.

Parameter	Type	Description
`DateTime1`	`Date`	First date or date/time value to compare.
`DateTime2`	`Date`	Second date or date/time value to compare.
→ Result	`Number`	The number of full months between the date/time values. Returns a negative value if `DateTime2` occurs earlier than `DateTime1`.

Examples:

MONTHS_BETWEEN(DATE("2017-01-01"), DATE("2018-01-01")) → 12
MONTHS_BETWEEN(DATE("2017-01-31"), DATE("2017-02-28")) → 0
MONTHS_BETWEEN(DATE("2017-02-28"), DATE("2017-04-28")) → 2
MONTHS_BETWEEN(DATE("2017-01-01"), DATE("2016-12-01")) → -1

NOW

NOW()

Returns the current date and time.

Parameter	Type	Description
→ Result	`Date`	Current date and time.

Example:

NOW()

PARSE_DATETIME

PARSE_DATETIME(Text, Format, Locale

, TimeZone)

Advanced function to convert a text into a date or date/time value. Accepts an arbitrary format string and, optionally, locale and time zone settings. Does not depend on the current user's locale nor time zone.

Parameter	Type	Description
`Text`	`Text/Each`	The value to convert.
`Format`	`Text`	The format string. For all the options, please see Java documentation for SimpleDateFormat.
`Locale` (Optional)	`Text`	Optional locale identifier. If omitted or `undefined`, will use Jira's system locale. (Not the user's locale!)
`TimeZone` (Optional)	`Text`	Optional time zone identifier. If omitted or `undefined`, will use Jira's system time zone. (Not the user's time zone!)
→ Result	`Date`	Date or date/time value for the original `Text`.

Examples:

PARSE_DATETIME("sam., avr. 15, `17", "EEE, MMM d, `yy", "fr_FR") → DATE("2017-04-15")
PARSE_DATETIME("2016-12-31T23:59:00", "yyyy-MM-dd'T'HH:mm:ss") → DATETIME("2016-12-31 23:59")

SECOND

SECOND(DateTime)

Returns the seconds in the specified date/time value.

Parameter	Type	Description
`DateTime`	`Date/Each`	Date or date/time value.
→ Result	`Number`	Seconds (0 to 59).

Example:

SECOND(DATETIME("2017-04-15 15:30:59")) → 59

START_OF_MONTH

START_OF_MONTH(DateTime)

Sets the day in the date/time value to the first day of the month.

Parameter	Type	Description
`DateTime`	`Date/Each`	Date or date/time value.
→ Result	`Date`	Date is set to first day of the month. Time value is unchanged.

Example:

START_OF_MONTH(DATE("2017-04-15")) → DATE("2017-04-01")

TODAY

TODAY()

Returns the current date with time set to midnight according to the current user's time zone.

Parameter	Type	Description
→ Result	`Date`	Current date

Example:

TODAY()

TRUNCATE_TIME

TRUNCATE_TIME(DateTime)

Removes the time value from the date/time.

Parameter	Type	Description
`DateTime`	`Date/Each`	Date/time value.
→ Result	`Date`	Date, set to midnight in the current user's time zone.

Example:

TRUNCATE_TIME(DATETIME("2017-01-01 15:15")) → DATE("2017-01-01")

TRUNCATE_TO_HOURS

TRUNCATE_TO_HOURS(DateTime)

Removes the minutes, seconds and milliseconds from the date/time.

Parameter	Type	Description
`DateTime`	`Date/Each`	Date/time value.
→ Result	`Date`	Date/time, set to the last even hour in the current user's time zone.

Example:

TRUNCATE_TO_HOURS(DATETIME("2017-01-01 15:15")) → DATE("2017-01-01 15:00")

TRUNCATE_TO_MINUTES

TRUNCATE_TO_MINUTES(DateTime)

Removes the seconds and milliseconds from the date/time.

Parameter	Type	Description
`DateTime`	`Date/Each`	Date/time value.
→ Result	`Date`	Date/time, set to the last even minute.

Example:

TRUNCATE_TO_MINUTES(DATETIME("2017-01-01 15:15:15")) → DATE("2017-01-01 15:15:00")

TRUNCATE_TO_SECONDS

TRUNCATE_TO_SECONDS(DateTime)

Removes the milliseconds from the date/time.

Parameter	Type	Description
`DateTime`	`Date/Each`	Date/time value.
→ Result	`Date`	Date/time, with milliseconds removed.

Example:

TRUNCATE_TO_SECONDS(NOW())

WEEKDAY

WEEKDAY(DateTime)

Returns the number of the day of the week.

Parameter	Type	Description
`DateTime`	`Date/Each`	Date or date/time value.
→ Result	`Number`	Numeric value for the day of the week. Follows ISO-8601 standard (1 – Monday, 7 – Sunday).

Example:

WEEKDAY(DATE("2017-04-23")) → 7

WEEKNUM

WEEKNUM(DateTime)

Returns the number of the week of the year.

Parameter	Type	Description
`DateTime`	`Date/Each`	Date or date/time value.
→ Result	`Number`	Numeric value for the week of the year. Follows ISO-8601 standard.

Example:

WEEKNUM(DATE("2017-01-02")) → 2

YEAR

YEAR(DateTime)

Returns the year in a date or date/time value as a number.

Parameter	Type	Description
`DateTime`	`Date/Each`	Date or date/time value.
→ Result	`Integer`	Year.

Example:

YEAR(DATE("2017-04-23")) → 2017

YEARS_BETWEEN

YEARS_BETWEEN(DateTime1, DateTime2)

Calculates the number of years between two date or date/time values.

Parameter	Type	Description
`DateTime1`	`Date`	First date or date/time value to compare.
`DateTime2`	`Date`	Second date or date/time value to compare.
→ Result	`Number`	The number of full years between the date/time values. Returns a negative value if `DateTime2` occurs earlier than `DateTime1`.

Examples:

YEARS_BETWEEN(DATE("2017-01-01"), DATE("2018-01-01")) → 1
YEARS_BETWEEN(DATE("1703-05-27"), DATE("2017-04-23")) → 313
YEARS_BETWEEN(DATE("2017-06-01"), DATE("2018-05-31")) → 0

Duration Functions

Duration is represented as a number of milliseconds. To create a value or make sense of a value, you need one of the following functions to convert a string to a duration and vice versa.

You can add duration to a date or date/time value and treat the result as a new date/time, but only if it's a calendar duration. This does not work with work duration.

To understand why, let's consider you wanted to add 16 hours at a date or date/time. The result should be slightly less than a day later. However, when using work duration, adding 16 hours will result in a date at least 2 days later (maybe more, if it crosses a weekend), based on Jira's default 8h/day 5-day work week.

CALENDAR_DAYS

CALENDAR_DAYS(Duration)

Returns a number of calendar days represented by the duration value as a decimal number.

Parameter	Type	Description
`Duration`	`Text/Each`	Value expressed in Jira Duration format (1d 3h 30m).
→ Result	`Number`	Number of calendar days. May return a fractional number of days.

Examples:

CALENDAR_DAYS(DURATION("10d")) → 10
CALENDAR_DAYS(DURATION("12h")) → 0.5

CALENDAR_HOURS

CALENDAR_HOURS(Duration)

Returns a number of hours represented by the duration value as a decimal number.

Parameter	Type	Description
`Duration`	`Text/Each`	Value expressed in Jira Duration format (1d 3h 30m).
→ Result	`Number`	Number of hours. May return a fractional number of hours.

Examples:

CALENDAR_HOURS(DURATION("10d")) → 240
CALENDAR_HOURS(DURATION("12h 45m")) → 12.75

CALENDAR_MINUTES

CALENDAR_MINUTES(Duration)

Returns a number of minutes represented by the duration value as a decimal number.

Parameter	Type	Description
`Duration`	`Text/Each`	Value expressed in Jira Duration format (1d 3h 30m).
→ Result	`Number`	Number of minutes. May return a fractional number of minutes.

Example:

CALENDAR_MINUTES(DURATION("3h")) → 180

CALENDAR_SECONDS

CALENDAR_SECONDS(Duration)

Returns a number of seconds represented by the duration value as a decimal number.

Parameter	Type	Description
`Duration`	`Text/Each`	Value expressed in Jira Duration format (1d 3h 30m).
→ Result	`Number`	Number of calendar seconds. May return a fractional number of seconds.

Example:

CALENDAR_SECONDS(DURATION("1h")) → 3600

CALENDAR_DURATION

CALENDAR_DURATION(Start, Finish, Calendar)

Returns the time spent between start and finish according to the chosen calendar. Be aware that calendars supplied by Structure do not depend on the user's time zone (system time zone is used), but some functions (like DATETIME) do. Other calendars may or may not depend on the user's time zone depending on the implementation.

Parameter	Type	Description
`Start`	`Date`	Date or date/time value to start counting calendar time.
`Finish`	`Date`	Date or date/time value to finish counting calendar time.
`Calendar (Optional)`	`Text`	Calendar name in current user locale. If omitted, the default (24/7) calendar is used, and the result would be equal to (Finish - Start).
→ Result	`Number`	Duration in milliseconds.

Example:

CALENDAR_DURATION(DATE("18/Jul/2022"), DATE("19/Jul/2022"), "Standard work calendar 8/5") → 28800000 (8h in milliseconds)

DURATION

DURATION(Text)

Converts a text representation of a calendar duration to a number.

This function ignores Jira's settings for work time, so DURATION("1w") = DURATION("7d") and DURATION("1d") = DURATION("24h").To consider work time, use JIRA_DURATION.

Parameter	Type	Description
`Duration`	`Text/Each`	Value expressed in Jira Duration format (1d 3h 30m).
→ Result	`Number`	Duration in milliseconds.

Examples:

DURATION("1w 2d 3h 4m")
DURATION("3d")

FORMAT_DURATION

FORMAT_DURATION(Duration)

Converts duration value to the Jira format with numbers followed by symbols specifying the time unit.

Parameter	Type	Description
`Duration`	`Number/Each`	Duration value in number format.
→ Result	Text	Duration value converted to Jira Duration format (1d 3h 30m).

Example:

FORMAT_DURATION(DURATION("1w 1d")) → "1w 1d"

JIRA_DAYS

JIRA_DAYS(Duration)

Returns a number of work days in the specified duration according to Jira's settings.

Parameter	Type	Description
`Duration`	`Text/Each`	Value expressed in Jira Duration format (1d 3h 30m).
→ Result	`Number`	Number of work days. (By default, one day is 8 hours.) May return a fractional number.

Example:

JIRA_DAYS(DURATION("24h")) → 3
JIRA_DAYS(DURATION("12h")) → 1.5

JIRA_DURATION

JIRA_DURATION(Text)

Converts a text representation of a Jira work duration to a number.

The specified time is work time, according to Jira's settings. With the default Jira settings, JIRA_DURATION("1w") = JIRA_DURATION("5d") and JIRA_DURATION("1d") = JIRA_DURATION("8h"). To use calendar time, use DURATION.

Parameter	Type	Description
`Duration`	`Text/Each`	Value expressed in Jira Duration format (1d 3h 30m).
→ Result	`Number`	Duration in milliseconds.

Examples:

JIRA_DURATION("1w 2d 3h 4m")
JIRA_DURATION("3d")

JIRA_WEEKS

JIRA_WEEKS(Duration)

Returns a number of work weeks in the specified duration according to Jira's settings.

Parameter	Type	Description
`Duration`	`Text/Each`	Value expressed in Jira Duration format (1d 3h 30m).
→ Result	`Number`	Number of work days. (By default, one week is 5 work days.) May return a fractional number.

Example:

JIRA_WEEKS(JIRA_DURATION("10d")) → 2
JIRA_WEEKS(DURATION("5d")) → 3

Numeric Functions

ABS

ABS(Value)

Calculates the absolute value of a number.

Parameter	Type	Description
`Value`	`Number/Each`	Value to check.
→ Result	`Number`	Absolute value of `Value`.

Examples:

ABS(5) → 5
ABS(-4) → 4

CEILING

CEILING(Value; N)

Rounds value up to the N^th decimal place.

Parameter	Type	Description
`Value`	`Number/Each`	Number to round.
`N` (Optional)	`Integer`	How many decimal places to round up to. Negative numbers round up to tens, hundreds, etc. Default value: `0` (round to an integer).
→ Result	`Number`	`Value` rounded up to the `N`^th place.

Examples:

CEILING(1.678) → 2
CEILING(12.34; 1) → 12.4
CEILING(12.34; -1) → 20
CEILING(-3.14) → -3

FLOOR

FLOOR(Value; N)

Rounds value down to the N^th decimal place.

Parameter	Type	Description
`Value`	`Number/Each`	Number to round.
`N` (Optional)	`Integer`	How many decimal places to round down to. Negative numbers round down to tens, hundreds, etc. Default value: `0` (round to an integer).
→ Result	`Number`	`Value` rounded down to the `N`^th place.

Examples:

FLOOR(1.678) → 1
FLOOR(12.34; 1) → 12.3
FLOOR(17.34; -1) → 10
FLOOR(-3.14) → -4

LN

LN(x)

Returns the logarithm for 'x' with base of e.

Parameter	Type	Description
`x`	`Number`	Value to check
→ Result	`Number`	logarithm for x with a base of e

LOG

LOG(x), LOG(x,b)

LOG(x) returns the logarithm for 'x' with base of 10. LOG(x,b) returns the logarithm for 'x' with base of 'b'.

Parameter

Type

Description

x

Number

Value to check

b

(Optional)

Number

Base value.

If omitted or an empty value is entered, uses base 10.

→ Result

Number

logarithm for x

Example - the following will categorize projects based on the logarithm of all the story points within them:

WITH projectSize = LOG(SUM{storypoints}):
IF(
projectSize < 1; "Small";
projectSize < 2; "Medium";
projectSize >= 2; "Large"
)

LOG10

LOG10(x)

Returns the logarithm for 'x' with base of 10. Same as LOG with only an x variable.

Parameter	Type	Description
`x`	`Number`	Value to check
→ Result	`Number`	logarithm for x

MOD

MOD(A; N)

Returns the remainder from dividing A by N.

Parameter	Type	Description
`A`	`Integer/Each`	The dividend, must be an integer.
`N`	`Integer`	The divisor, must be an integer.
→ Result	`Number`	The remainder from dividing `A` by `N`.

Example:

MOD(17; 5) → 2

MUL

MUL(Value1, Value2,...)

MUL(A)

Short for "multiply" - produces the product of all values passed as arguments. When used with an array, produces the product of all values in the array.

Parameter

Type

Description

Value1, Value2, ..., ValueN

OR

A

Number

Array

Series of number values.

Array containing numeric elements.

→ Result

Integer

Product of all numeric elements.

Undefined values are ignored. Non-numeric values result in an error.

Example:

MUL(2, 3, 5) → 30
MUL(ARRAY(1, 2, 3, 4)) → 24

NUMBER

NUMBER(Value, DefaultOpt)

Converts value to number. This function is rarely needed, because conversion to number happens automatically when needed.

Parameter	Type	Description
`Value`	`Any`	Value to convert
`Default` (Optional)	`Number`	Optional. If provided and `Value` cannot be converted to a number, this function returns the `Default` rather than an error.
→ Result	`Number`	`Value` converted to a number.

Example:

NUMBER("1.234") → 1.234

POW

POW(B; E)

Produces B to the power of E (B^E). Both values can be fractional.

Parameter	Type	Description
`B`	`Number/Each`	Base
`E`	`Number`	Exponent
→ Result	`Number`	B to the power of E (B^E)

Example:

POW(3; 3) → 27
POW(27; 1/3) → 3

ROUND

ROUND(Value, N)

Rounds value to the N^th decimal place.

Parameter	Type	Description
`Value`	`Number/Each`	A number to round.
`N` (Optional)	`Integer`	How many decimal places to round to. Negative numbers round to the nearest tens, hundreds, etc. Default value: `0` (round to an integer).
→ Result	`Number`	`Value` rounded to the `N`^th place.

Examples:

ROUND(1.678) → 2
ROUND(12.34, 1) → 12.3
ROUND(12.34, -1) → 10
ROUND(ARRAY(1.1, 2.6)) → ARRAY(1, 3)

SIGN

SIGN(Value)

Returns the sign of the Value (1 for positive, -1 for negative).

Parameter	Type	Description
`Value`	`Number/Each`	Value to check.
→ Result	`Number`	Returns `1` if `Value` is positive, `-1` if `Value` is negative.

Examples:

SIGN(123) → 1
SIGN(0) → 0
SIGN(-123) → -1

SQR

SQR(Value)

Returns the passed numerical value, squared.

Parameter	Type	Description
`Value`	`Number/Each`	Numerical value.
→ Result	`Number`	`Value`²

Example:

SQR(5) → 25

SQRT

SQRT(Value)

Returns the square root of the passed numerical value.

Parameter	Type	Description
`Value`	`Number/Each`	Numerical value.
→ Result	`Number`	√`Value`

Example:

SQRT(25) → 5

SUM

SUM(Number1, Number2, ...)

SUM(A)

Produces the total of all numeric values passed as arguments. When used with an array, produces a total of all numeric elements in the array.

Parameter

Type

Description

Number1, Number2, ..., NumberN)

OR

A

Number

Array

Array containing numeric elements.

→ Result

Integer

Sum of all numeric elements.

Undefined values are ignored. Non-numeric values (that cannot be converted to numbers) result in an error.

Example:

SUM(1; 3; 5) → 9
SUM(ARRAY(1, 2, 3, 4)) → 10

Statistical Functions

AVERAGE

AVERAGE(Number1, Number2, ...)

AVERAGE(A)

Calculates the average of numbers in an array, or a series of numbers. When used with multiple arguments, finds the average of the numbers passed as parameters. When used with an array, finds the average of the numbers in the array.

Parameter Type Description

Number1, Number2, ..., NumberN

OR

A

Number

Array

Series of number values.

Array of numbers values to be considered.

→ Result

Number

Average of the numbers. If the array is empty, or all element are undefined, returns undefined.

Undefined values are ignored. Values that cannot be converted to numbers produce an error.

Examples:

AVERAGE(1; 3; 5) → 3
AVERAGE(numberArray)

MAX

MAX(Number1, Number2, ...)

MAX(A)

When used with multiple arguments, finds the largest value among the numbers passed as parameters. When used with an array, finds the maximum number in the array.

Parameter Type Description

Number1, Number2, ..., NumberN

OR

A

Number

Array

Series of number values.

Array of numbers values to be considered.

→ Result

Number

Largest value. If the array is empty, or all element are undefined, returns undefined.

Undefined values are ignored. Values that cannot be converted to numbers produce an error.

Examples:

MAX(due_date; updated_date)
MAX(0; -10; undefined; 10) → 10
MAX(ARRAY(1,6,3)) → 6

MEDIAN

MEDIAN(A)

Calculates the median of the numbers in an array. Equal to PERCENTILE(A, 0.5).

Parameter	Type	Description
`A`	`Array`	Array of numbers values to be considered.
→ Result	`Number`	Median value.

Example:

MEDIAN(ARRAY(1,2,5,7,8)) → 5

MIN

MIN(Number1, Number2, ...)

MIN(A)

When used with multiple arguments, find the smallest value among the numbers passed as parameters. When used with an array, finds the minimum number in the array.

Parameter Type Description

Number1, Number2, ..., NumberN

OR

A

Number

Array

Series of number values.

Array of numbers values to be considered.

→ Result

Number

Smallest value. If the array is empty, or all element are undefined, returns undefined.

Undefined values are ignored. Values that cannot be converted to numbers produce an error.

Examples:

MIN(0; -10; undefined; 10) → -10
MAX(ARRAY(1,6,3)) → 1

PERCENTILE

PERCENTILE(A, N)

Calculates N percentile of the values in the given array.

Parameter	Type	Description
`A`	`Array`	Array of values to be considered.
`N`	`Number`	Value between 0.0 and 1.0. Any other value will produce an error.
→ Result	Number	Resulting value.

Undefined elements are ignored. Non-numeric values that cannot be converted to a number will result in an error.

If A contains only numbers, PERCENTILE(A, 0) is equal to A.MIN() and PERCENTILE(A, 1) is equal to A.MAX().

Example:

PERCENTILE(ARRAY(1,2,3,4,5), 0.25) → 2

QUARTILE

QUARTILE(A, N)

Calculates the quartile of the numbers in an array, or a series of numbers. Equal to PERCENTILE(A, N*0.25).

Parameter Type Description

A

Array

Array of values to be considered.

N

INT

Interger value between 0 and 4.

0 = value at the 0 percentile

1 = value at the 25th percentile

2 = value at the 50th percentile

3 = value at the 75th percentile

4 = value at the 100th percentile

→ Result

Number

Quartile value.

Example:

QUARTILE(ARRAY(1,2,3,4,5), 3) → 4

STDEV

STDEV(A)

Calculates standard deviation, based on a sample population. For the entire population, use STDEVP.

Parameter	Type	Description
`A`	`Array`	Array of numbers values to be considered.
→ Result	`Number`	Standard deviation.

Example:

STDEV(ARRAY(1,2,3)) -> 1

STDEVP

STDEVP(A)

Calculates standard deviation, based on the entire population. For a sample of the population, use STDEV.

Parameter	Type	Description
`A`	`Array`	Array of numbers values to be considered.
→ Result	`Number`	Standard deviation.

Example:

STDEVP(ARRAY(1,2,3)) -> 0.8165

UMAX

UMAX(Value1, Value2, ...)

UMAX(A)

Same as MAX, but does a universal comparison, accepting any type of values, including items and arrays. When used with multiple arguments, finds the largest value passed as parameters. When used with an array, finds the maximum value in the array.

Parameter Type Description

Value1, Value2, ..., ValueN

OR

A

Any

Array

Series of values.

Array of values to be considered.

→ Result

Any

Largest value. If the array is empty, or all element are undefined, returns undefined.

Undefined values are ignored.

Examples:

UMAX("aardvark", "zebra", "lion") → "zebra"
UMAX(ArrayofAnyTypes)

UMAX_BY

UMAX_BY(A, $)

Returns the maximum value by comparing all values in the array using the values calculated by calling $ for each element. Applies universal comparison to the value (UMAX).

Parameter	Type	Description
`A`	`Array`	Array of values to be considered.
`$`	`User Function`
→ Result	`Any`	Maximum value. If the array is empty, or all element are undefined, returns undefined.

Undefined values are ignored.

Example:

fixVersions.UMAX_BY($.releaseDate) → returns the latest fix version

UMIN

UMIN(Number1, Number2, ...)

UMIN(A)

Same as MIN, but does a universal comparison, accepting any type of values, including entities. When used with multiple arguments, find the smallest value passed as parameters. When used with an array, finds the minimum value in the array.

Parameter Type Description

Number1, Number2, ..., NumberN

OR

A

Any

Array

Series of values.

Array of values to be considered.

→ Result

Any

Smallest value. If the array is empty, or all element are undefined, returns undefined.

Undefined values are ignored.

Examples:

UMIN("aardvark", "zebra", "lion") → "aardvark"
UMIN(ArrayofAnyTypes)

UMIN_BY

UMIN_BY(A, $)

Returns the minimum value by comparing all values in the array using the values calculated by calling $ for each element. Applies universal comparison to the value (UMIN).

Parameter	Type	Description
`A`	`Array`	Array of values to be considered.
`$`	`User Function`
→ Result	`Any`	Minimum value. If the array is empty, or all element are undefined, returns undefined.

Undefined values are ignored.

Example:

fixVersions.UMIN_BY($.releaseDate) → returns the earliest fix version

Text Functions

Text functions let you manipulate character strings.

If a function expects a string but encounters a number, it converts it to a string using mathematical notation ("." decimal separator, no thousands separator).

CONCAT

CONCAT(Value; ...)

Concatenates (merges) text values into a single text.

Parameter	Type	Description
`Value1`, `Value2`, ..., `ValueN`	`Text/Joined`	Text string(s). Accepts any number of arguments. Ignores falsy values.
→ Result	`Text`	A single text containing all the values combined.

Example:

CONCAT(Reporter; ' => '; Assignee)

EXACT

EXACT(A; B)

Checks if text value A is exactly the same as text value B.

Parameter	Type	Description
`A`	`Text/Joined`	Text value.
`B`	`Text/Joined`	Text value.
→ Result	`Boolean`	Returns true (`1`) if values are exactly the same. Otherwise, false (`0`).

This comparison is case sensitive, which is different from comparing A with B using an equals sign or text matching. Undefined values will be equal to each other and to empty strings.

Examples:

EXACT("Fox"; "fox") → 0
EXACT("Fox"; "Fox") → 1
EXACT(""; undefined) → 1

LEFT

LEFT(Value; N)

Returns up to N leftmost characters from a text value.

Parameter	Type	Description
`Value`	`Text/Joined`	Text to get characters from.
`N`	`Integer`	The number of characters to get.
→ Result	`Text`	The first `N` characters, starting from the left. If `Value` contains fewer characters, all of them are returned. If the value is less than zero, an empty text is returned.

Example:

LEFT("abc"; 2) → "ab"

LEN

LEN(Value)

Returns the number of characters in a text value.

Parameter	Type	Description
`Value`	`Text/Joined`	Text to count. If the value is not text, it is converted to text first.
→ Result	`Integer`	The number of characters in `Value`.

Example:

LEN("abc") → 3

LOWER

LOWER(Value)

Converts text to lowercase. The locale of the current user is applied.

Parameter	Type	Description
`Value`	`Text/Each`	Text to convert.
→ Result	`Text`	`Value` in all lowercase.

Example:

LOWER("HAM") → "ham"

MATCH

MATCH(Value; Pattern)

Checks if the Value matches the Pattern.

Parameter	Type	Description
`Value`	`Text/Joined`	Value to check.
`Pattern`	`Text/Joined`	Pattern to check against. Can be an exact value, a wildcard expression or a regular expression. See Expr Pattern Matching for details.
→ Result	`Boolean`	Returns `true` (1) or `false` (0).

Examples:

MATCH("Apples"; "Oranges") → 0
MATCH(" Blocker "; "blocker") → 1
MATCH("Hamster"; "ham*") → 1
MATCH("The Flight of the Bumblebee"; "/.light.*beer?/") → 1

MID

MID(Value; Index; Count)

Retrieves a part of the text.

Parameter	Type	Description
`Value`	`Text/Joined`	The text value to get a substring from.
`Index`	`Integer`	The starting index of the part to retrieve, 1-based (first character is at index 1).
`Count`	`Integer`	The number of characters to retrieve.
→ Result	`Text`	Text containing `Count` number of characters, starting from `Index`.

Example:

MID("A quick brown fox"; 3; 5) → "quick"

REPEAT

REPEAT(Value; N)

Produces a text that is a repetition of the string value N times.

Parameter	Type	Description
`Value`	`Text/Joined`	Text to repeat.
`N`	`Integer`	The number of repetitions.
→ Result	`Text`	The repeated text.

Examples:

REPEAT("ha"; 3) → "hahaha"
REPEAT(123, 3) → "123123123"

REPLACE

REPLACE(Value; Pattern; Replacement)

Replaces all occurrences of Pattern with Replacement and returns the new text.

Parameter	Type	Description
`Value`	`Text/Joined`	The text to manipulate.
`Pattern`	`Text`	Pattern to find. Can be an exact value, a wildcard expression or a regular expression. See Expr Pattern Matching for details.
`Replacement` (Optional)	`Text`	An optional text to use instead of the matched parts. If omitted, the matched parts are removed.
→ Result	`Text`	`Value` with replacements.

Examples:

REPLACE("I like cats"; "CAT"; "DOG") → "I like DOGs"
REPLACE("Can you read this?"; "/[aeuio]/") → "Cn y rd ths?"

REPLACE_AT

REPLACE_AT(Value; Index; Count; Replacement)

Replaces a specific part of the Value with Replacement text and returns the value.

Parameter	Type	Description
`Value`	`Text/Joined`	The text to manipulate.
`Index`	`Integer`	The starting index of the part to replace, 1-based (first character is 1, second is 2, etc.)
`Count`	`Integer`	The number of characters to replace. When Count is 0, the Replacement string gets inserted at the Index position.
`Replacement` (Optional)	`Text`	An optional text to use instead of the replaced part. If omitted, the part will be deleted.
→ Result	`Text`	`Value` with replacements.

When the values of Index and Count are out of range, they are brought to the nearest sensible value.

Examples:

REPLACE_AT("A"; 1; 1; "B") → "B"
REPLACE_AT("What does the fox say?"; 6; 4; "did") → "What did the fox say?"
REPLACE_AT("A step for mankind"; 3; 0; "small ") → "A small step for mankind"
REPLACE_AT("A step for mankind"; 7; 1000) → "A step"

RIGHT

RIGHT(Value; N)

Returns up to N rightmost characters from a string value.

Parameter	Type	Description
`Value`	`Text/Joined`	Text to get characters from.
`N`	`Integer`	The number of characters to get.
→ Result	`Text`	The first `N` characters, starting from the right. If `Value` contains fewer characters, all of them are returned.

Example:

RIGHT("abc"; 2) → "bc"

SEARCH

SEARCH(Pattern; Value; Index)

Finds the first occurrence of a pattern in the value.

Parameter	Type	Description
`Pattern`	`Text`	The text or pattern to look for. Can be an exact value, a wildcard expression or a regular expression. See Expr Pattern Matching for details.
`Value`	`Text/Joined`	The text to search in.
`Index` (Optional)	`Integer`	Optional parameter that provides an index to start searching at.
→ Result	`Integer`	Returns the index of the matched part (1-based), or `undefined` if not found.

Examples:

SEARCH("ham"; "The Ham is for the Hamster"; 6) → 20
SEARCH("Jedi*"; "Return of the Jedi") → 15
SEARCH("/^Jedi/"; "Not the Jedi you're looking for") → undefined

SPLIT

SPLIT(Value; Separator)

Produces an array from the value by splitting it using a separator.

Parameter	Type	Description
`Value`	`Text/Joined`	The text to split.
`Separator`	`Text`	The text or pattern to split by. Can be an exact value, a wildcard expression or a regular expression. See Expr Pattern Matching for details.
→ Result	`Array`	Returns an array which contains the split texts.

Examples:

SPLIT("One, Two, Three", ",") → ARRAY("One", "Two", "Three")
SPLIT("A and B or C", "/ and | or /") → ARRAY("A", "B", "C")

SUBSTRING

SUBSTRING(Value; From; To)

Returns a substring, indicated by a starting index and ending index. Note that the indexes are 0-based, unlike in some other functions.

Parameter	Type	Description
`Value`	`Text/Joined`	The text to take the part from.
`From`	`Integer`	Starting index, inclusive, 0 means the first character, `LEN(Value)-1` means the last character.
`To` (Optional)	`Integer`	Optional ending index, exclusive - the character at this index will not be included. If omitted, the substring will include all characters up to the end of the `Value`.
→ Result	`Text`	Returns the portion of the text contained between `From` and `To`.

If To value is greater than the text length, all characters will be included. If To is less than From, an empty text is returned.

Examples:

SUBSTRING("Batman"; 0; 3) → "Bat"
SUBSTRING("Batman"; 3) → "man"

TEXT

TEXT(Value)

Converts value to text. This function is rarely needed, because conversion to text happens automatically when needed.

Parameter	Type	Description
`Value`	`Any (If text, Text/Joined)`	Value to convert
→ Result	`Text`	`Value` converted to text.

Example:

TEXT(1.234) → "1.234"

TRIM

TRIM(Value)

Removes leading and trailing whitespace from the text.

Parameter	Type	Description
`Value`	`Text/Each`	The text to manipulate.
→ Result	`Text`	Returns `Value` without leading/trailing whitespace.

Example:

TRIM(" Batman ") → "Batman"

UPPER

UPPER(Value)

Converts the string to uppercase. The locale of the current user is applied.

Parameter	Type	Description
`Value`	`Text/Each`	The text to manipulate.
→ Result	`Text`	Returns `Value` in all uppercase.

Example:

UPPER("ham") → "HAM"

User-related Functions

USER_NAME

USER_NAME(UserKey)

Parameter	Type	Description
`UserKey`	`Item/Each`	UserKey value.
→ Result	`Text`	Username.

Returns the username specified by the user key.

USER_DISPLAY_NAME

USER_DISPLAY_NAME(UserKey)

Returns the user's full name, defined by the user key.

Parameter	Type	Description
`UserKey`	`Item/Each`	UserKey value.
→ Result	`Text`	User's full name.

USER_EMAIL

USER_EMAIL(UserKey)

Returns the user's email, defined by the user key.

Parameter	Type	Description
`UserKey`	`Item/Each`	UserKey value.
→ Result	`Text`	User's email address.

USER_IS_ACTIVE

USER_IS_ACTIVE(UserKey)

Returns 1 for active user, 0 for inactive user.

Parameter	Type	Description
`UserKey`	`Item/Each`	UserKey value.
→ Result	`Number`	1 for active user, 0 for inactive user.

Miscellaneous Functions

ACCESS

ACCESS(Item, PropertyName)

Tries to access a property/field of the item.

Parameter	Type	Description
`Item`	`Item`	Item containing the property or a key-value map.
`PropertyName`	`Text`	Property or field being accessed.
→ Result	`Any`	Value in `PropertyName`. Undefined if the `Item` is not an item or key-value map, or it does not have this property.

Example:

ACCESS(epic, "Story Points")

HISTORICAL_VALUE

HISTORICAL_VALUE(Item, Field, Date)

Show value of the field at specified date

Parameter	Type	Description
`Item`	`Item`	Issue item; use "this" for current row.
`Field`	`Text`	Field being accessed
`Date`	`Date/Time`	Date or date/time value; use DATETIME function to convert into it: #DATETIME
`→ Result`	`Any`	Field value at specified date (current value will be used for future dates)

Example:

HISTORICAL_VALUE(this, "cost", DATETIME("15/May/18 6:24 PM")) → 5.55
HISTORICAL_VALUE(this, "cost", NOW()) → 5.55

ME

ME()

Returns the user item of the current user.

Example:

IF(ME() = "admin"; "You're admin!")

URL_ENCODE

URL_ENCODE(Value)

Translates a text into application/x-www-form-urlencoded format.

Parameter	Type	Description
`Value`	`Text`	Value to convert
→ Result	`Text`	`Value` converted to application/x-www-form-urlencoded format.

Example:

URL_ENCODE("http://www.test.com") → "http%3A%2F%2Fwww.test.com"

URL_DECODE

URL_DECODE(Value)

Decodes an application/x-www-form-urlencoded text.

Parameter	Type	Description
`Value`	`Text`	Value to convert
→ Result	`Text`	Decoded `Value`.

Example:

URL_DECODE("http%3A%2F%2Fwww.test.com") → "http://www.test.com/"

Expr Function Reference

Parameter Types

Undefined Values

Arrays

Array Functions

[data-colorid=gz37mzkq1c]{color:#333333} html[data-color-mode=dark] [data-colorid=gz37mzkq1c]{color:#cccccc} ALL

ANY

ARRAY

COMPACT

CONTAINS

CONTAINS_ALL

CONTAINS_ANY

FILTER

FIRST

FLATTEN

GET

GROUP

INDEX_OF

INDEXES

IS_ARRAY

IS_EMPTY

JOIN

LAST

LAST_INDEX_OF

MAP

MERGE_ARRAYS

NONE

RECURSIVE_FLATTEN

REDUCE

REVERSE

SEQUENCE

SIZE

SORT

SORT_BY

SUBARRAY

UNIQUE

WITHOUT

Conditional Functions

CASE

CHOOSE

DEFINED

DEFAULT

IF

IFERR

ISERR

Date and Time Functions

DATE

DATE_ADD

DATE_SET

DATE_SUBTRACT

DAY

DAYS_BETWEEN

DATETIME

END_OF_MONTH

FORMAT_DATETIME

HOUR

HOURS_BETWEEN

MAKE_DATE

MAKE_DATETIME

MINUTE

MONTH

MONTHS_BETWEEN

NOW

PARSE_DATETIME

SECOND

START_OF_MONTH

TODAY

TRUNCATE_TIME

TRUNCATE_TO_HOURS

TRUNCATE_TO_MINUTES

TRUNCATE_TO_SECONDS

WEEKDAY

WEEKNUM

YEAR

YEARS_BETWEEN

Duration Functions

CALENDAR_DAYS

CALENDAR_HOURS

CALENDAR_MINUTES

CALENDAR_SECONDS

ALL