With Formula Fields, you can easily make calculations between numeric, date, and time Custom 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 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"!
With over 70 different functions, formulas currently work with these Custom Fields:
Number fields
Currency field
Dropdowns fields that only use numbers
Date field
Start Date & Due Date
Date Started, Date Updated, Date Closed
Time Tracked (in hours)
Time Estimated (in hours)
Coming Soon
This is just a taste of what's to come! We plan on adding a lot more functionality, including...
Formula Column Calculations (sum, average, range)
More functions
Simple formula
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 (multiply, divide, add, subtract)
Select a second value
Click
Calculate
Advanced formula
For those looking for a bit more flexibility, advanced formulas can be enabled by flipping the switch in the bottom corner of the formula options. To create an advanced formula:
Search through the list of available Custom Fields and operators
Write any valid formula
Click
Calculate
Note:
Formula Fields can be applied at any level but are not transferable to other locations.
Formula options automatically calculate on the entire column; editing a formula in one task edits the formula for the entire column.
Supported advanced functions
We support many of the common functions used in your favorite spreadsheet application! Additional documentation can be found in app when building your formula.
FUNCTIONS | SYNTAX |
DATE | DATE(year, month, day) |
DAY | DAY(date) |
DAYS | DAYS(end_date, start_date) |
EDATE | EDATE(start_date, months) |
EOMONTH | EOMONTH(start_date, months) |
HOUR | HOUR(time) |
MINUTE | MINUTE(time) |
MONTH | MONTH(date) |
NETWORKDAYS | NETWORKDAYS(start_date, end_date, [holiday]) |
TODAY | TODAY() |
WEEKDAY | WEEKDAY(date, [type]) |
YEAR | YEAR(date) |
WEEKNUM | WEEKNUM(date, [type]) |
WORKDAY | WORKDAY(start_date, num_days, [holidays]) |
AND | AND(logical_expression1, [logical_expression2, ...]) |
IF | IF(logical_expression, value_if_true, value_if_false) |
NOT | NOT(logical_expression) |
OR | OR(logical_expression1, [logical_expression2, ...]) |
SWITCH | SWITCH(expression, case1, value1, [default or case2, value2], …) |
XOR | XOR(logical_expression1, [logical_expression2, ...]) |
COS | COS(angle) |
ISEVEN | ISEVEN(value) |
LOG | LOG(value, base) |
MOD | MOD(dividend, divisor) |
POWER | POWER(base, exponent) |
PRODUCT | PRODUCT(factor1, [factor2, ...]) |
QUOTIENT | QUOTIENT(dividend, divisor) |
RANDBETWEEN | RANDBETWEEN(low, high) |
ROUND | ROUND(value, [places]) |
ROUNDDOWN | ROUNDDOWN(value, [places]) |
ROUNDUP | ROUNDUP(value, [places]) |
SIN | SIN(angle) |
SQRT | SQRT(value) |
SUM | SUM(value1, [value2, ...]) |
SUMIF | SUMIF(range, criterion, [sum_range]) |
SUMIFS | SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...]) |
SUMPRODUCT | SUMPRODUCT(array1, [array2, ...]) |
TAN | TAN(angle) |
TRUNC | TRUNC(value, [places]) |
AVERAGE | AVERAGE(value1, [value2, ...]) |
AVERAGEIF | AVERAGEIF(criteria_range, criterion, [average_range]) |
AVERAGEIFS | AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...]) |
COUNT | COUNT(value1, [value2, ...]) |
COUNTBLANK | COUNTBLANK(range) |
COUNTIF | COUNTIF(range, criterion) |
COUNTIFS | COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...]) |
COUNTUNIQUE | COUNTUNIQUE(value1, [value2, ...]) |
LARGE | LARGE(data, n) |
MAX | MAX(value1, [value2, ...]) |
MEDIAN | MEDIAN(value1, [value2, ...]) |
MIN | MIN(value1, [value2, ...]) |
SMALL | SMALL(data, n) |
CHAR | CHAR(table_number) |
CLEAN | CLEAN(text) |
CONCATENATE | CONCATENATE(string1, [string2, ...]) |
EXACT | EXACT(string1, string2) |
FIND | FIND(search_for, text_to_search, [starting_at]) |
LEFT | LEFT(string, [number_of_characters]) |
LEN | LEN(text) |
LOWER | LOWER(text) |
MID | MID(string, starting_at, extract_length) |
PROPER | PROPER(text_to_capitalize) |
REGEXEXTRACT | REGEXEXTRACT(text, regular_expression) |
REGEXMATCH | REGEXMATCH(text, regular_expression) |
REGEXREPLACE | REGEXMATCH(text, regular_expression) |
REPLACE | REPLACE(text, position, length, new_text) |
RIGHT | RIGHT(string, [number_of_characters]) |
SEARCH | SEARCH(search_for, text_to_search, [starting_at]) |
SPLIT | SPLIT(text, delimiter, [split_by_each], [remove_empty_text]) |
TRIM | TRIM(text) |
UPPER | UPPER(text) |