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 custom 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 multiple these together to get the total "cost" or "budget". 

With over 70 different functions, formulas currently work with these custom fields: 

  1. Number fields
  2. Currency field
  3. Dropdowns fields that only use numbers
  4. Date field
  5. Start Date & Due Date
  6. Date Started, Date Updated, Date Closed
  7. Time Tracked (in hours)
  8. Time Estimated (in hours)

Coming Soon

This release is just a taste of what's to come! We plan on adding a lot more functionality, including...

Simple formula

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 (multiply, divide, add, subtract)
  3. Select a second value
  4. Click Calculate  
formula field example

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: 

  1. Search through the list of available custom fields and operators
  2. Write any valid formula
  3. Click Calculate  
how to create an advanced formula

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)

COPUNTIFS

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)

Did this answer your question?