Estamos trabalhando na tradução dos nossos artigos da central de ajuda. Você também pode acessar nossa central de ajuda em inglês ou entrar em contato conosco para dar um feedback.

With Formula Fields, you can easily make calculations between numeric, date, and time fields on a task. Automatically determine things like the cost of a new product order or scoring for a new lead.

Formula Fields allow you to calculate task and other Custom Fields using both simple and advanced formulas.

Imagine that you have an hours worked Custom Field along with an hourly cost Custom Field. With Formulas, you can multiply these together to get the total cost or budget!

Formula Fields are a type of Custom Field, so they are available along with all other Custom Fields.

• Workspaces on our Free Forever Plan get 100 uses of Custom Fields to try out the feature

• Workspaces on our Unlimited Plan and above have unlimited use of Custom Fields

• Custom Fields can be hidden from guests

How do Formula Fields Work?

Formula Fields can be applied at any level of your Workspace hierarchy, but they are not transferable to other locations.

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

Pro tip: Try using a Formula Field in List view to see how they are automatically calculated on all tasks!

Supported Fields

With over 70 different functions, formulas work with the following fields:

1. Number Custom Fields

2. Currency Custom Fields

3. Dropdown Custom Fields that only use numbers

4. Date Custom Fields

5. Start Date & Due Date

6. Date Created

7. Date Started

8. Date Updated

9. Date Closed

10. Time Tracked (in hours)

11. Time Estimated (in hours)

Simple Formulas

The simple version of Formula Fields is meant for quick calculations of values. To create a simple formula:

1. Select the first value from the dropdown

2. Select an operator (add, subtract, multiply, or divide)

3. Select a second value

4. Click Calculate

Advanced Formulas

For those looking for a bit more flexibility, advanced formulas can be enabled by clicking on the Advanced Editor toggle.

To create an advanced formula:

1. Search through the list of available Custom Fields and operators

2. Write any valid formula

3. Click Calculate

Supported Advanced Functions

We support many of the common functions used in your favorite spreadsheet applications! Our functions can be broken down into four basic categories, Date & Time, Logic, Math, and Strings!

Note: Any variables surrounded by {} in the following sections are optional variables and can be left out of a function without the function breaking.

Date & Time Functions

Below is a table of our simplest date & time functions. All inputs are date objects and all outputs are numbers, except for TODAY() which has no inputs and simply outputs a date corresponding to the current day's date.

Here are our more complex date & time functions:

DATE(year, month, day): This function takes three numbers then returns a date object corresponding to the numbers that were input.

Example: DATE(2021, 7, 15)

Expected Result: 7/15/21

EDATE(start_date, months): This function takes a date object (start_date) and a number (months) then returns a date representing a specified number of months before or after the start_date. Negative numbers will allow you to access months before the start_date.

Because this function returns a serial number rather than a date object, you will need to use the DATE() function to convert it to a date object.

Example: DATE(YEAR(EDATE('7/15/2021', -1)), MONTH(EDATE('7/15/2021', -1)), DAY(EDATE('7/15/2021', -1)))

Expected Result: 6/15/21

EOMONTH(start_date, months): This functions takes a date object (start_date) and a number (months) then returns a date representing the last day of a month which falls a specified number of months before or after the start_date. Negative numbers will allow you to access months before the start_date.

As this function returns a serial number rather than a date object, you will need to use the DATE() function to convert it to a date object.

Example: DATE(YEAR(EOMONTH('7/15/21', 0)), MONTH(EOMONTH('7/15/21', 0)), DAY(EOMONTH('7/15/21', 0)))

Expected Result: 7/31/21

HOUR(time): This functions takes a date object that includes time then returns a number corresponding to the hour of the time that was input. The number result will be based on a 24 hour clock.

Example: HOUR('7/15/21 7:45:00 PM')

Expected Result: 19

MINUTE(time): This functions takes a date object that includes time then returns a number corresponding to the minute of the time that was input.

Example: MINUTE('7/15/21 7:45:00 PM')

Expected Result: 45

NETWORKDAYS(start_date, end_date, {holidays}): This function takes two date objects and an array of date objects then returns a number corresponding to the net working days between start_date and end_date, excluding weekends and any specified holidays.

Example: NETWORKDAYS('7/15/21', '7/22/21', ['7/19/21', '7/20/21'])

Expected Result: 4

WORKDAY(start_date, num_days, {holidays}): This function takes a date object, a number, and an array of date objects then returns a date object corresponding to the number of working days after start_date, excluding weekends and any specified holidays.

Example: WORKDAY('7/15/21', 7, ['7/19/21', '7/20/21'])

Expected Result: 7/28/21

WEEKDAY(date, {type}): This function takes a date object and a number then returns a number representing the day of the week that the specified date lands on.

The type variable is a bit more complex. Here's a table displaying what each number means for this function:

Example: WEEKDAY('7/15/21', 2)

Expected Result: 4

WEEKNUM(date, {type}): This function takes a date object and a number then returns a number representing the week of the year that the specified date lands on.

The type variable is a bit more complex. Here's a table displaying what each number means for this function:

Example: WEEKNUM('7/15/21', 1)

Expected Result: 29

Type 21 follows the methodology specified by ISO 8601, which is commonly known as the European week numbering system. The week containing the first Thursday of the year is the first week of the year, and is numbered as week 1.

Logic Functions

Below is a table of the logic gates that ClickUp offers. All inputs and outputs are booleans (either true or false):

Here are the more complex logical operators:

EXACT(string1, string 2): This function takes two strings then returns true if the two strings are identical. Otherwise, the function returns false.

Example: EXACT('Word', 'word')

Expected Result: false

Note: Formula Fields do not support using Custom Fields that contain text.

IF(logical_expression, value_if_true, value_if_false): This function takes an argument and two variables then returns the value_if_true variable if the provided argument is logically true. Otherwise, the function returns the value_if_false variable.

Example: IF(true, 'Hello!', 'Goodbye!')

Expected Result: Hello!

Note: Formula Fields do not support using Custom Fields that contain text.

ISEVEN(value): This function takes a number then returns true if the number is even. Otherwise, the function returns false .

Example: ISEVEN(-2.5)

Expected Result: true

REGEXMATCH(text, regular_expression): This function takes a string and a regular expression then returns true if a piece of the string matches the regular expression. Otherwise, the function returns false.

Example: REGEXMATCH('Palo Alto', 'Alto')

Expected Result: true

SWITCH(expression, case1, value1, [default or case2, value2], {...}): This function tests an expression against a list of cases and returns the corresponding value of the first matching case, with an optional default value if nothing else is met.

Example: SWITCH(7, 9, 'Nine', 7, 'Seven')

Expected Result: Seven

Example: SWITCH(true, 9, 'Nine', 7, 'Seven', 'Not a number')

Expected Result: Not a number

Mathematical Functions

Below is a table of the basic mathematical operators that ClickUp offers. All inputs and outputs are numbers:

Here are the more complex mathematical operators:

AVERAGEIF(criteria_range, criterion): This functions takes an array of numbers and a criterion then returns a number corresponding to the average of the array depending on the criterion.

Example: AVERAGEIF([2, 4, 8, 16], ">=5")

Expected Result: 12

COUNT(value1, {value2, ...}): This function takes as many numbers as you would like then returns a number corresponding to the amount of numbers input.

Example: COUNT(1, 2, 3, 4)

Expected Result: 4

COUNTBLANK(variable1, {variable2, ...}): This functions takes as many variables as you would like then returns a number corresponding to the amount of empty variables input.

Example: COUNTBLANK(1, null, 3, 'a', '', 'c')

Expected Result: 2

COUNTIF(range, criterion): This function takes as many variables as you would like, then returns a number corresponding to the amount of variables that match the criterion.

Example: COUNTIF([1, 0, 5, 1, 0, 1, 2, 5], 5)

Expected Result: 2

Example: COUNTIF([1, 0, 1, 0, 1, 2], ">=1")

Expected Result: 4

COUNTUNIQUE(variable1, {variable2, ...}): This function takes as many variables as you would like then returns a number corresponding to the amount of unique variables input.

Example: COUNTUNIQUE('', '', 2, 5, 3, 3)

Expected Result: 4

LARGE({variable1, variable2, ...}, n): This function takes an array of numbers and a number then returns a number corresponding to the nth largest number from the numbers in the array, where n is user-defined.

If n exceeds the amount of numbers input in the array, the function will break and return blank.

Example: LARGE([3,5,3,5,4,4,2,4,6,7], 3)

Expected Result: 5

RANDBETWEEN(low, high): This function takes two numbers then returns an integer (whole number) corresponding to a random number between the two numbers input. This function includes the low and high numbers as possible outcomes.

Example: RANDBETWEEN(-1, 1)

Expected Results: Could return: [-1, 0, or 1]

SMALL({variable1, variable2, ...}, n): This function takes an array of numbers and a number then returns a number corresponding to the nth smallest number from the numbers in the array, where n is user-defined.

If n exceeds the amount of numbers input in the array, the function will break and return blank.

Example: SMALL([3,5,3,5,4,4,2,4,6,7], 3)

Expected Result: 3

SUMIF(range, criterion): This functions takes an array of numbers and a criterion then returns a number corresponding to the sum of the array depending on the criterion.

Example: SUMIF([2, 4, 8, 16], ">=5")

Expected Result: 24

TRUNC(value, {places}): This function takes two numbers then returns a number corresponding to the first number with a certain amount of significant digits specified by the second number.

If places is left blank, the function will truncate to a whole number.

To note: This function does not round the number, it simply removes significant digits from the result.

Example: TRUNC(-8.96234, 1)

Expected Result: -8.9

String Functions

Note: Formula Fields do not support using Custom Fields that contain text.

CHAR(table_number): This function takes a number then returns a character corresponding to the associated character in the current Unicode table.

Example: CHAR(65)

Expected Result: A

CLEAN(text): This function takes a string then returns a string with the non-printable ASCII characters removed.

Example: CLEAN("Before Tab After Tab")

Expected Result: Before TabAfter Tab

CONCATENATE(string1, {string2, ...}): This function takes as many variables as you would like then returns a string corresponding to all the variables appended together.
You can enter text to be added manually between quotation marks, use other functions, or any supported Fields.

Example: CONCATENATE("This year is ", YEAR("7/15/21"), "!")

Expected Result: This year is 2021!

FIND(search_for, text_to_search, {starting_at}): This function takes two strings and a number then returns a number corresponding to the position at which search_for is first found within text_to_search.

starting_at can be entered to determine where you would like to start searching text_to_search.

Example: FIND('M', 'Miriam McGovern', 3)

Expected Result: 13

LEFT(string, {number_of_characters}): This function takes a string and a number then returns a string corresponding to the beginning of string.

If number_of_characters isn't entered, the function will return the first letter in the string.

Example: LEFT("Sale Price", 4)

Expected Result: Sale

LEN(text): This function takes a string then returns a number corresponding to the length of the string.

Example: LEN("Phoenix, AZ")

Expected Result: 11

LOWER(text): This function takes a string then returns a string converted to lowercase.

Example: LOWER("E. E. Cummings")

Expected Result: e. e. cummings

MID(string, starting_at, extract_length): This function takes a string and two numbers then returns a string corresponding to the segment defined by the two numbers.

Example: MID("This is 21 characters", 12, 10)

Expected Result: characters

PROPER(text): This function takes a string then returns a string with each word capitalized.

Example: PROPER("this is a TITLE")

Expected Result: This Is A Title

REGEXEXTRACT(text, regular_expression): This function takes a string and a regular expression then returns a string corresponding to matching substrings according to the regular expression.

Example: REGEXEXTRACT('Palo Alto', 'Alto')

Expected Result: Alto

REGEXREPLACE(text, regular_expression, replacement): This function takes three strings then returns a string with part of text replaced by replacement using a regular expression.

Example: REGEXREPLACE('Hello!', 'ello', 'i')

Expected Result: Hi!

REPLACE(text, position, length, new_text): This function takes two strings and two numbers then returns a string with part of text replaced by new_text. The text being replaced is determined by position and length.

Example: REPLACE("Good morning!", 6, 7, "afternoon")

Expected Result: Good afternoon!

RIGHT(string, {number_of_characters}): This function takes a string and a number then returns a string corresponding to the end of string.

If number_of_characters isn't entered, the function will return the last letter in the string.

Example: RIGHT("Sale Price", 5)

Expected Result: Price

SEARCH(search_for, text_to_search, {starting_at}): This function takes two strings and one number then returns a number corresponding to the position at which search_for is first found within text_to_search.

Example: SEARCH('margin', 'Profit Margin')

Expected Result: 8

TRIM(text): This function takes a string then returns a string with removed leading and trailing spaces.

Example: TRIM(" First Quarter Earnings ")

Expected Result: First Quarter Earnings

UPPER(text): This function takes a string then returns a string converted to uppercase.

Example: UPPER("total")

Expected Result: TOTAL

Real-world examples

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

Atualizado 5 de junho de 2023 03:42