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
!
We'll walk you through:
Simple Formulas for quick mathematical calculations (add, subtract, multiply, divide)
Advanced Formulas for more sophisticated functions including:
Who can use this?
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:
Number Custom Fields
Currency Custom Fields
Dropdown Custom Fields that only use numbers
Date Custom Fields
Start Date & Due Date
Date Created
Date Started
Date Updated
Date Closed
Time Tracked (in hours)
Time Estimated (in hours)
Simple Formulas
The simple version of Formula Fields is meant for quick calculations of values. To create a simple formula:
Select the first value from the dropdown
Select an operator (add, subtract, multiply, or divide)
Select a second value
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:
Search through the list of available Custom Fields and operators
Write any valid formula
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.
Syntax | Example | Expected Result |
DAY(date) | DAY('7/15/21') | 15 |
DAYS(end_date, start_date) | DAYS('7/15/21', '6/15/21') | 30 |
MONTH(date) | MONTH('7/15/21') | 7 |
TODAY() | TODAY() | Today |
YEAR(date) | YEAR('7/15/21') | 2021 |
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:
type | Number returned |
1 or omitted | Numbers 1 (Sunday) through 7 (Saturday). |
2 | Numbers 1 (Monday) through 7 (Sunday). |
3 | Numbers 0 (Monday) through 6 (Sunday). |
11 | Numbers 1 (Monday) through 7 (Sunday). |
12 | Numbers 1 (Tuesday) through 7 (Monday). |
13 | Numbers 1 (Wednesday) through 7 (Tuesday). |
14 | Numbers 1 (Thursday) through 7 (Wednesday). |
15 | Numbers 1 (Friday) through 7 (Thursday). |
16 | Numbers 1 (Saturday) through 7 (Friday). |
17 | Numbers 1 (Sunday) through 7 (Saturday). |
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:
type | Week begins on |
1 or omitted | Sunday |
2 | Monday |
11 | Monday |
12 | Tuesday |
13 | Wednesday |
14 | Thursday |
15 | Friday |
16 | Saturday |
17 | Sunday |
21 | Monday* |
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
):
Syntax | Example | Expected Result |
AND(logical_expression1, [logical_expression2, {...}]) | AND(true, false, true) | false |
NOT(logical_expression) | NOT(false) | true |
OR(logical_expression1, [logical_expression2, {...}]) | OR(true, false, true) | true |
XOR(logical_expression1, [logical_expression2, {...}]) | XOR(true, false, true) | 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:
Syntax | Example | Expected Result |
AVERAGE(value1, {value2, ...}) | AVERAGE(2, 4, 8, 16) | 7.5 |
COS(angle[in radians]) | COS(1) | 0.5403023058681398 |
LOG(value, base) | LOG(8, 2) | 3 |
MAX(value1, {value2, ...}) | MAX(0.1, 0.2, 0.4, 0.8) | 0.8 |
MEDIAN(value1, {value2, ...}) | MEDIAN(1, 2, 3, 4, 5, 6) | 3.5 |
MIN(value1, {value2, ...}) | MIN(0.1, 0.2, 0.4, 0.8) | 0.1 |
MOD(dividend, divisor) | MOD(3, -2) | -1 |
POWER(base, exponent) | POWER(5, 2) | 25 |
PRODUCT(factor1, {factor2, ...}) | PRODUCT(5, 15, 30) | 2250 |
QUOTIENT(dividend, divisor) | QUOTIENT(-10, 3) | -3 |
ROUND(value, places) | ROUND(626.3, -3) | 1000 |
ROUNDDOWN(value, places) | ROUNDDOWN(-3.14159, 2) | -3.14 |
ROUNDUP(value, places) | ROUNDUP(-3.14149, 2) | -3.15 |
SIN(angle[in radians]) | SIN(1) | 0.8414709848078965 |
SQRT(value) | SQRT(16) | 4 |
SUM(value1, {value2, ...}) | SUM(-5, 15, 32) | 42 |
TAN(angle[in radians]) | TAN(1) | 1.5574077246549023 |
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
Want to learn more?
Check out the other types of Custom Fields that are available
Learn more about using Custom Fields in your Workspace
Use List view to work efficiently with Formula Fields