Guide
This is a reference guide for all Formulas in Fibery.
Access Fields
Use dot notation to refer to fields in linked entities and collections.
If you have a space symbol in Field or Collection name, put the name in square brackets [ ] , like this:
Epic.Estimate
Epic.Product.Value * [Own Value]
[User Stories].[Total Effort]
Math operations
Nothing fancy here, usual operators are supported (*, -, +, /)
(Effort * Risk) / Complexity.Value
Velocity - [Assigned Effort]
Power(number, power)
There is also Power(number, power) function. Note that decimal power is also possible. For example:
Power(10, 2) → 100
Power(4, 0.5) → 2
Round(number)
Round() function rounds a number to the specified number of decimal places.
Round(1.154, 1) → 1.2
RoundDown(number[, places])
Rounds a number down to the specified number of decimal places
RoundDown(9.999, 2) → 9.99
RoundUp(number[, places])
Rounds a number up to the specified number of decimal places
RoundUp(12.001, 1) → 12.1
AbsoluteValue(number)
Calculates the absolute value of a number
AbsoluteValue(-14) → 14
AbsoluteValue(123) → 123
Log(number, base)
Calculates the logarithm of a number for a given base.
Log(128, 2) → 7
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 its Features. Here is where collection aggregates will help:
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) prior to getting 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)
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()
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())
(check Date Ranges in Formulas 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.
Here we subtract two dates and add dates difference (in days) to the third date:
Date(2022,1,13) + Days(ToDays(Today() - Date(2022,6,24)))
❌ (coming Q1 2025) You can also add months, using Months function.
Date(2022,1,13) + Months(6)
Today()
Returns current date.
ToDays(Today() - Planned.Start())
If you want to get "yesterday"
Today() - Days(1)
Now()
Returns current date time.
Now() is only available in Automation Formulas (not in Formula Fields).
Year(date)
Returns the year of a date (as a number).
Year(Today())
Month(date)
Returns the month of a date (as a number).
Day(date)
Returns the day of a date (as a number).
IsoWeekday(date)
Extracts the day of the week of the date as a number between 1 (Monday) and 7 (Sunday)
IsoWeekday(Today()) → 4
IsoWeekNum(date)
Returns number of the ISO week number of the year for a given date.
IsoWeekNum([Creation Date]) → 23
Date(year, month, day)
Returns a date based on the year, month and day numbers.
Date(1999, 12, 31)
DateTime(year, month, day, hours, minutes, seconds)
Returns a datetime value.
DateTime(1999, 12, 31, 23, 59, 50)
You may want to check out how Timezones work.
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-the-week name from a date.
Supported formats: DAY, Day, day, DY, Dy, dy.
WeekdayName([Creation Date])
Duration
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)
Months(number)
Returns a Duration for the number of months.
For example, you want to create a field that will show the date 1 month before the deadline.
[Due Date] - Months(1)
Days(number)
Returns a Duration for the number of 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 a Duration for the number of hours.
Minutes(number)
Returns a Duration for the number of minutes.
Seconds(number)
Returns a Duration for the number of seconds.
DateRange(start date, end date)
Returns a date range, from the start date to the end date.
DateRange(Today(), Today() + Days(2))
DateRange(Date(1999, 12, 31), Today())
Check out Date Ranges in Formulas.
DateTimeRange(start datetime, end datetime)
Returns a datetime range, from the start datetime to the end datetime.
DateRange(DateTime(1999, 12, 31, 23, 59, 50), DateTime(2000, 1, 1, 00, 00, 00))
Logical (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(value)
Checks whether the value is empty.
IsEmpty("") → true
IfEmpty(value)
Returns the first non-empty value from a list
IfEmpty(Interview.Weight, Chat.Weight, Topic.Weight, Conversation.Weight, 1.0)
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]))
Text
Use + to combine or join strings. Some examples:
Name + " " + [Public Id]
Project.Abbreviation + "-" + [Public Id]
"https://www.google.com/maps/search/?api=1&query=" + Address
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.
StartsWith("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") → 9
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
Rich Text (snippets)
Snippets are a plain text version of the rich text field.
You can use the snippet field in the formula as a proxy for the rich text field. For example, you can generate the entity Name field from the snippet.
Note: snippets are generated asynchronously (which means there is a slight delay between changes to the rich text field and updates to the snippet, and the snippet fields only store the first 3000 characters of the Rich text field.
Location
FullAddress
Extracts full address from location.
[Client Address].FullAddress()
Latitude
Extracts latitude coordinate portion of location.
[Client Address].Latitude()
Longitude
Extracts longitude coordinate portion of location.
[Client Address].Longitude()
AddressPart
Extracts specific part of address component.
[Client Address].AddressPart("country")
Available options: country, region, post code, district, place, locality, neighborhood, address, points of interest.
FAQ
Can I use a single select field in a formula?
Yes, you can. Please find details here Single and Multi-Select fields
Is there a way to get the initials of a Creator in a formula?
i.e. Polina John = PJ?
A very rudimentary way is to strip out anything that is not a capital letter, so Polina John becomes PJ
Might not work as you'd like for 'weird' names, like Prof. John McNichol Snr !
Here's a formula to do it
ReplaceRegex([Created By].Name, "[^A-Z]", "")
Why does rounding work differently for negative numbers?
I have a calculation that sometimes produces negative numbers (e.g. expenses). When I apply Round Up or Round Down, the behavior seems reversed:
Is this a bug?
This is expected behavior. Fibery uses mathematical rounding:
Round Up always rounds toward positive infinity. For negative numbers, this means moving closer to 0 (e.g., -10.5 → -10).
Round Down always rounds toward negative infinity. For negative numbers, this means moving farther from 0 (e.g., -10.5 → -11).
If you want rounding based on absolute value (e.g., always rounding away from zero), you’ll need to adjust your calculation formula accordingly.
