Formula reference

A formula column in the List calculates a value for each work item from its fields and other columns. This page lists everything you can write in a formula.

To open the formula editor, open any column header’s menu and select Insert formula column. To change an existing formula column, select Edit formula… in its header menu. Select Formula reference in the editor to come back to this page.

Quick start

[Story Points] * 2
IF([Status] = "Done", 0, [Remaining Estimate])
DAYS([Due date], TODAY())
NETWORKDAYS([Created], [Resolved])

Type [ to pick a field or column from a list. Type a letter to see matching functions.
While the caret is inside a function, the editor shows that function’s syntax. Select Learn more to jump to it on this page.
Preview shows the result for the first few work items before you save.
Choose how to Show result as, for example Hours or Date. See Result formats.

Formula columns are saved for you, per Task Hub. To share one, save a view that includes it.

Syntax

Part	How to write it	Example
Field or column	Name in square brackets. Names ignore case.	`[Story Points]`, `[due date]`
Field by ID	Field ID in square brackets. Use this when two fields share a name.	`[customfield_10016]`
Number	Digits, with `.` for decimals	`2`, `0.5`
Text	In double quotes. Write `""` for a quote inside the text.	`"Done"`, `"Say ""hi"""`
True or false	`TRUE`, `FALSE`	`IFS([Story Points] > 5, "Big", TRUE, "Small")`
Function	Name, then arguments in brackets, separated by `,` or `;`	`ROUND([Story Points] / 3, 1)`
Leading `=`	Optional, as in a spreadsheet	`=[Story Points] * 2`

You can refer to:

any Jira field, system or custom, including fields that aren’t shown in the table
calculated columns, such as [Time in current status]
other formula columns

If you rename a formula column, formulas that use it keep working, because they store its ID rather than its name. If you delete it, they show #REF!.

Operators

From highest to lowest precedence:

Operator	Meaning	Example	Result
`%`	Percent (divide by 100)	`50%`	`0.5`
`-` (before a value)	Negative	`-[Story Points]`
`^`	Power	`2 ^ 3`	`8`
`*` `/`	Multiply, divide	`[Original Estimate] / 8`	days of 8 hours
`+` `-`	Add, subtract	`[Due date] + 7`	a week later
`&`	Join text	`[Key] & " – " & [Summary]`	`DEMO-7 – Pagination redesign`
`=` `<>` `<` `>` `<=` `>=`	Compare	`[Priority] = "High"`	`TRUE` or `FALSE`

Use brackets to change the order: ([Story Points] + 1) * 2.

Comparisons follow spreadsheet rules:

Text comparisons ignore case: [Status] = "done" matches Done.
An empty value equals "", 0 and FALSE.
A date compared with date text compares as dates: [Due date] < "2026-12-31".

Values and types

Every value is a number, text, a date, TRUE/FALSE, or empty. Fields arrive as follows:

Field	Value in a formula
Number fields (Story Points, custom numbers)	Number
Time tracking (Original Estimate, Remaining Estimate, Time Spent)	Number of hours
Date and date-time fields (Created, Updated, Due date, Resolved, custom dates)	Date
Status, Priority, Resolution, Work item type, select lists	Text: the option’s name
People (Assignee, Reporter)	Text: the person’s display name
Multi-value fields (Labels, Components, Fix versions)	Text. Use `CONTAINS` to test for one value.
Calculated durations (Time in current status, Time with current assignee…)	Number of hours
Calculated percentages (Sub-task progress)	Number from `0` to `1`
Empty field	Empty

Working with dates

Date − date gives days, including fractions of a day from the time part. Use DAYS(end, start) for whole days.
Date ± number moves the date by that many days: [Created] + 14.
If either date is empty, date − date is empty instead of an error. For example, [Resolved] - [Created] is empty for unresolved work items.
Write a fixed date as text, "2026-12-31", or as DATE(2026, 12, 31).
NETWORKDAYS and WORKDAY count Monday to Friday. Holidays aren’t excluded.

Numbers in arithmetic

In + - * / ^, an empty value counts as 0, TRUE as 1, and text that is a plain number, such as "12.5", is read as a number. Other text gives #VALUE!.

Result formats

Choose Show result as in the editor:

Format	Shows	Use it for
Automatic	Picks a format from the result	Most formulas
Number	`12.5`	Plain numbers
Percent	`0.25` shows as `25%`	Ratios
Hours	`5h`	Results in hours, such as estimates
Duration from hours	`3d 4h`	Hours shown as days and hours
Days	`12d`	Results in days, such as date differences
Date	A date	Date results, such as `WORKDAY(...)`
Text	The text as is	Labels you build with `&`

The format also decides how the column sorts, filters and totals in the sum-up row.

Errors

When a formula can’t be calculated for a work item, its cell shows an error code. Hover over it, or look at Preview in the editor, to see the reason.

Error	Meaning	Typical fix
`#PARSE!`	The formula isn’t written correctly, or a function has the wrong number of arguments	Check brackets, quotes and commas
`#NAME?`	There’s no function with that name	Check the spelling against the function list
`#REF!`	A field, calculated column or formula column it uses no longer exists	Pick the field again with `[`
`#VALUE!`	A value has the wrong type, for example text where a number is needed, or two dates added	Convert with `VALUE` or `DATEVALUE`, or check the field
`#DIV/0!`	Division by zero	`IFERROR([A] / [B], 0)`
`#NUM!`	A number is out of range, or a function got an invalid option	Check the arguments
`#N/A`	`IFS` or `SWITCH` found no match	Add a default
`#CIRCREF!`	Formula columns use each other in a loop	Remove one of the references

The editor won’t save a formula with a syntax error, an unknown field, or a loop between formula columns. In the table, cells with errors sort last.

Math functions

SUM

SUM(value1, value2, …): adds the numbers. Empty values and text are skipped.

SUM([Time Spent], [Remaining Estimate])

AVERAGE

AVERAGE(value1, value2, …): the average of the numbers. Empty values are skipped.

MEDIAN

MEDIAN(value1, value2, …): the middle value of the numbers.

MIN

MIN(value1, value2, …): the smallest number, or the earliest date.

MIN([Due date], [Target end])

MAX

MAX(value1, value2, …): the largest number, or the latest date.

PRODUCT

PRODUCT(value1, value2, …): multiplies the numbers.

COUNT

COUNT(value1, value2, …): how many of the values are numbers or dates.

COUNTA

COUNTA(value1, value2, …): how many of the values aren’t empty.

COUNTA([Assignee], [Due date], [Story Points])

COUNTBLANK

COUNTBLANK(value1, value2, …): how many of the values are empty.

ROUND

ROUND(number, [digits]): rounds to the given number of decimal places, or to a whole number if you leave out digits.

ROUND([Time Spent] / 8, 1)

ROUNDUP

ROUNDUP(number, [digits]): rounds away from zero.

ROUNDDOWN

ROUNDDOWN(number, [digits]): rounds towards zero.

INT

INT(number): rounds down to a whole number.

ABS

ABS(number): the number without its sign.

ABS(DAYS([Due date], TODAY()))

MOD

MOD(number, divisor): the remainder after division.

POWER

POWER(number, power): the number raised to a power. Same as ^.

SQRT

SQRT(number): the square root.

CEILING

CEILING(number, [step]): rounds up to the next multiple of step, or of 1 if you leave it out.

CEILING([Original Estimate], 4)

FLOOR

FLOOR(number, [step]): rounds down to a multiple of step, or of 1 if you leave it out.

Logic functions

IF

IF(condition, value_if_true, [value_if_false]): one value when the condition holds, another when it doesn’t. Without value_if_false, the result is FALSE.

IF([Status] = "Done", 0, [Remaining Estimate])

IFS

IFS(condition1, value1, condition2, value2, …): the value for the first condition that holds. If none holds, the result is #N/A, so end with TRUE, default.

IFS([Story Points] >= 8, "Large", [Story Points] >= 3, "Medium", TRUE, "Small")

SWITCH

SWITCH(value, match1, result1, …, [default]): the result for the first match of the value. Matching ignores case.

SWITCH([Priority], "Highest", 1, "High", 2, "Medium", 3, 4)

IFERROR

IFERROR(value, value_if_error): the value, or the fallback if the value is an error.

IFERROR([Time Spent] / [Original Estimate], 0)

COALESCE

COALESCE(value1, value2, …): the first value that isn’t empty.

COALESCE([Due date], [Target end], [Created] + 30)

AND

AND(condition1, condition2, …): TRUE when every condition holds.

AND([Status] <> "Done", [Due date] < TODAY())

OR

OR(condition1, condition2, …): TRUE when any condition holds.

NOT

NOT(condition): turns TRUE into FALSE, and FALSE into TRUE.

ISBLANK

ISBLANK(value): TRUE when the value is empty.

IF(ISBLANK([Assignee]), "Needs owner", [Assignee])

ISNUMBER

ISNUMBER(value): TRUE when the value is a number.

ISTEXT

ISTEXT(value): TRUE when the value is text.

ISDATE

ISDATE(value): TRUE when the value is a date.

ISERROR

ISERROR(value): TRUE when the value is an error, such as #DIV/0!.

Text functions

CONCAT

CONCAT(text1, text2, …): joins the values into one text. Same as &.

CONCAT([Key], ": ", [Summary])

LEN

LEN(text): the number of characters.

LOWER

LOWER(text): the text in lower case.

UPPER

UPPER(text): the text in upper case.

TRIM

TRIM(text): removes spaces at the ends, and doubled spaces.

LEFT

LEFT(text, [count]): the first characters of the text, or the first one if you leave out count.

LEFT([Summary], 30)

RIGHT

RIGHT(text, [count]): the last characters of the text, or the last one if you leave out count.

MID

MID(text, start, count): characters from the middle of the text. start counts from 1.

SUBSTITUTE

SUBSTITUTE(text, old, new): replaces every occurrence of old with new.

CONTAINS

CONTAINS(text, search): TRUE when the text contains search, ignoring case.

IF(CONTAINS([Labels], "customer"), "Customer", "Internal")

VALUE

VALUE(text): reads a number from text.

Date functions

TODAY

TODAY(): today’s date.

DAYS([Due date], TODAY())

NOW

NOW(): the current date and time.

DATE

DATE(year, month, day): a date from its parts.

DATE(2026, 12, 31)

DATEVALUE

DATEVALUE("yyyy-mm-dd"): reads a date from text.

YEAR

YEAR(date): the year of the date.

MONTH

MONTH(date): the month of the date, 1 to 12.

DAY

DAY(date): the day of the month, 1 to 31.

WEEKDAY

WEEKDAY(date): the day of the week, from 1 (Sunday) to 7 (Saturday).

DAYS

DAYS(end_date, start_date): whole days from start to end. Negative if the end is earlier.

DAYS([Due date], TODAY())

Use Show result as › Days to show the result as 12d.

NETWORKDAYS

NETWORKDAYS(start_date, end_date): working days (Monday to Friday) between the dates, counting both.

NETWORKDAYS([Created], COALESCE([Resolved], TODAY()))

WORKDAY

WORKDAY(start_date, days): the date a number of working days (Monday to Friday) after the start.

WORKDAY([Created], 5)

EDATE

EDATE(date, months): the same day a number of months later, or earlier if months is negative.

DATEDIF

DATEDIF(start_date, end_date, unit): whole days, months or years between the dates. unit is "D", "M" or "Y". The start must be before the end, otherwise the result is #NUM!.

DATEDIF([Created], TODAY(), "M")

HOURS

HOURS(start, end): hours between two dates or date-times.

HOURS([Created], [Resolved])

Examples

Goal	Formula	Show result as
Days until due (negative when overdue)	`DAYS([Due date], TODAY())`	Days
Overdue flag	`AND([Status] <> "Done", [Due date] < TODAY())`	Automatic
Cycle time in working days	`NETWORKDAYS([Created], [Resolved])`	Number
Estimate used	`IFERROR([Time Spent] / [Original Estimate], 0)`	Percent
Remaining work, zero when done	`IF([Status] = "Done", 0, [Remaining Estimate])`	Hours
Size bucket	`IFS([Story Points] >= 8, "L", [Story Points] >= 3, "M", TRUE, "S")`	Text
Priority as a number, to sort	`SWITCH([Priority], "Highest", 1, "High", 2, "Medium", 3, 4)`	Number
Due date or fallback	`COALESCE([Due date], [Created] + 30)`	Date
Owner or a warning	`IF(ISBLANK([Assignee]), "Unassigned", [Assignee])`	Text
Stuck in status for over a week	`[Time in current status] > 7 * 24`	Automatic