2.4Formulas & calculated fields
The essentials in 30 seconds
- From storage to intelligence: the formula alone calculates statuses, delays and totals — zero manual updates.
- Functions by families: logic (IF), text (CONCATENATE), dates (DATETIME_DIFF), numbers (ROUND) — combine simple pieces.
- Calculated fields drive the rest: filters, views and automations read your formulas — the base itself flags urgency.
Ongoing mini-case — Lina automates the « overdue » badges
Lina is tired of spotting late files by eye: she creates a Total field (quantity × price), a delay field in days with DATETIME_DIFF, and a calculated IF status showing « En retard » when the deadline is passed and the file is not closed. Her filtered « En retard » view fills itself. In 2.5, this same view will feed the team dashboard.
Continuation of thread 2.3: Lina's linked tables become intelligent — and continue in 2.5.
1. From storage to intelligence
Formulas move a base from « storage » to « intelligence ». A formula field automatically calculates a value from the other fields of the record: a status (« overdue » if the deadline is passed), a delay (days between two dates), a total (quantity × price), a concatenated label. The syntax recalls Excel, with functions by families: logic (IF, AND, OR), text (CONCATENATE), dates (DATETIME_DIFF, TODAY), numbers (ROUND). Fields are referenced by name in braces, like {Échéance}.
2. Automatic recalculation
The decisive advantage: the value recalculates itself and stays always correct, without intervention. You thus automate indicators — an « urgent » badge, a countdown, a score — that then drive filters, views and automations: Lina's « En retard » view fills without her touching anything. A few well-designed formulas replace hours of manual updates. No need to be a developer: combine simple functions, test, adjust.
3. First step towards low-code
Writing formulas gently moves you from no-code to low-code: a little logic, a lot of effect. Hygiene rules: start simple (a multiplication, an IF) then add complexity; test on a few rows before extending; never enter by hand a value a formula would calculate alone; and do not confuse formula (calculates in the same row) and rollup (calculates across a relationship, see 2.3). A formula unreadable in one go = a formula to split into steps.
🔎 Concrete example — 🔎 Concrete example — Lina's « overdue » status
IF logic in action, with valid Airtable syntax:
Total = {Quantité} * {Prix unitaire}
Délai = DATETIME_DIFF({Échéance}, TODAY(), 'days')
Statut = IF(AND({Échéance}, {Échéance} < TODAY(), {Statut} != 'Fait'), 'En retard', 'OK')Reading: a base that itself flags what is urgent.
Lesson: reference fields by name in braces.
Section vocabulary
- Formula field
- Airtable field whose value is automatically calculated from the other fields of the same record.
Simple example: Lina's Total: {Quantité} * {Prix unitaire}.
What it is not: an amount entered by hand on each row.
Reflex: deducible value = formula, never entry.
- Function
- Calculation brick of a formula (IF, CONCATENATE, DATETIME_DIFF, ROUND), organized by families: logic, text, date, number.
Simple example: DATETIME_DIFF({Échéance}, TODAY(), 'days') for Lina's delay.
What it is not: a mental calculation recopied into the cell.
Reflex: one simple function per need, then combine.
- IF (condition)
- Logic function returning one value if a condition is true, another otherwise — the basis of calculated statuses.
Simple example: IF(AND({Échéance} < TODAY(), {Statut} != 'Fait'), 'En retard', 'OK').
What it is not: a status hand-picked in a list.
Reflex: binary status = think IF.
- DATETIME_DIFF
- Function calculating the gap between two dates (in days, hours…) — useful for delays and countdowns.
Simple example: Lina's deadline countdown in days.
What it is not: dates compared by eye one by one.
Reflex: delay or lateness = DATETIME_DIFF.
- Calculated field
- General term for a field whose value comes from a calculation (formula, rollup, lookup) rather than entry.
Simple example: Badges, delays and totals in Lina's base.
What it is not: a field filled manually each week.
Reflex: spot everything that recalculates to stop entering it.
In practice — In practice — Writing your first formulas
- Create a « Total » formula field = {Quantité} * {Prix unitaire} on a line or file table.
- Create a « Delay (days) » field = DATETIME_DIFF({Échéance}, TODAY(), 'days').
- Create a calculated status with IF: IF(AND({Échéance}, {Échéance} < TODAY(), {Statut} != 'Fait'), 'En retard', 'OK').
- Use this status in a filtered view (« En retard »): the formula now drives your dashboard.
Ready-to-say script — suggesting a formula instead of entry (20 s)
« Rather than updating this status by hand each week, I create a formula field: if the deadline is passed and the file is not closed, it shows En retard by itself. We enter nothing more, the filtered view fills automatically — and it will never err. »
Say with the formula tested on a few rows before your eyes; show the automatic recalculation.
Copy-paste template — 4 ready-to-use Airtable formulas
Total: {Quantité} * {Prix unitaire}
Rounded total: ROUND({Quantité} * {Prix unitaire}, 2)
Delay (days): DATETIME_DIFF({Échéance}, TODAY(), 'days')
Status: IF(AND({Échéance}, {Échéance} < TODAY(), {Statut} != 'Fait'), 'En retard', 'OK')
Label: CONCATENATE({Nom}, ' — ', {Statut})Quotes and invoicing
Ex-VAT total as formula, VAT and incl-VAT totals as chained formulas — ROUND(..., 2) for clean amounts.
SME Algeria
Lina calculates her file processing times in days and drives follow-ups on them.
Team tracking
Remaining load = DATETIME_DIFF between deadline and today, IF badge for urgencies.
Catalogue
Automatic labels with CONCATENATE (name + status + reference) for always up-to-date lists.
✓ Best practices
- Start simple (a multiplication, an IF) then add complexity.
- Use calculated fields to drive filters and views.
- Test the formula on a few rows before extending it.
✗ Common pitfalls
- Confusing formula (same row) and rollup (linked records).
- Entering by hand a value a formula would calculate alone.
- Stacking an unreadable formula in one go (proceed stepwise).
Key takeaways
- A formula field automatically calculates a value from the other fields.
- Functions by families: logic (IF, AND, OR), text (CONCATENATE), dates (DATETIME_DIFF), numbers (ROUND).
- The value recalculates itself and stays always correct (statuses, delays, totals).
- Calculated fields then drive filters, views and automations.
48h challenge — apply tomorrow
Create 3 real calculated fields in your base (a total, a delay, an IF status) with the template above, then build the filtered view they drive. Remove any manual entry they replace.
- I can write a valid IF and DATETIME_DIFF.
- I can distinguish formula (row) and rollup (relationship).
- I have 3 calculated fields driving a filtered view.
Frequently asked questions
Do you need to know Excel to write Airtable formulas?
It helps, because the logic is very close (functions, parentheses, field references), but it is not essential. Airtable formulas reference fields by name (in braces), which is often more readable than Excel. Start with simple formulas (a multiplication, an IF) and add complexity progressively. The editor shows errors and suggests functions. Even with no Excel background, you quickly become autonomous on common cases: totals, delays, conditional statuses. That is exactly where you gently move from no-code to low-code, painlessly.
What is the difference between a formula field and a rollup?
A formula field calculates from the other fields of the same record (Quantity × Price of this row). A rollup calculates from the linked records of another table (sum of rows linked to this order). In short: the formula looks « inside the row », the rollup looks « across a relationship ». They are often combined: a rollup aggregates linked amounts, then a formula applies a rule on top. Knowing which to use depends on data origin: same record → formula; linked records → rollup.
Lina wants a status that updates itself according to the deadline. Which function?