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:

1. How do Formula Fields Work?

1. Supported Fields

2. Simple Formulas for quick mathematical calculations (add, subtract, multiply, divide)

3. Advanced Formulas for more sophisticated functions including:

1. Date & Time

2. Logic (true or false)

3. Math

4. Strings (text)

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:

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` For those looking for a bit more flexibility, advanced formulas can be enabled by clicking on the `Advanced Editor` toggle. 1. Search through the list of available Custom Fields and operators

2. Write any valid formula

3. Click `Calculate` 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&nbsp;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`