Formula Fields allow you to make calculations between numeric, date, and time fields on tasks using simple or advanced Formulas.
From calculating the number of days between start and due dates, differences between Custom Fields, or more complex calculations, use Formula Fields to figure out additional information that will enrich your ClickUp experience.
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. - You must have access to all of the fields used in Formula Custom Fields to see the results. If a private field that you don't have access to is used, you won't see results.
Create a Formula Field
To create a Formula Field in List or Table view:
- In the upper-right corner above the task table, click the plus icon.
- Select Formula.
- Name the Formula Field.
- Click Create.
- From the new Formula column, click the ƒx symbol.
- From the dropdowns, select two fields and an operator. You may need to create the fields you need.
- You can also open the Advanced Editor to create an advanced Formula.
For example, there are two number Custom Fields named Revenue and Cost. There is a Formula 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.
The Calculation card shows the sum of all the Profit Formula Fields in all locations the card is set to report on.
Formula Fields can be applied at any level of your Workspace Hierarchy, but they cannot be added to additional locations.
Create a Formula Field column
To create a Formula Field in List view or Table view:
- In the upper-right corner, click the plus icon.
- Select Formula.
- Name the Formula Field.
- Click Add Column.
Formula options automatically calculate the entire column. Editing a Formula in one task edits the Formula for the entire 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
- Other Formula Fields
Use simple Formulas
Simple Formulas are meant for quick addition, subtraction, division, and multiplication.
To create a simple Formula:
- Click your Formula Field.
- Select a value from the first dropdown.
- Select an operator:
- Add
- Subtract
- Multiply
- Divide
- Select a value from the second dropdown.
- Click Calculate.
Use advanced Formulas
Advanced Formulas allow for more complex and flexible calculations.
To create an advanced Formula:
- Click your Formula Field.
- Toggle on the Advanced Editor.
- Search through the available Custom Fields and operators.
- Write a valid Formula in the text field at the top.
- Click Calculate.
Use Formulas in Formulas
You can use other Formula Fields as variables in your simple and advanced Formulas!
When creating a new Formula, there is no limit to the number of other Formulas you can use in its definition, and you can mix and match with other numeric fields like Numbers and Dates.
Nested Formulas cannot currently exceed one layer. For example, a Formula that is defined using another Formula cannot be used to define a third Formula.
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:
- Open the view.
- Click the header of the Formula Field column.
- 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:
- Click the header of the Formula Field column.
- 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:
- Open the view.
- At the top of the view, click Filters.
- In the first dropdown, select the Formula Field.
- 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:
- Open the view.
- At the top of the view, click Group.
- Select Custom Field.
- 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:
- At the bottom of a Formula Field column, click Calculate.
- 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.
- 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: To calculate the difference between a task's start and due 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: |
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: |
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:
|
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:
Alternatively, calculate using start date instead of date created:
|
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. |
|
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. |
|
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. |
|
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.