Formula fields
A formula field calculates its value from other fields of the same item. You write the formula in the field’s settings. Open in Teamhood
Syntax rules
Section titled “Syntax rules”- Name a field by its title in braces:
{Hours} * {Rate}. Field titles ignore capital letters. - Test equality with
==and inequality with!=. A single=is not an operator. - There is no
^. Multiply instead:{Size} * {Size}. - Put parentheses around a calculation next to
==,!=or&:100 == ({Price} + {Tax}). - Give each function the values its row in the table below shows. A value in square brackets may be left out, and
…means more values of the same kind may follow. A function with a value too many or too few is a mistake. - Write text in single or double quotes:
'Late'. Write decimals with a dot:0.2. - A formula may start with
=. Teamhood ignores a leading=. - A calculation that uses an empty field gives an empty result.
IFtreats an empty test as false.
The formula editor in the app shows a mistake and does not save the formula. The editor does not catch a single = or a ^. A formula with either one shows empty cells.
While you type, the editor suggests functions, fields after {, and the units a date function takes. Inside a function, the line under the editor shows the values the function takes, with the current one in bold.
Teamhood does not check a formula saved through the API. An AI assistant refuses a formula that uses a function Teamhood does not have, but saves other mistakes, such as a field name that does not exist. Chat in the app checks the whole formula as the editor does before it asks you to allow the field. Most mistakes that are saved leave the formula’s cells empty.
Operators
Section titled “Operators”| Operator | Does | Example |
|---|---|---|
+ - * / | Adds, subtracts, multiplies, divides | {Hours} * {Rate} |
< <= > >= | Compares two numbers | {Spent} > {Budget} |
== != | Tests whether two numbers are equal or not equal | {Done} == {Total} |
& | Joins two values as text | 'Week ' & {Week} |
( ) | Groups a calculation | ({Low} + {High}) / 2 |
- A comparison gives 1 when it is true and 0 when it is false.
- Comparisons work on numbers only. Two dates or two texts are never equal. To compare dates, compare the days between them:
DATEDIFF({Due}, TODAY(), 'days') > 0. *and/come before+and-. The comparisons<,<=,>and>=come last.==,!=and&do not follow this order. To compare or join a whole calculation, put that calculation in parentheses.&joins values as they are stored, such as1234.5and2026-01-31. It does not use the organization’s formats.- Dividing by zero gives an empty result.
What a formula can use
Section titled “What a formula can use”| Reference | Gives |
|---|---|
| A number field | Its number |
| A date field | Its date |
| A select field | The numeric value of the option the item holds |
| A formula field | Its result |
{Created on} | The day the item was created |
{Updated on} | The day the item last changed |
- Each select option has a numeric value from 0 to 999. Turn on Numeric values in the field’s options to see and change the values.
- A select field with Allow multiple turned on works only as a direct argument of a function. The functions are
SUM,MIN,MAX,AVG,ISEMPTY,HASandANYOF. - Text, People, Relation, Lookup, Tracked time, Checkbox and Auto ID fields cannot be used.
- A formula cannot use its own result, directly or through another formula.
- A reference names a field by its title. When you rename a field, change every formula that names it. Teamhood does not change the formulas for you.
Functions
Section titled “Functions”| Function | Gives |
|---|---|
ROUND(x, [places]) | x rounded to that many decimals, or to a whole number without places |
FLOOR(x) | x rounded down to a whole number |
CEIL(x) | x rounded up to a whole number |
ABS(x) | x without its minus sign |
MIN(a, …) | The smallest value |
MAX(a, …) | The largest value |
SUM(a, …) | The total |
AVG(a, …) | The average |
IF(test, then, [else]) | then when the test is not 0 and not empty, otherwise else |
ISEMPTY({Select}) | 1 when the item holds no option |
HAS({Select}, "Option") | 1 when the item holds that option |
ANYOF({Select}, "A", …) | 1 when the item holds any of those options |
TODAY() | Today’s date |
DATE('2026-01-31') | That date |
DATEADD(date, n, 'days') | The date n days later |
DATEDIFF(later, earlier, 'days') | The days from earlier to later |
YEAR(date), MONTH(date), DAY(date) | That part of the date, as a number |
WEEKDAY(date) | 1 for Monday to 7 for Sunday |
STARTOFMONTH(date) | The first day of the date’s month |
ENDOFMONTH(date) | The last day of the date’s month |
- Function names and option names ignore capital letters.
- An option name that no option has never matches in
HASorANYOF. ROUNDrounds a half away from zero: 2.5 becomes 3, and -2.5 becomes -3.MIN,MAX,SUMandAVGgive an empty result when any value is empty. A select field that holds no option is left out instead.IFwithoutelsegives an empty result when the test is false.- Both results of
IFmust be the same kind: two numbers, two dates or two texts.
DATEADDalso takes'weeks','months'and'years'.DATEDIFFalso takes'weeks'.- To subtract days, give a negative number:
DATEADD({Due}, -5, 'days'). - A date minus a date is not allowed. Use
DATEDIFF. DATEADDadds whole units only: 1.7 days adds 1 day.- Adding a month to 31 January gives a day in early March, because February has no 31st day.
DATEDIFFin weeks can give a fraction.
Formats
Section titled “Formats”The format decides how a number field or a formula field shows its value. The organization’s date format, number format and currency are set in Settings → Organization → Regional. Open in Teamhood See Change your organization’s currency and regional settings.
| Format | Value in the API | Shows |
|---|---|---|
| Number | number | The number |
| Currency | currency | The number with the organization’s currency |
| Percent | percentage | A fraction as a percentage: 0.25 shows as 25% |
| Date | date | A date, for a formula field only |
- A new formula field uses Number until you choose another format.
- In the app, a formula that returns a date always uses Date. A formula that returns text shows Text as its only format.
- Through the API or an AI assistant, set
datefor a formula that returns a date. Without it, cells show the date as2026-01-31, not in the organization’s date format.
Convert a formula from the previous version of Teamhood
Section titled “Convert a formula from the previous version of Teamhood”| Previous version | This version |
|---|---|
{key} or {keyOwn} | {Field title} |
{startDate}, {dueDate}, {budget}, {estimation}, {duration} | Your fields by title, such as {Start}, {Finish}, {Budget}, {Estimate (h)} and {Duration (d)} in a new organization |
{createdDate}, {modifiedDate} | {Created on}, {Updated on} |
a = b | a == b |
add, subtract, multiply, divide | +, -, *, / |
lt, lte, eq, gte, gt | <, <=, ==, >=, >, for numbers only |
concatenate(a, b) | a & b |
adddays(d, n) | DATEADD(d, n, 'days') |
addweeks, addmonths, addyears | DATEADD with 'weeks', 'months' or 'years' |
date1 - date2 | DATEDIFF(date1, date2, 'days') |
date + n | DATEADD(date, n, 'days') |
date - n | DATEADD(date, -n, 'days') |
floor(x, 2), ceil(x, 2) | FLOOR(x * 100) / 100, CEIL(x * 100) / 100 |
datetodays(d) | DATEDIFF(d, DATE('1970-01-01'), 'days') |
daystodate(n) | DATEADD(DATE('1970-01-01'), n, 'days') |
IF, MIN, MAX, ROUND and TODAY keep their names.
In the == and & rows, put a or b in parentheses when it is a calculation. The previous version did not need them.
The previous version read an empty number as 0. In this version, the result is empty. To read an empty number as 0, write IF({Price}, {Price}, 0).
These have no equivalent:
workdays,uppercase,lowercaseandformatdatepowand^{progress},{completedOn}and the planned values, such as{plannedBudget}and{plannedStartDate}{totalLoggedTime}, because a formula cannot use tracked time{…TotalSum}and every other sum of child items- The children’s sum that
{key}read when an item had no value of its own. A formula in this version reads only the item’s own fields.