Use advanced Formulas to make calculations in ClickUp.
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.
- Individual Custom Fields can be hidden from guests.
- Guests with edit or full edit permissions can edit existing Custom Fields but cannot create new ones.
- Formula Fields do not support using Custom Fields that contain text.
Create an advanced Formula
Learn how to create an advanced Formula.
Ask ClickUp AI to write an advanced Formula
If enabled in your Workspace, you can ask ClickUp AI to write you an advanced Formula!
Formulas with specific syntax requirements are complex, so results may vary. As we continuously refine ClickUp AI, accuracy will improve.
Show advanced Formulas in tasks and Lists
Show Formula data in your tasks and views.
Add advanced Formulas to new locations
Learn how to duplicate a Formula Field to use it in another location.
Use Formulas in Formulas
You can use other Formula Fields as variables in your 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.
Date and time functions
Use advanced Formulas to make calculations using date and time functions.
String functions
Use advanced Formulas to make calculations using string functions.
Logic functions
Use advanced Formulas to make calculations using logic functions.
Mathematical functions
Use advanced Formulas to make calculations using mathematical functions.
Real-world Formula examples
You can use advanced Formulas to add or subtract days, show percent signs, round decimal places, and more! The table below shows real-world use cases of advanced Formulas:
| 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. |
|
Unexpected results from advanced Formulas
If your advanced Formula is not calculating the expected result, try the following:
-
Double-check for typos, correct use of parentheses and commas, and ensure you're using the correct function names.
-
Ensure field names are exact and case-sensitive and that all variables used in the calculation are of supported data types.
You can’t use conditional formatting based on the result of a Formula Field.
-
Ensure that you are referencing appropriate fields. If you're working with dates, ensure the date fields you're referencing are populated.
- If you're using a Checkbox Custom Field in your Formula, ensure your Formula uses these boolean values appropriately.
-
Read about the different types of functions to ensure your syntax is correct:
-
Consider using ClickUp AI to write the Formula for you by stating what you want the Formula to do.
- Remove filters from the view or Dashboard. If this corrects the calculation, please contact Support by clicking the chat bubble icon in the lower-right corner of your screen.
- If you've checked your Formula and believe the syntax is correct, please contact Support by clicking the chat bubble icon in the lower-right corner of your screen.