+1 (415) 612-6492
BaseBrainers

Airtable Formulas: Conditional Logic, Date Maths, and Rollup Patterns That Work

Most Airtable bases are held together by formula fields, and most formula fields are written once, by someone in a hurry, and never revisited. That is where the slow bugs come from: a status that never flips, a date that is a day off for half the team, a rollup that quietly counts deleted records. This tutorial is a working reference for the formula patterns we use on client bases every week, with the traps that cause silent wrong answers.

We will build them against a simple Projects table with fields Name, Start date, Due date, Completed on (date), Status (single select), Budget (currency), Owner (collaborator), and a link to Tasks.

The three rules that prevent most formula bugs

  1. Blank is not zero. An empty number field is blank, and blank behaves differently from 0 in comparisons. Wrap anything that can be empty: IF({Budget} = BLANK(), 0, {Budget}).
  2. Formula fields return a type. A formula that returns a date in one branch and text in another gets coerced to text, and then date functions stop working on it. Keep every branch of an IF() the same type.
  3. Formulas recalculate; they do not remember. TODAY() moves. If you need the value on the day something happened, that is an automation writing to a date field, not a formula. This is the single most common cause of "the report changed since yesterday".

Pattern 1: readable conditional logic with SWITCH

Nested IF() statements are the usual way people write status logic, and they become unreadable at the third level. When you are testing one field against fixed values, use SWITCH():

SWITCH(
  {Status},
  "Not started", "⚪️ Queued",
  "In progress", "🔵 Active",
  "Blocked",     "🔴 Needs attention",
  "Done",        "🟢 Complete",
  "❓ Unknown status"
)

The final argument is the default. When conditions are more than equality checks, IF() nesting is unavoidable, but you can keep it flat by ordering from most specific to least:

IF({Completed on}, "Done",
IF(AND({Due date}, IS_BEFORE({Due date}, TODAY())), "Overdue",
IF(AND({Due date}, DATETIME_DIFF({Due date}, TODAY(), 'days') <= 7), "Due this week",
"On track")))

Note the AND({Due date}, ...) guard on each branch. Without it, records with no due date fall into whichever comparison blank happens to satisfy, and you get phantom overdue items.

Pattern 2: date maths that survives time zones

Date fields in Airtable store a timestamp. If the field is not configured to use the same time zone as your readers, DATETIME_DIFF results shift by a day around midnight. Two habits fix this:

  • Turn on Use the same time zone for all collaborators on date fields used in calculations, and set it to the business's operating time zone.
  • Use SET_TIMEZONE() when formatting for display: DATETIME_FORMAT(SET_TIMEZONE({Due date}, 'Europe/London'), 'DD MMM YYYY').

Useful building blocks:

Days until due:      DATETIME_DIFF({Due date}, TODAY(), 'days')
Working duration:    WORKDAY_DIFF({Start date}, {Completed on})
Deadline + 10 days:  WORKDAY({Start date}, 10)
Month bucket:        DATETIME_FORMAT({Start date}, 'YYYY-MM')
Age in whole months: DATETIME_DIFF(TODAY(), {Start date}, 'months')

DATETIME_DIFF truncates rather than rounds, so a gap of 47 hours is "1 day". If you need a rounded figure, compute in hours and divide: ROUND(DATETIME_DIFF({Completed on}, {Start date}, 'hours') / 24, 1).

The 'YYYY-MM' month bucket is worth calling out: it is the cheapest way to get a groupable, sortable period field for reporting, and it sorts correctly as text, unlike 'MMM YYYY'.

Pattern 3: rollups that count the right thing

A rollup runs an aggregation over linked records. The trap is that the rollup ignores your view filters — it sees every linked record — so "open tasks" needs the filter inside the rollup, not in a view.

Roll up a Tasks field with a condition set on the rollup ("Only include linked records that meet certain conditions"), then choose the aggregation:

COUNTALL(values)            all linked tasks
COUNTA(values)              linked tasks where the field is non-empty
SUM(values)                 total estimated hours
ARRAYJOIN(ARRAYUNIQUE(values), ", ")   distinct owners, as text
ARRAYCOMPACT(values)        strips blanks before another function

A percent-complete rollup that behaves when a project has no tasks yet:

IF(
  {Task count} = 0,
  BLANK(),
  ROUND({Completed task count} / {Task count}, 2)
)

Returning BLANK() rather than 0 matters here: zero would drag your average completion metric down with empty projects, whereas blank is excluded from averages.

Lookup vs rollup: a lookup brings back the raw list of values and is an array; a rollup collapses that array to one value. If you want to filter or compare on the child data, roll it up. If you only want to see it, look it up — and remember that a lookup of a number is not a number, so wrap it: SUM({Lookup of hours}) inside a formula field.

Pattern 4: text cleanup and matching keys

Deduplication and integrations both depend on a normalised key. Build one as a formula field:

LOWER(TRIM(SUBSTITUTE({Email}, " ", "")))

Domain extraction from an email, useful for grouping inbound leads by company:

IF(
  FIND("@", {Email}),
  LOWER(RIGHT({Email}, LEN({Email}) - FIND("@", {Email}))),
  ""
)

REGEX_EXTRACT is shorter when it is available to you:

REGEX_EXTRACT({Email}, "@(.+)$")

For a human-friendly record label that never comes out blank:

IF({Name}, {Name}, "Untitled " & DATETIME_FORMAT(CREATED_TIME(), 'YYYY-MM-DD'))

Set that as the primary field and every linked-record chip in the base becomes readable — a small change with a disproportionate effect on how usable the base feels.

Pattern 5: formulas as automation triggers

A common and slightly hidden technique: rather than triggering an automation on "when record matches conditions" with a long condition list, put the logic in a formula field that returns a single flag, and trigger on that field's value.

IF(
  AND(
    {Status} = "In progress",
    {Due date},
    DATETIME_DIFF({Due date}, TODAY(), 'days') <= 2,
    NOT({Reminder sent})
  ),
  "SEND",
  ""
)

The automation then triggers when Reminder flag becomes SEND. The logic lives in one readable place, you can eyeball which records are queued by filtering the grid, and the NOT({Reminder sent}) clause makes the trigger idempotent — the flag clears itself once the automation checks the box.

Be aware of one thing: formula fields that depend on TODAY() or NOW() recalculate on Airtable's own schedule, not the instant midnight passes, so time-critical reminders are better driven by a scheduled automation that reads the flag than by the trigger firing on its own.

Debugging a formula that returns #ERROR!

Work through it in this order:

  1. Division by blank. Guard with IF({Denominator} = 0, BLANK(), ...).
  2. Type mismatch. Concatenating a date with & produces a raw timestamp string; wrap it in DATETIME_FORMAT() first.
  3. A branch returning the wrong type. Make every IF() branch return the same kind of value; use BLANK() for the empty case, not "", when the field should stay numeric or date typed.
  4. Field renamed via API. Formulas track field IDs in the UI, but a formula pasted in from elsewhere references names literally and breaks.
  5. Circular references. A rollup that feeds a formula that feeds the rollup source will error out. Break the loop with a static field written by an automation.

Build the formula in pieces: create a scratch formula field, return one sub-expression at a time, confirm the values look right in the grid, then assemble.

Where formulas should stop

Formulas are cheap to add and expensive to accumulate. Long chains of formula fields that reference other formula fields across links are the top cause of sluggish bases — every edit recalculates the chain. Once a calculation crosses three or four dependent hops, or once you find yourself simulating a loop, it is time for an automation script writing a stored value instead.

If your base has grown a formula layer nobody fully understands any more, that is a normal outcome of a base that has been useful for a few years. Talk to us — an audit that documents and simplifies the formula layer usually pays for itself in the performance improvement alone.