Index

This documentation relates to version 4.1 of the Structure Plugin for JIRA and subsequent patch releases. Documentation for previous versions is available via Documentation Index.

Skip to end of metadata
Go to start of metadata

All standard Expr functions are listed on this page, grouped by category.

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

When a function expects a text or a numeric value as an argument and the actual type of value is different, the function will try to convert the value to the required type. If the conversion is not possible and the value is not empty (for example, it's impossible to convert "ABC" to a number), the result will be an error.

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 undefined value at its primary argument, the return value will also typically be undefined. 

Functions

Conditional Functions

CASE

CASE(Value; Match1; Result1; Match2; Result2; ...; DefaultOpt)

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

  • Value – value to check.
  • Match1Match2, ..., MatchN – 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.
  • Result1Result2, ..., ResultN – values to return from the function, each value corresponds to the preceding Match parameter.
  • DefaultOpt – optional default value, to be returned if none of the patterns match. If not specified, undefined is returned.

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

If the Value is undefined, the function immediately returns DefaultOpt result (or undefined if there's no default). There's no sense in using undefined as one of the matches.

Examples:

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

CHOOSE

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

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

  • Index – numeric index, with 1 corresponding to Value1, 2 corresponding to Value2 and so on.
  • Value1Value2, ..., ValueN – the values to pick from.

Examples:

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

DEFINED

DEFINED(Value)

Checks if the value is defined. Returns false (0) if Value is undefined and true (1) otherwise.

Example:

  • IF(DEFINED(Resolution); ...)

DEFAULT

DEFAULT(Value; DefaultValue)

Substitutes DefaultValue in case Value is undefined.

Examples:

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

IF

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

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

  • Condition1Condition2, ..., Condition3 – the conditions to check. The values are evaluated using "truthfulness check" – the first value that is "truthy", that is, not undefined, not zero and not an empty string, will define the returned value.
  • Result1Result2, ..., ResultN – results to be returned, each result corresponding to the preceding check.
  • DefaultOpt – optional default value to be returned if none of the conditions are true. If omitted, undefined is returned.

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 error value.

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; ErrorCodeOpt)

Checks if calculating value produced an error. Returns true (1) if there was an error. If ErrorCodeOpt is specified, returns true only if the error was of the specified error code.

  • Value – value to check.
  • ErrorCodeOpt – optional error code. See Expr Error Codes for a list.

Examples:

  • ISERR("Ham") → 0
  • ISERR(1 / 0) → 1
  • ISERR(1 / 0, 4) → 1

Numeric Functions

ABS

ABS(Value)

Calculates absolute value of a number.

Examples:

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

CEILING

CEILING(Value; N)

Rounds value up to the Nth decimal place.

  • Value – a number to round.
  • N – how many decimal places to round up to, can be negative to round up to tens, hundreds, etc. Default value: 0 (round to an integer).

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 Nth decimal place.

  • Value – a number to round.
  • N – how many decimal places to round down to, can be negative to round up to tens, hundreds, etc. Default value: 0 (round to an integer).

Examples:

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

MAX

MAX(Value; ...)

Selects numerically largest value from all values passed as arguments. Undefined values are skipped. Text values that cannot be converted to a number will also be skipped.

Examples:

  • MAX(Due_Date; Updated_Date)
  • MAX(0; -10; undefined; 10) → 10

MIN

MIN(Value; ...)

Selects numerically smallest value from all values passed as arguments. Undefined values are skipped. Text values that cannot be converted to a number will also be skipped.

Example:

  • MIN(0; -10; undefined; 10) → -10

MOD

MOD(A; N)

Returns remainder from dividing A by N.

  • A – the dividend, must be an integer.
  • N – the divisor, must be an integer.

Example:

  • MOD(17; 5) → 2

POW

POW(B; E)

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

  • B – base
  • E – exponent

Example:

  • POW(27; 1/3) → 3

ROUND

ROUND(Value; N)

Produces a rounded value up to the Nth decimal place.

  • Value – a number to round.
  • N – how many decimal places to round to, can be negative to round up to tens, hundreds, etc. Default value: 0 (round to an integer).

Examples:

  • ROUND(1.678) → 2
  • ROUND(12.34; 1) → 12.3
  • ROUND(12.34; -1) → 10

SIGN

SIGN(Value)

Returns sign of the Value.

Examples:

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

SQR

SQR(Value)

Returns the passed numerical value, squared.

Example:

  •  SQR(5) → 25

SQRT

SQRT(Value)

Returns the square root of the passed numerical value.

Example:

  • SQRT(25) → 5

Text Functions

Text function 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 strings together. Accepts any number of arguments. Ignores undefined values.

Example:

  • CONCAT(Reporter; ' => '; Assignee)

EXACT

EXACT(A; B)

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

This comparison is case sensitive, which is different from comparing A with B using 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 string value.

  • Value – string to get characters from.
  • N – the number of characters to get. If Value contains fewer characters, all of them are returned.

Example:

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

LEN

LEN(Value)

Returns the number of characters in a string value. If the value is not a string, it is converted to string first.

Example:

  • LEN("abc") → 3

LOWER

LOWER(Value)

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

Example:

  • LOWER("HAM") → "ham"

MATCH

MATCH(Value; Pattern)

Checks if the Value matches the Pattern. Returns true (1) or false (0).

  • Value – the value to check.
  • Pattern – pattern to check against. Can be an exact value, a wildcard expression or a regular expression. See Expr Pattern Matching for details.

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.

  • Value – the string value to get a substring from.
  • Index – the starting index of the part to retrieve, 1-based (first character is at index 1).
  • Count – the number of characters to retrieve.

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.

  • Value – a string value to repeat.
  • N – the number of repetitions.

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 string.

  • Value – the value to manipulate.
  • Pattern – pattern to find. Can be an exact value, a wildcard expression or a regular expression. See Expr Pattern Matching for details.
  • Replacement – an optional string to use instead of the matched parts. If omitted, the matched parts are removed.

Examples:

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

REPLACE_AT

REPLACE_AT(Value; Index; Count; Replacement)

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

  • Value – the string to manipulate.
  • Index – the starting index of the part to replace, 1-based (first character is 1, second is 2, etc.)
  • Count – the number of characters to replace. When Count is 0, the Replacement string gets inserted at the Index position.
  • Replacement – optional string to use instead of the replaced part. If omitted, the part will be deleted.

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 string value.

  • Value – string to get characters from.
  • N – the number of characters to get. If Value contains fewer characters, all of them are returned.

Example:

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

SEARCH(Pattern; Value; Index)

Finds the first occurrence of a pattern in the value. Returns the index of the matched part (1-based), or undefined if not found.

  • Pattern – the string or pattern to look for. Can be an exact value, a wildcard expression or a regular expression. See Expr Pattern Matching for details.
  • Value – the string to search in.
  • Index – optional parameter that provides an index to start searching at.

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

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.

  • Value – the string to take the part from.
  • From – starting index, inclusive, 0 means the first character, LEN(Value)-1 means the last character.
  • To – 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.

Examples:

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

TRIM

TRIM(Value)

Removes leading and trailing whitespace from the text.

Example:

  • TRIM(" Batman ") → "Batman"

UPPER

UPPER(Value)

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

Example:

  • UPPER("ham") → "HAM"

Date and Time Functions

Date/time functions operate with 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.

To display a result of a date/time calculation in a readable way, you need to either configure Formula Column 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; LocaleOpt; TimeZoneOpt)

Converts text representation of a date to number. The resulting timestamp will correspond to midnight of the specified date at the specified timezone.

  • Text – the text value to convert.
  • LocaleOpt – optional locale identifier, such as "fr_FR". If not specified, user's locale is used.
  • TimeZoneOpt – optional time zone identifier, such as "America/New_York". 

The conversion uses tries 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.

If conversion is unsuccessful, returns an error.

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 number of seconds, minutes, hours, days, months or years to the date or date/time value. 

  • DateTime – date or date/time value.
  • Number – the number of units of time to add.
  • Unit – a text value specifying the unit of time: "seconds", "minutes""hours""days""months""years"

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")

DATE_SET

DATE_SET(DateTime, Number, Unit)

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

  • DateTime – date or date/time value.
  • Number – the number to be set as the unit value in this date/time.
  • Unit – a text value specifying the unit of time: "second""minute""hour""day""month""year", "day_of_week".

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 number of seconds, minutes, hours, days, months or years from the date or date/time value. 

  • DateTime – date or date/time value.
  • Number – the number of units of time to subtract.
  • Unit – a text value specifying the unit of time: "seconds""minutes""hours""days""months""years"

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 give date or date/time value. The result is calculated using 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. Returns 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; LocaleOpt; TimeZoneOpt)

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

  • Text – the text value to convert.
  • LocaleOpt – optional locale identifier, such as "fr_FR". If not specified, user's locale is used.
  • TimeZoneOpt – optional time zone identifier, such as "America/New_York". 

The conversion uses tries 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.

If conversion is unsuccessful, returns an error.

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.

Example:

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

FORMAT_DATETIME

FORMAT_DATETIME(DateTime, Format, LocaleOpt, TimeZoneOpt)

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

  • DateTime – the value to convert.
  • Format – the format string. For all the options, please see Java documentation for SimpleDateFormat.
  • LocaleOpt – the optional locale identifier. If omitted or undefined, will use JIRA's system locale. (Not the user's locale!)
  • TimeZoneOpt – the optional time zone identifier. If omitted or undefined, will use JIRA's system time zone. (Not the user's time zone!)

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).

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. Returns 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.

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. The current user's time zone is used. The valid values for Hour are 0–23.

Example:

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

MINUTE

MINUTE(DateTime)

Returns the minute in the specified date/time value (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 (from 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. Returns 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.

Example:

  • NOW()

PARSE_DATETIME

PARSE_DATETIME(Text, Format, LocaleOpt, TimeZoneOpt)

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

  • Text – the value to convert.
  • Format – the format string. For all the options, please see Java documentation for SimpleDateFormat.
  • LocaleOpt – the optional locale identifier. If omitted or undefined, will use JIRA's system locale. (Not the user's locale!)
  • TimeZoneOpt – the optional time zone identifier. If omitted or undefined, will use JIRA's system time zone. (Not the user's time zone!)

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 second in the specified date/time value.

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 month. 

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.

Example:

  • TODAY()

TRUNCATE_TIME

TRUNCATE_TIME(DateTime)

Removes time value from the date/time, setting it 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 minutes, seconds and milliseconds from the date/time, setting it 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 seconds and milliseconds from the date/time, setting it 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 milliseconds from the date/time.

Example:

  • TRUNCATE_TO_SECONDS(NOW())

WEEKDAY

WEEKDAY(DateTime)

Returns the number of the day in the week, following ISO-8601 standard (1 – Monday, 7 – Sunday).

Example:

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

YEAR

YEAR(DateTime)

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

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. Returns 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 to make sense of a value, you need the following function to convert a string to a duration and vice versa.

Note that you can add duration to a date or date/time value and treat the result as a new date/time, but only if the duration is a "calendar duration", not "work duration". Work duration is when JIRA's settings, like the number of work hours per day, are used – the value "2d" really becomes 16 hours in JIRA's default setup, and if you add 16 hours to a date, you won't get a date 2 days later.

CALENDAR_DAYS

CALENDAR_DAYS(Duration)

Returns a number of calendar days represented by the duration value as a decimal number. May return 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. May return 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. May return 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. May return fractional number of seconds.

Example:

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

DURATION

DURATION(Text)

Converts a text representation of a calendar duration to a number. The format is provided by JIRA – the text may be several numbers, each number followed by a symbol to specify the time unit: w for weeks, d for days, h for hours and m for minutes.

Note that this function ignores JIRA's settings for work time, so DURATION("1w") = DURATION("7d") and DURATION("1d") = DURATION("24h").

Examples:

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

FORMAT_DURATION

FORMAT_DURATION(Duration)

Converts duration value to the JIRA's format with numbers followed by symbols specifying the time unit.

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. (By default, one day is 8 hours.) May return 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 format is provided by JIRA – the text may be several numbers, each number followed by a symbol to specify the time unit: w for weeks, d for days, h for hours and m for minutes.

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").

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. (By default, one week is 5 work days.) May return fractional number.

Example:

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

Miscellaneous Functions

ME

ME()

Returns the user key of the current user.

Example:

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

NUMBER

NUMBER(Value)

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

Example:

  • NUMBER("1.234") → 1.234

TEXT

TEXT(Value)

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

Example:

  • TEXT(1.234) → "1.234"