# Airtable formulas: a cheat sheet for text, dates and logic, with the reference's own examples

> The Airtable formulas you use most — joining and cleaning text, formatting and comparing dates, IF and SWITCH — with examples from Airtable's reference.

Published 2026-11-07 · https://shareglint.com/en/guides/airtable-formulas

Airtable formulas are spreadsheet-style expressions in a **formula field**: they read other fields of the same record and compute a value — a joined name, a clean headline, a formatted date, a status label. This cheat sheet covers the functions that do most of the everyday work, grouped by job. Every function on this page comes from Airtable's [formula field reference](https://support.airtable.com/docs/formula-field-reference), as we read it on 2026-11-07, and outputs marked "⇒" are the reference's own examples. The combinations we suggest (a length limit, days to go, a status badge) use only those functions, but we did not run them in a base ourselves — test them on a copy of your table first.

Fields are referenced by name in curly braces — `{Due Date}` — and text is written in double quotes. A formula field's result is read-only; to change data, edit the fields it reads.

## Joining text

The `&` operator joins values; `CONCATENATE()` does the same with commas:

```text
Name & " - " & Age                    ⇒ Jane - 28
CONCATENATE("Bob", " - ", 43)         ⇒ Bob - 43
```

For a list of values — a linked record's names, a multiple-select field — `ARRAYJOIN()` joins them with a separator:

```text
ARRAYJOIN(values, "; ")               ⇒ 1; 2; 3
```

A quote mark inside text is escaped with a backslash: `CONCATENATE("\"", {Name}, "\"")` ⇒ `"Bob"`.

## Cleaning and trimming text

Data typed by people or imported from forms is untidy. These functions clean it before it goes anywhere else:

| Function | Does | Reference example |
|---|---|---|
| `TRIM(string)` | Removes spaces at the start and end | `TRIM(" Hello! ")` ⇒ `Hello!` |
| `UPPER()` / `LOWER()` | Changes case | `UPPER("Hello!")` ⇒ `HELLO!` |
| `LEFT(string, n)` | First n characters | `LEFT("quick brown fox", 5)` ⇒ `quick` |
| `LEN(string)` | Number of characters | `LEN("quick brown fox")` ⇒ `15` |
| `SUBSTITUTE(string, old, new)` | Replaces text | `SUBSTITUTE("gold mold", "old", "et")` ⇒ `get met` |
| `REGEX_EXTRACT(string, regex)` | First match of a pattern | `REGEX_EXTRACT("Hello World", "W.*")` ⇒ `World` |
| `REGEX_MATCH(string, regex)` | 1 if it matches, else 0 | `REGEX_MATCH("Hello World", "Hello.World")` ⇒ `1` |
| `ENCODE_URL_COMPONENT(string)` | Makes text safe in a URL | `ENCODE_URL_COMPONENT("chicken & waffles")` ⇒ `chicken%20%26%20waffles` |

Two combinations come up all the time:

- **A length limit with an ellipsis** — a headline that must fit a card or a subject line:

  ```text
  IF(LEN({Title}) > 60, LEFT({Title}, 57) & "…", {Title})
  ```

- **Digits only** — from a phone number or a price typed with symbols, as the reference suggests: `VALUE(REGEX_REPLACE({Price}, "\\D", ""))`.

## Dates

| Function | Does | Reference example |
|---|---|---|
| `DATETIME_FORMAT(date, 'format')` | Date as text in a format | `DATETIME_FORMAT("07/10/19", "YYYY")` ⇒ `2019` |
| `DATEADD(date, n, 'unit')` | Adds days, hours… | `DATEADD("07/10/19", 10, "days")` ⇒ `2019-07-20` |
| `DATETIME_DIFF(date1, date2, 'unit')` | Difference in a unit | `DATETIME_DIFF("04/06/2019 12:00", "04/05/2019 11:00", "hours")` ⇒ `25` |
| `IS_BEFORE(date1, date2)` | 1 if date1 is earlier | `IS_BEFORE("1/1/1979", "1/1/2000")` ⇒ `1` |
| `DATESTR(date)` | Date as `YYYY-MM-DD` | `DATESTR("12/13/21")` ⇒ `2021-12-13` |
| `TODAY()` / `NOW()` | Current date / date and time | — |

**Format specifiers** follow Airtable's [list of supported format specifiers](https://support.airtable.com/docs/supported-format-specifiers-for-datetime-format): `MMMM D, YYYY` gives "June 8, 2016", `D MMMM YYYY` the day first.

**Another language.** `SET_LOCALE` changes the language of month and day names: `DATETIME_FORMAT(SET_LOCALE("07/10/19", 'es'), 'LLLL')` ⇒ `miércoles, 10 de julio de 2019 0:00`. `SET_TIMEZONE` does the same for the time zone, so an event time shows in the venue's time, not the server's.

**Days to go**, for a countdown or a reminder: `DATETIME_DIFF({Event date}, TODAY(), 'days')`. The reference notes that `DATETIME_DIFF` returns whole numbers.

**When `NOW()` updates.** Not continuously. Airtable says it updates "when the formula is recalculated, when a base is loaded, or otherwise roughly every 15 minutes when a base is open"; with the base closed, about every hour and only when time-dependent automations or syncs depend on it. Do not build anything that needs to-the-minute accuracy on it.

## Logic: IF, SWITCH, BLANK

```text
IF({Sales} > 50, "Win", "Lose")
IF({Price} > 1000000, "Wow, that's pretty expensive", BLANK())
```

`IF(expression, value1, value2)` returns the first value when the expression is true, the second otherwise; IFs can be nested. `BLANK()` returns an empty value, so the field counts as empty for filters and other formulas.

When one field decides among several outcomes, `SWITCH` reads better than nested IFs:

```text
SWITCH({Status},
  "To Do", "Get this started!",
  "In Progress", {Due Date},
  "Done", "Work complete!")
```

The last unpaired argument, if you give one, is the default. The comparison operators are the usual ones — equals `=`, not equals `!=`, greater than `>`, and the less-than and or-equal forms — and conditions combine with `AND(…)` and `OR(…)`.

## Formulas, automations or scripts?

Formulas are the first tool to reach for, but not the only one, and choosing the right one saves a lot of contorted expressions.

- **A formula field** computes a value from fields of the same record, every time those fields change. It is ideal for anything that can be described as "this record's text, cleaned up or combined": a display name, a headline with a length limit, a date in another format, a label chosen from a status. It cannot change other fields, look at other records except through linked-record lookups and rollups, or call anything outside the base.
- **An automation** reacts to an event — a record is created, a field changes, a form is submitted — and does something: updates a record, sends an email, posts a message, runs a script. Use it when something should happen, not just be shown.
- **A script** (inside an automation, or the scripting extension) is for logic that formulas cannot express: loops over many records, calls to other services, writing results back into fields.

A good rule of thumb: if you find yourself nesting a fifth IF, or copying the same formula into several fields with small changes, step back and ask whether a lookup table in a linked table, a SWITCH, or a short script would be clearer for the next person who opens the base.

## Formulas that prepare content

Formula fields are a good place to shape text before another tool uses it — a newsletter, a CMS, a social post, an image. Three patterns from content bases:

1. **A display headline**: the title trimmed, limited to 60 characters with an ellipsis, as above.
2. **A date line in the reader's language**: `DATETIME_FORMAT(SET_LOCALE({Date}, 'de'), 'D. MMMM YYYY')`.
3. **A status badge**: `SWITCH({Stage}, "Live", "NOW LIVE", "Soon", "COMING SOON", "")`.

Keep the original fields untouched and do the shaping in formula fields: when a rule changes, you change one formula, and every record follows.

## Where images come in

Airtable formulas work on text, numbers and dates; they do not draw images. If your base holds content that should become images — an event card per session, a quote card per testimonial, a product card per item — the formula fields above make the text right, and something else renders the picture.

That is where we fit, and only there: Shareglint is not an Airtable app and adds nothing inside Airtable. An Airtable automation with a short script (or Zapier, Make or n8n) sends a record's fields — the formula fields included — to our API, gets back an image URL, and writes it into an attachment field; Airtable then downloads the file from that URL. The [Airtable recipe](https://shareglint.com/en/integrations/airtable) has the script and the table layout, and works with the formula fields from this page: map a template's title field to the 60-character headline field and its date field to the localized date field.

## Common mistakes

- **Smart quotes.** Copying a formula from a word processor can turn `"` into `“ ”`, which breaks the formula. Type quotes in the formula editor.
- **Field names.** A formula copied from a tutorial or another base needs your base's exact field names inside the curly braces.
- **Dates as text.** `DATETIME_FORMAT` returns text; compare and add with the original date field, not the formatted one.
- **Too much in one formula.** Split long logic into helper formula fields; each one is easier to check than one giant expression.

## FAQ

### How do I combine text from two fields in Airtable?

Use the & operator or CONCATENATE: {Name} & " - " & {City}, or CONCATENATE({Name}, " - ", {City}). Airtable's reference shows Name & " - " & Age giving "Jane - 28".

### How do I format a date in an Airtable formula?

DATETIME_FORMAT({Date}, 'format'), for example DATETIME_FORMAT({Date}, 'D MMMM YYYY'). For another language, wrap the date in SET_LOCALE: Airtable's reference shows DATETIME_FORMAT(SET_LOCALE("07/10/19", 'es'), 'LLLL') giving "miércoles, 10 de julio de 2019 0:00".

### What is the difference between IF and SWITCH in Airtable?

IF(expression, value1, value2) tests one condition and can be nested. SWITCH(expression, pattern, result, …, default) compares one value against a list and is easier to read than several nested IFs.

### How often does NOW() update in Airtable?

According to Airtable, when the formula is recalculated, when the base is loaded, or roughly every 15 minutes while the base is open; with the base closed, about every hour and only if time-dependent automations or syncs depend on it.