# 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](https://app.teamhood.com/go/settings/fields)

## 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. `IF` treats 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

| 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 as `1234.5` and `2026-01-31`. It does not use the organization's formats.
- Dividing by zero gives an empty result.

## 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`, `HAS` and `ANYOF`.
- 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

| 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 `HAS` or `ANYOF`.
- `ROUND` rounds a half away from zero: 2.5 becomes 3, and -2.5 becomes -3.
- `MIN`, `MAX`, `SUM` and `AVG` give an empty result when any value is empty. A select field that holds no option is left out instead.
- `IF` without `else` gives an empty result when the test is false.
- Both results of `IF` must be the same kind: two numbers, two dates or two texts.

## Dates

- `DATEADD` also takes `'weeks'`, `'months'` and `'years'`. `DATEDIFF` also takes `'weeks'`.
- To subtract days, give a negative number: `DATEADD({Due}, -5, 'days')`.
- A date minus a date is not allowed. Use `DATEDIFF`.
- `DATEADD` adds 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.
- `DATEDIFF` in weeks can give a fraction.

## 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](https://app.teamhood.com/go/settings/regional) See [Change your organization's currency and regional settings](../../get-started/change-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 `date` for a formula that returns a date. Without it, cells show the date as `2026-01-31`, not in the organization's date format.

## 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`, `lowercase` and `formatdate`
- `pow` and `^`
- `{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.

## Next

- [What is a field?](../what-is-a-field/)
- [MCP server](../../integrations/mcp/)