Airtable formula field reference

What is an Airtable formula field?

Note

• Formulas in Airtable run automatically on every record and can't be overridden manually.

• Formula field output is read-only. The value updates instantly whenever the referenced fields change.

A formula field automatically calculates a value for every record in a table using a formula you write. Formula fields are similar to spreadsheet formulas — they can do math, compare values, manipulate text, work with dates, and combine information from other fields.

Basic syntax used in Airtable

Referencing fields

Note

Field names are case-sensitive and must match exactly, including spaces. If the field is named "Start Date", {start date} or {StartDate} won't work — use {Start Date}.

To reference a specific field in a formula, wrap the field name in curly brackets like: {Field Name}

Operators

Use the following operators to do math and comparisons:

Text values

Note

Most copy-paste operations from messaging apps or word processors (Google Docs, Microsoft Word) use smart quotes, so it's best to type formulas directly in the editor

Only use straight (plain) quotes around text values — "like this". Curly or "smart" quotes ("like this") cause errors.

What should I consider when structuring a formula?

• Functions are followed by parentheses: UPPER({Name})

• Function arguments are separated by commas: DATEADD({Date}, 7, "days")

• Formulas can be nested — a function can wrap another function

• Capitalization of function names doesn't matter: IF and if both work

Conditional logic in Airtable

IF — return different values based on a condition

Note

The argument immediately below (value “if false”) is optional. If left out, the field shows blank when the condition is false.

IF(condition, value_if_true, value_if_false)

IF({Status} = "Complete", "✓ Done", "In progress")

IF({Budget} >= 10000, "Qualified", "Not qualified")

IF with AND / OR — check multiple conditions

IF(AND({Status} = "Active", {Budget} >= 5000), "Priority", "Standard")

IF(OR({Region} = "West", {Region} = "Central"), "US", "Other")

NOT — reverse a true/false value

Use NOT() to check if something is false or unchecked. It works well with checkbox fields:

IF(AND(NOT({Approved}), NOT({Reviewed})), "Needs attention")

Working with checkbox fields in formulas

Note

SWITCH only works when a single field is being compared. If you need to check multiple fields or more complex conditions, use nested IF statements instead. Also, the values being matched against must be plain literals — computed expressions don't work as SWITCH comparison arguments. If you need to match against a computed value (like the result of YEAR()), calculate the simpler value first and switch on that:

// Won't work reliably:
SWITCH(YEAR({Date}), 2026, "This year", 2025, "Last year")

// Works — switch on the difference instead:
SWITCH(YEAR(TODAY()) - YEAR({Date}), 0, "This year", 1, "Last year", "Older")

Checkbox fields store a value of 1 (checked) or 0 / empty (unchecked). In formula conditions, reference them directly — no comparison needed:

IF({Approved}, "Yes", "No")          // reads the checkbox directly
IF(NOT({Approved}), "Pending")       // true when unchecked

When an explicit comparison is needed, use TRUE() or 1 — not the text strings "true", "checked", or "yes", which will never match:

IF({Approved} = TRUE(), "Approved", "Not approved")
IF({Approved} = 1, "Approved", "Not approved")

SWITCH — match 1 field against multiple values

SWITCH is cleaner than stacking multiple nested IF statements when checking a single field against a list of possible values.

SWITCH(
  {Product},
  "Apparel", "Shelby Warehouse",
  "Footwear", "NALC Warehouse",
  "Equipment", "NALC Warehouse",
  "Unknown"
)

The last argument before the closing parenthesis is the fallback — it shows when nothing matches.

Text functions in Airtable

Note

Be sure to use an "&" before each element in your concatenation chain. For example, use {Job #} & "-" & {Project Name}. If you skip the second &, the project name won't be included.

Joining text together

Use & to join text values. Every element in the chain needs its own &:

{Job #} & "-" & {Project Name}

{First Name} & " " & {Last Name}

UPPER, LOWER, PROPER, TRIM

LEN, LEFT, RIGHT, MID

FIND and SEARCH — locate text within text

FIND is case-sensitive; SEARCH is case-insensitive. Both return the position of the first match, or blank if nothing is found.

IF(SEARCH("sodium laureth sulfate", LOWER({Ingredients} & "")), "Contains SLS", "No SLS")

Adding & "" after a field converts it to text, which avoids errors when the field is empty or is a lookup field.

SUBSTITUTE — replace text

SUBSTITUTE({Notes}, "old text", "new text")

To remove all instances of a string, replace it with an empty string "":

SUBSTITUTE({Tags}, "#", "")

REGEX_EXTRACT — pull structured data out of text

Note

If REGEX_EXTRACT can't find a match, it returns an error instead of blank on some field configurations. Wrap it in an IF(REGEX_MATCH(...)) check to handle records where the pattern doesn't exist:

IF(
  REGEX_MATCH({Email Body}, "The Artist, ([^\(]+) \("),
  REGEX_EXTRACT({Email Body}, "The Artist, ([^\(]+) \(")
)

REGEX_EXTRACT uses a regular expression pattern to pull a specific piece of text out of a field. It returns blank if no match is found.

REGEX_EXTRACT({Email Body}, "[A-Za-z0-9._%+\-]+@[A-Za-z0-9.\-]+\.[A-Z|a-z]{2,}")

To extract a name that always appears in the format "The Artist, [Name] ([Email])":

REGEX_EXTRACT({Email Body}, "The Artist, ([^\(]+) \(")

IF(
  REGEX_MATCH({Email Body}, "The Artist, ([^\(]+) \("),
  REGEX_EXTRACT({Email Body}, "The Artist, ([^\(]+) \(")
)

REGEX_REPLACE — find and replace using a pattern

REGEX_REPLACE replaces all text that matches a regular expression pattern with a new string. It's the most powerful text-replacement tool in Airtable — use it when `SUBSTITUTE` isn't flexible enough.

REGEX_REPLACE({Notes}, "\\s+", " ")   // collapse multiple spaces into one
REGEX_REPLACE({Text}, "[0-9]+", "#")  // replace all numbers with #

To delete lines of text that start with a specific word (e.g., removing annotation lines from imported content):

REGEX_REPLACE({Body}, ".*Highlight.*\n\n?", "")

To remove all content after a specific character:

REGEX_REPLACE({Email}, "@.*", "")   // strips the domain from an email address

Padding numbers with leading zeros

To format a number as a fixed-width string (e.g., "INC-0042"):

"INC-" & REPT("0", 4 - LEN({Incident ID} & "")) & {Incident ID}

Date and time functions in Airtable

Note

Don't rely on NOW() for anything requiring precise timing. For time-sensitive automation triggers, consider adding a buffer of at least 15 minutes to account for this delay.

TODAY and NOW

DATEADD — add or subtract time from a date

DATEADD({Start Date}, 7, "days")

Supported units: "days", "weeks", "months", "years", "hours", "minutes", "seconds". Use the plural form.

DATEADD({Created}, 30, "days")     // 30 days from creation
DATEADD({Due Date}, -1, "weeks")   // 1 week before due date

DATETIME_DIFF — calculate the time between 2 dates

DATETIME_DIFF({End Date}, {Start Date}, "days")

Returns a number — positive if the second date is later, negative if it's earlier. To get hours as a decimal:

DATETIME_DIFF({End Time}, {Start Time}, "minutes") / 60

WORKDAY — skip weekends when calculating due dates

WORKDAY({Start Date}, 10)

Returns the date that's 10 working days (Monday–Friday) after {Start Date}. To subtract working days, use a negative number.

DATETIME_FORMAT — display a date in a specific format

Note

DATETIME_FORMAT always returns text, not a date — meaning the formula field can't be used in date filters or further date calculations. If the goal is to format how a date displays rather than extract a string, set the date field's built-in display format instead. If you need both — a true date for filtering and a specific text format for display — use 2 separate fields.

DATETIME_FORMAT({Date}, "MMMM D, YYYY")  // April 6, 2026
DATETIME_FORMAT({Date}, "YYYY-MM-DD")    // 2026-04-06
DATETIME_FORMAT({Date}, "M/D/YY")        // 4/6/26

DATETIME_PARSE — convert a text string into a date

DATETIME_PARSE("2026-04-06", "YYYY-MM-DD")

Use this when a date is stored as text and needs to be treated as a real date for calculations.

SET_TIMEZONE — display a date in a specific timezone

DATETIME_FORMAT(SET_TIMEZONE({Timestamp}, "America/Los_Angeles"), "M/D/YYYY h:mma")

Airtable uses IANA timezone names (e.g., "America/New_York", "Europe/London", "Asia/Tokyo").

Comparing dates

Comparing dates

Dates can be compared with <, >, =, etc. The most common pattern is checking whether a date is overdue:

• TODAY() and IS_SAME() evaluate against UTC midnight, not the user's local time. For users in timezones significantly behind UTC (e.g., US Pacific Time), records created or updated in the evening may not yet show as "today" because UTC has already rolled over to the next day. If this matters for the workflow, use SET_TIMEZONE() to anchor the calculation to a specific local timezone.

• When comparing a date field to a hardcoded date value in a formula, always use ISO format ("YYYY-MM-DD"). Other formats like "MM/DD/YYYY" are not reliably parsed and will produce errors or incorrect results:

  // Correct
  IF({Date} = "2026-04-06", "Match")
  
  // Will not work reliably
  IF({Date} = "4/6/2026", "Match")

• If {Due Date} is a lookup or rollup field rather than a direct date field, date comparisons can produce unexpected results. Wrap the field in DATETIME_PARSE() to convert it to a real date value first:

  IF(DATETIME_PARSE({Due Date}) < TODAY(), "Overdue", "On track")

• Formula fields that copy or transform a date can sometimes display a date one day earlier than the source field due to timezone handling. If this happens, open the formula field settings, click Edit field, and enable the "Use the same time zone for all collaborators" (GMT) option. This typically resolves the offset.

IF({Due Date} < TODAY(), "Overdue", "On track")

IS_AFTER() and IS_BEFORE() are cleaner alternatives for date comparisons — they return true or false and are especially useful inside IF and automation trigger formulas:

IS_AFTER({End Date}, TODAY())        // true if End Date is in the future
IS_BEFORE({Start Date}, NOW())       // true if Start Date has already passed

IF(IS_AFTER({Expiry Date}, TODAY()), "Active", "Expired")

IS_SAME() checks whether 2 dates are equal at a specific level of precision — day, month, or year. It's more reliable than = for date comparisons because it ignores time components:

IS_SAME({Date}, TODAY(), "day")    // true if Date is today (ignores time)
IS_SAME({Date}, TODAY(), "month")  // true if Date is in the current month
IS_SAME({Date}, TODAY(), "year")   // true if Date is in the current year

IF(IS_SAME({Order Date}, TODAY(), "day"), "Today's order", "Past order")

Extracting parts of a date

Airtable numeric functions

Note

Multiplying or dividing decimal numbers can produce results with many decimal places due to floating point precision (e.g., 1.1 * 3 might return 3.3000000000000003). Always wrap the result in ROUND() when displaying currency, percentages, or any value where decimal precision matters:

ROUND({Price} * {Quantity}, 2)

To guard against dividing by zero:

IF({Count} = 0, 0, {Total} / {Count})

IF(
  {End Date},
  DATETIME_FORMAT({Start Date}, "M/D") & "–" & DATETIME_FORMAT({End Date}, "M/D/YYYY"),
  DATETIME_FORMAT({Start Date}, "M/D/YYYY")
)

IF(
  HOUR({Order Time}) < 16,
  WORKDAY({Order Date}, 1),
  WORKDAY({Order Date}, 2)
)

IF(
  {Due Date} = BLANK(), "No due date",
  IF({Completed}, "✓ Complete",
  IF({Due Date} < TODAY(), "Overdue",
  IF(DATETIME_DIFF({Due Date}, TODAY(), "days") <= 3, "Due soon",
  "On track"
  )))
)

DATETIME_DIFF({End Time}, {Start Time}, "minutes") / 60

ABS(DATETIME_DIFF({End Time}, {Start Time}, "minutes") / 60)

WORKDAY({Start Date}, 10)

DATETIME_FORMAT({Date}, "dddd, MMMM D, YYYY")

"INC-" & REPT("0", 4 - LEN({Autonumber} & "")) & {Autonumber}

{Client} & " — #" & {Order Number}

REGEX_EXTRACT({Body}, "[A-Za-z0-9._%+\-]+@[A-Za-z0-9.\-]+\.[A-Z|a-z]{2,}")

SWITCH(
  {Genre},
  "Country", "Americana",
  "Folk", "Americana",
  "Latin", "Americana",
  "Songwriter", "Americana",
  "Acoustic", "Americana",
  "Other"
)

IF({Budget} >= 10000, "Qualified", "Not qualified")

IF(AND(NOT({Approved}), NOT({Reviewed})), "Needs review", "OK")

Formatting Airtable formula output

Note

Date formatting is only available when every possible output of the formula is a date value. If the formula ever returns text (like "N/A" or "Missing"), the date formatting option disappears. Use BLANK() instead of text for the "else" case to keep date formatting available.

Formula fields support several output format options in the field settings, including number formatting, currency, percentages, date display, and "format as single select." These are configured after saving the formula — click the field name and select Edit field to access them

When using "format as single select," the formula's output must exactly match one of the defined options. If the output doesn't match any option, the field shows blank.

Using formula fields with Airtable automations

Note

• Automation actions don't accept formulas typed directly into action fields. If an automation action includes a date field (like setting a due date on a new record to "start date + 7 days"), the calculated date must come from a formula field on the triggering record — not a formula typed inline in the action.

• Bases can go inactive when not in use, and automations that depend on NOW()-based formula fields may not fire if the base has been idle for a long time. If reliability is critical, add a scheduled "keep-alive" automation (e.g., a daily automation that finds records and does a minimal update) to keep the base active and ensure formula fields recalculate on schedule.

One of the most common uses for formula fields is bridging the gap between calculated values and automation triggers. Automations can't run formulas directly — but they can watch a formula field and fire when its output changes.

Airtable formula-as-trigger pattern

The most reliable way to trigger an automation on a date calculation is to create a formula field that outputs a signal value when the condition is true, then set the automation to trigger "when a record matches conditions" on that field. For example, to trigger an automation 7 days after a start date:

1. Add a formula field (e.g., "7-Day Follow-up Date"):    

   DATEADD({Start Date}, 7, "days")

2. Add another formula field (e.g., "Trigger Follow-up") that signals when today has reached that date:    

   IF(IS_AFTER(TODAY(), {7-Day Follow-up Date}), "Ready")

3. Set the automation trigger to: When record matches conditions → "Trigger Follow-up" is "Ready".

4. Add the automation action (send an email, create a record, etc.) and set any date fields to the value of the formula field from step 1.

Testing date-based automations

When testing an automation whose trigger is "when a formula field equals X" or "when a date is today," the automation won't fire during testing on a future date. To test it: temporarily set a record's date field to today so the formula evaluates as true, then run the test. Remove the temporary date after testing.

Passing a calculated date into an automation action

After setting up the automation trigger, use the formula field's value in the action by selecting it as a dynamic field reference — not by typing the formula again. In the action setup, click the blue + button next to the field and select the formula field from the trigger record.

Airtable formula limitations

• Airtable formulas do not offer a "locked cell reference" like Excel's $ syntax. Formulas in Airtable can't lock a reference to a specific record the way Excel locks a cell with $B$2. If a constant value (like a national average or tax rate) needs to be shared across all records, either hardcode it directly in the formula — {Score} - 1555 — or store it in a single-record reference table, link all records to it, and pull the value in via a lookup field.

• Formulas can't rank records. There's no way to calculate a rank (e.g., "3rd highest view count") using a formula field alone, because formulas can't compare a record's value against other records. Ranking requires a script, an external tool, or a manually maintained sort order.

• Formulas can't evaluate formulas stored as text. It's not possible to store a formula expression in a text field and have another formula field execute it dynamically. Airtable doesn't support eval-style formula execution. If the calculation logic needs to vary per record, use an IF or SWITCH to hardcode each variant in the formula field itself.

• Computed fields are read-only. Formula, rollup, and lookup fields can't be set by automations or via the API. If an automation is returning "cannot modify a computed field," change the primary field or the target field to a non-computed type.

• Formulas can't reference other tables. They can only reference fields in the same table. Use linked records and rollup fields to bring in data from another table.

• Formulas can't store state. Formula output is always recalculated from scratch — it's not possible to capture a value at a point in time using a formula field alone. Use an automation to write the value to a text or number field if a snapshot is needed.

• No SUMPRODUCT equivalent for a single table. To calculate a weighted average across all records (e.g., sum of implied cost ÷ sum of NAV), use a helper table with linked records and rollup fields, then a formula field in that summary table.

• No HYPERLINK() function. Formula fields output plain text, so URLs in a formula field aren't clickable. Use a button field to create a clickable URL built from a formula, or enable rich text on a long text field for formatted links.

• Formulas can't search linked records by record ID. Using FIND("rec123abc", {Linked Field}) to locate a record by its ID in a linked record field doesn't work — Airtable formula syntax can't match record IDs in linked fields directly. The workaround is to search by the primary field value of the linked record instead: FIND("Record Name", {Linked Field}). Alternatively, create a rollup field that joins all linked record IDs as a text string, then use FIND() against that rollup field.

• Airtable doesn't support all Excel/Google Sheets functions. Common functions that don't exist in Airtable: XLOOKUP, VLOOKUP, INDEX/MATCH, COUNTIF, SUMIF, and AVERAGEIF. These cross-record aggregations require rollup fields or interface elements instead.

Using Airtable formula syntax with external tools

Note

When passing a date value from an external tool (like Make's {{now}}) into an Airtable formula filter, the date must be formatted as an ISO string (YYYY-MM-DD) before being injected into the formula string. Passing a raw datetime object will cause a formula parse error. In Make, wrap the value with formatDate(now; "YYYY-MM-DD") before using it in the formula.

The same formula syntax used in formula fields also applies when filtering records via the Airtable API or automation platforms like Make, Zapier, or n8n. These tools expose a "formula filter" box (sometimes called filterByFormula) in their Airtable modules that accepts Airtable formula syntax to control which records are returned.

The rules are identical: field names in {}, straight quotes around text values, and standard Airtable functions. A few patterns come up frequently in external tool contexts:

FAQs

{Client} & ": order #" & {Order No.}