If you have a space symbol in Field or Collection name, put the name in square brackets [ ] , like this:

[User Stories].[Total Effort]

Basic math operations

Nothing fancy here, usual operators are supported (*, -, +, /)

(Effort * Risk) / Complexity.Value
Velocity - [Assigned Effort]

There is also Power(number, power) function in formulas. Note that decimal power is also possible. For example:

Power(10, 2) → 100 
Power(4, 0.5) → 2

Access Collection and Fields

Use dot notation to access Collections and Fields:

Epic.Product.Value * [Own Value]


For example, you have Product and several Features linked to every Product. On a Product level you may want to calculate various metrics for Features. Here is where collection aggregates will help:

  • Sum(Number Field Name)

  • Avg(Number Field Name)

  • Count()

  • Max(Number Field Name)

  • Min(Number Field Name)

For Product Database you may use these examples:


Sort([field, isAscending]) + First() and Last()

Sort a collection of Entities by a particular Field (rank by default) and get the first/last Entity.

For example, here is how you get the latest Sprint for a Team and extract its progress:

Sprints.Sort([Start Date]).Last()
Sprints.Sort([Start Date]).Last().Progress

Join(text field, delimiter)

Combines text values from a collection of entities into a single text. For example, returns a string of assignees

Assignees.Join(Name, ', ') → "Michael, Teddy, Jerry"


Counts the number of unique values in a collection of entities

How many teams work on Feature across all User Stories?



In general, you can do operations with Dates like with Numbers. For example, here is how you define the duration of a project in days:

ToDays(Planned.End(false) - Planned.Start())

(see this article for why it is appropriate to use the false parameter)

Note that you can just subtract dates. The result of these operations is a Duration object that has format Duration(days, hours, minutes, seconds). For example, it can be Duration(10, 0, 0, 0) and this is exactly 10 days. 

Then you have to apply functions like ToDays or ToHours to convert the duration object into a number of days or hours.


Returns current date.

ToDays(Today() - Planned.Start())

If you want to receive "yesterday"

Today() - Days(1) 


Convert a time duration into a number of days.

ToDays(Planned.End(false) - Planned.Start())


Convert a time duration into a number of hours.

ToHours(Planned.End(false) - Planned.Start())


Convert a time duration into a number of minutes.

ToMinutes(Planned.End(false) - Planned.Start())


Convert a time duration into a number of seconds.

ToSeconds(Planned.End(false) - Planned.Start())

Duration(days, hours, minutes, seconds)

Create a time duration.

Duration(10, 0, 0, 0)


Returns Duration for days.
For example, you want to create a field that will show the date 2 days before the deadline. Later you can use this field to highlight cards on boards. Here is how you do it:

[Due Date] - Days(2)


Returns Duration for hours.


Returns Duration for minutes.


Returns Duration for seconds.


These functions can be used inside collections. Here is the case:

  • A Feature is decomposed into Stories

  • The Stories are planned using Sprints

  • Each Sprint has a start date and an end date

Could I automatically calculate the start and the end date of the Feature to show on a Timeline? Yes, you could:

Feature.[Start Date] = Stories.Min(Sprint.[Start Date])
Feature.[End Date] = Stories.Max(Sprint.[End Date])

Filter inside collections

Use Filter function (with AND and OR operators) to extract subsets from collections.

Sum of Effort of all not completed Features:

Features.Filter(State.Final != true).Sum(Effort)

Count of all high complexity non-estimated Features: 

Features.Filter(Effort = 0 AND Complexity.Name = "High").Count()

Logic (If, and, or)

You can use If  to create various interesting formulas. 

If(condition, true result, false result)

Returns some results based on condition. Here are some examples:

Cycle Time calculation:

If([Planned Dates].End > Today(),
ToDays(Today() - [Planned Dates].Start),
ToDays([Planned Dates].End - [Planned Dates].Start))

Generate some text field based on conditions

If((Confidence > 10) and (Reach > 10),"Important","So-so")

Nest if functions:

If([RICE Score] >= 15,"Cool", If([RICE Score] >= 10,"OK","Bad"))

Generate checkbox (for example, you may create "Fix ASAP" field with this formula)

If((Risk = "High") and (Severity = "High"), true, false)


Checks whether the value is empty

IsEmpty("") → true

Text formulas

Use + to concatenate strings. Some examples:

Name + " " + [Public Id]

Project.Abbreviation + "-" + [Public Id]

"https://www.google.com/maps/search/?api=1&query=" + Address

Note that you can select Text or URL as a formula type.

Calculates the number of characters in the text

Length("This phrase has 29 characters")

Converts the text to lower/upper case

Upper(Name) + " " + Upper(Abbreviation)

Left(text, numberOfCharacters)
Extracts the desired number of starting characters from the text

Left("Fibery rules", 6)

Right(text, numberOfCharacters)
Extracts the desired number of ending characters from the text

Right("Fibery rules", 5)

Removes starting and ending spaces from the text


EndsWith(text, suffix)
Checks if text ends with a suffix

EndsWith("Fibery rules", "rules")

StartsWith(text, prefix)
Checks if text starts with a prefix

EndsWith("Fibery rules", "Fibery")

MatchRegex(text, regularExpression)
Checks if text matches a regular expression

MatchRegex("Fibery", "F.b")

Replace(text, searchFor, replacementText)
Substitutes a matching part of the text

Replace("Fibery is slow", "slow", "fast")

ReplaceRegex(text, regularExpression, replacementText)
Substitutes regular expression matches in the text

ReplaceRegex("It costs $2000", "(\d+)", "1000")

Middle(text, startAt, numberOfCharacters)

Extracts the desired number of characters from the middle of the text

Middle("Umbrella", 5, 4)

Find(text, searchFor)

Finds the first occurrence of the string in the text

Find("Where's Waldo?", "Waldo") → 8

ToText([number] or [date])

Converts numbers or dates into text

ToText([Creation Date])

There is no pattern formatting, so you might use this trick

ToText(Day([Creation Date])) + " " + MonthName([Creation Date]) + " " + ToText(Year([Creation Date])) → 21 July 2020 

MonthName(date, [format])

Extracts the full or short month name from a date.

Supported formats: MONTH, Month, month, MON, Mon, mon.

MonthName([Creation Date], "Mon")

WeekDayName(date, [format])

Extracts the day-of-week name from a date.

Supported formats: DAY, Day, day, DY, Dy, dy.

WeekdayName([Creation Date])


Round ()

Round() function rounds a number to the specified number of decimal places. There are also two more:

RoundDown(number[, places])
Rounds a number down to the specified number of decimal places

RoundUp(number[, places])
Rounds a number up to the specified number of decimal places

Round(1.154, 1) → 1.2 
RoundDown(9.999, 2) → 9.99
RoundUp(12.001, 1) → 12.1

Greatest(value1[, value2, value3,…])

Calculates the largest number or the latest date across several values

Greatest(Appointments.Max([Creation Date], Drugs.Max([Creation Date])), Comments.Max([Creation Date]))

Least(value1[, value2, value3,…])

Calculates the smallest number or the earliest date across several values

Least(Appointments.Max([Creation Date], Drugs.Max([Creation Date])), Comments.Max([Creation Date]))
Did this answer your question?