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.ValueVelocity - [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.EstimateEpic.Product.Value * [Own Value]``

## Collections

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:

``Features.Count()Features.Sum(Estimate)Features.Max(Estimate)Features.Avg(Estimate)``

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

### CountUnique(expression)

Counts the number of unique values in a collection of entities

How many teams work on Feature across all User Stories?

``Stories.CountUnique(Team.PublicId)``

## Dates

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.

### Today()

Returns current date.

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

If you want to receive "yesterday"

``Today() - Days(1) ``

### ToDays(Duration)

Convert a time duration into a number of days.

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

### ToHours(Duration)

Convert a time duration into a number of hours.

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

### ToMinutes(Duration)

Convert a time duration into a number of minutes.

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

### ToSeconds(Duration)

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

### Days(number)

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

### Hours(number)

Returns Duration for hours.

### Minutes(number)

Returns Duration for minutes.

### Seconds(number)

Returns Duration for seconds.

### Min/Max

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

### IsEmpty

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.

Length(text)
Calculates the number of characters in the text

``Length("This phrase has 29 characters")``

Lower/Upper(text)
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)``

Trim(text)
Removes starting and ending spaces from the text

``Trim(LastName)``

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(1067)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])``

## Misc.

### 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]))``