Use Formula Fields

Formula Fields allow you to make calculations between numeric, date, and time fields on tasks using simple or advanced Formulas. They can be applied at any level of your Workspace Hierarchy, but they cannot be added to additional locations.

Formula options automatically calculate the entire column. Editing a Formula in one task edits the Formula for the entire column.

What you'll need

  • 60 uses of Custom Fields, including Formula Fields, are available on the Free Forever Plan.
  • Unlimited uses are available on all paid plans.
  • You can calculate Formula Fields on all paid plans.
  • Individual Custom Fields can be hidden from guests.
  • Guests with edit or full permissions can edit existing Custom Fields but cannot create new ones.
  • Formula Fields that use the TODAY() function cannot be sorted, filtered, grouped, or used in Dashboard Calculation cards.
  • You cannot calculate Formula Field columns if the Formula Field uses the TODAY() function.

Create a Formula Field

To create a Formula Field in List or Table view:

  1. In the upper-right corner above the task table, click the plus icon.
  2. Select Formula.
  3. Name the Formula Field.
  4. Click Create.
  5. From the new Formula column, click the ƒx symbol.
  6. From the dropdowns, select two fields and an operator. You may need to create the fields you need.

For example, there are two number Custom Fields named Revenue and Cost. There is a Forumla Custom Field named Profit. The Formula set for the Formula Custom Field is Revenue – Cost. The sum shows in the Profit field when values are entered into the Revenue and Cost fields

Screenshot of a Formula subtracting cost from revenue.png

The Calculation card shows the sum of all the Profit Formula Fields in all locations the card is set to report on. 

Screenshot of the Calculation card.png

Create a Formula Field column

To create a Formula Field in List view or Table view:

  1. In the upper-right corner, click the plus icon.
  2. Select Formula.
  3. Name the Formula Field.
  4. Click Add Column.

Supported fields

With over 70 different functions, Formulas can calculate data using the following fields:

  • Number Custom Fields
    You can use Number Custom Fields in Calculation cards
  • Currency Custom Fields
  • Dropdown Custom Fields that only use numbers
  • Date Custom Fields
  • Start Date
  • Due Date
  • Date Done
  • Date Created
  • Date Started
  • Date Updated
  • Date Closed
  • Time Tracked
    • Time Tracked is in hours.
  • Time Estimated
    • Time Estimated is in hours.
  • Sprint Points

Use simple Formulas

Simple Formulas are meant for quick addition, subtraction, division, and multiplication.

To create a simple Formula:

  1. Click your Formula Field.
  2. Select a value from the first dropdown.
  3. Select an operator:
    • Add
    • Subtract
    • Multiply
    • Divide
  4. Select a value from the second dropdown.
  5. Click Calculate.

Use advanced Formulas

Advanced Formulas allow for more complex and flexible calculations.

To create an advanced Formula:

  1. Click your Formula Field.
  2. Toggle on the Advanced Editor.
  3. Search through the available Custom Fields and operators.
  4. Write a valid Formula in the text field at the top.
  5. Click Calculate.

Sort Formula Fields

You can sort tasks in List or Table view by Formula Fields.

You cannot sort tasks by Formula Fields that contain the TODAY() function or return multiple output types.

To sort tasks by Formula Fields:

  1. Open the view.
  2. Click the header of the Formula Field column.
  3. Choose one of the sorting options:
    • Sort: Sort the current group of tasks by the Formula Field column.
    • Sort entire column: Sort every visible task in the view by the Formula Field column.

To clear your sorting selection:

  1. Click the header of the Formula Field column.
  2. Click Clear sort.

Filter Formula Fields

You can filter tasks by Formula Fields.

You cannot filter tasks by Formula Fields that contain the TODAY() function or return multiple output types.

To filter tasks by Formula Fields:

  1. Open the view.
  2. At the top of the view, click Filters.
  3. In the first dropdown, select the Formula Field.
  4. Add the remaining filter criteria.

Group Formula Fields

You can group tasks by Formula Fields in List view and Table view.

You can only group tasks by Formula Fields that output dates, booleans, or a mix of dates and booleans.

To group by a Formula Field:

  1. Open the view.
  2. At the top of the view, click Group.
  3. Select Custom Field.
  4. Choose the Formula Field.

Calculate Formula Field columns

You can calculate Formula Field columns in List view on all paid plans.

You can only calculate columns for Formula Fields that output numbers and dates.

To calculate a Formula Field column:

  1. At the bottom of a Formula Field column, click Calculate.
  2. Options vary depending on Formula output:
    • For Formulas that return dates, choose between Range, Earliest date, and Latest date.
    • For Formulas that return numbers, choose between Sum, Average, or Range.
  3. Results display at the bottom of the column.

Real-world advanced Formula examples

You can use advanced formulas to add or subtract days, show percent signs, round decimal places, and more! You can use advanced formulas to add or subtract days, show percent signs, round decimal places, and more! 

Desired outcome Method Example formula
Add a specific number of days to the date field Use the DATE function and apply basic mathematical expressions for YEAR(), MONTH(), OR DAY(). For example, to add seven days to a date: DATE(YEAR(field("Due date")),MONTH(field("Due date")),DAY(field("Due date"))+7)
Subtract a specific number of days from the date field Use the DATE function and apply basic mathematical expressions for YEAR(), MONTH(), OR DAY(). For example, to subtract five days from a date: DATE(YEAR(DAYS(field("Due date"), 5)), MONTH(DAYS(field("Due date"), 5)), DAY(DAYS(field("Due date"), 5)))
Calculate the difference between two dates Use the DAYS function to accomplish this.

The following format is used for this formula: DAYS(end_date, start_date)

To calculate the difference between a task's start and due date: DAYS(field("Due date"),field("Start date"))

Calculate a percentage from a Custom Field Instead of decimals, use percentages in the formula. For example: field("Number Field")*10%
Color-code results Use a formula to color-code results with emojis.

For example:

• If the age of a task is 10 days or less, add a green indicator.

• If the age of a task is 20 days or less, add a yellow indicator.

• If the age of a task is over 20 days, add a red indicator.

The following formula would achieve the intended result: IF(DAYS(TODAY(), field("Date created"))<11, "🟢", IF(DAYS(TODAY(), field("Date created"))<21, "🟡","🔴"))

Add a percent sign Use the CONCATENATE function to add a percent sign.

There are two ways to add a percent sign:

• Use the CONCATENATE function: CONCATENATE((field("Number 1")*field("Number 2")),"%")
• Use &"%"to save time: (field("Number 1")*field("Number 2"))&"%"
Field names are case-sensitive.

Round decimal places on results Use the ROUND function to round results after using basic functions. The following format is used for this formula: ROUND(value, [places])
To add two number fields and round the result to the hundredth decimal place, use the following formula: ROUND((field("Number 1") + field("Number 2")),2)
Field names are case-sensitive.
Calculate the number of days a task is overdue

Use a formula to see how many days a task is overdue.

The following format is used for this formula:

IF(TODAY()<=field("Due date"),0,DAYS(TODAY(),field("Due date")))

Calculate the number of days it took to complete a task

Use a formula to see how many days it took to complete a task.

The following format is used for this formula:

DAYS(field("Date closed"),field("Date created"))

Alternatively, calculate using start date instead of date created:

DAYS(field("Date closed"),field("Start date"))

Show today's date in DD/MM/YYYY format

Use a formula to see today's date in DD/MM/YYYY instead of the default MM/DD/YYYY.

CONCATENATE(DAY(TODAY()),"/",MONTH(TODAY()),"/",YEAR(TODAY()))

Show the number of weeks between start and due dates

Use a formula to see the number of weeks between a task's start and due dates.

DAYS(field("Due date"),field("Start date"))/7

Move weekend due dates to Monday

Use a formula to show the due date of a task if it was moved to the following Monday.

This formula only works if the task's original due date is on a Saturday or Sunday.

SWITCH(WEEKDAY(field("Due date")), 1, DATE(YEAR(field("Due date")), MONTH(field("Due date")), DAY(field("Due date")) + 1), 7, DATE(YEAR(field("Due date")), MONTH(field("Due date")), DAY(field("Due date")) + 2), field("Due date"))

Formula Fields limits

The following actions are not supported with Formula Fields:

  • Formula Fields do not support using Custom Fields that contain text.

  • Formula Fields that use the TODAY() function cannot be sorted, filtered, or grouped.

  • You cannot calculate Formula Field columns if the Formula Field uses the TODAY() function.

Was this article helpful?