---
title: 'The Complete Airtable Formulas Cheat Sheet'
description: 'Complete Airtable formula reference — text, numeric, date, logical, and array functions with copy-paste examples for real business use cases.'
canonical_url: 'https://www.business-automated.com/tutorials/airtable-formulas-cheat-sheet'
md_url: 'https://www.business-automated.com/tutorials/airtable-formulas-cheat-sheet.md'
last_updated: 2026-04-04
---

[Airtable](/airtable-consultant) formulas let you calculate, transform, and generate values automatically within your records. Whether you're building a [CRM](/tutorials/how-to-build-crm-in-airtable), tracking project budgets, or creating dynamic status fields, formulas eliminate manual data entry and keep your base consistent.

This cheat sheet covers every major formula category with **copy-paste examples** for real business use cases. Formulas are organized by type — jump to the section you need.

## How Airtable Formulas Work

Every formula references field names wrapped in curly braces: `{Field Name}`. The formula calculates a value automatically whenever the referenced fields change. You can't edit formula results directly — they update based on the source fields.

Key rules:

- **Field names are case-sensitive** and must match exactly, including spaces
- **Text values** use single quotes: `'Active'`, not `"Active"`
- **Empty fields** return blank unless you handle them with `IF()` or a fallback
- **Formulas only access fields in the same table** — use Lookup fields to pull data from linked records

For the official function list, see [Airtable's Formula Field Reference](https://support.airtable.com/docs/formula-field-reference).

## Logical Formulas

Logical formulas are the backbone of most Airtable bases. They power conditional statuses, flags, and dynamic calculations.

**IF()**\
The most-used Airtable formula. Returns one value if a condition is true, another if false.

**Syntax:** `IF(condition, value_if_true, value_if_false)`

```airtable-formula
IF({Status} = 'Complete', '✓ Done', 'In Progress')
```

**Nested IF for multiple conditions:**

```airtable-formula
IF({Days Overdue} > 30, '🔴 Critical',
  IF({Days Overdue} > 7, '🟡 Warning', '🟢 On Track'))
```

**Common business uses:**

```airtable-formula
// Flag high-value deals
IF({Deal Value} >= 10000, 'Enterprise', 'Standard')

// Check if a date has passed
IF(IS_BEFORE({Due Date}, TODAY()), 'Overdue', 'On Time')

// Handle empty fields
IF({Email}, {Email}, 'No email on file')
```

**SWITCH()**\
Cleaner alternative to nested IF statements. Maps specific values to outputs.

**Syntax:** `SWITCH(expression, pattern1, result1, [pattern2, result2, ...], [default])`

```airtable-formula
SWITCH({Priority},
  'Critical', '🔴 P1',
  'High', '🟠 P2',
  'Medium', '🟡 P3',
  'Low', '🟢 P4',
  '⚪ Unset')
```

**Map stages to percentages:**

```airtable-formula
SWITCH({Pipeline Stage},
  'Lead', 10,
  'Qualified', 25,
  'Proposal Sent', 50,
  'Negotiation', 75,
  'Closed Won', 100,
  0)
```

**AND() / OR()**\
Combine multiple conditions.

```airtable-formula
// Both conditions must be true
IF(AND({Status} = 'Active', {Revenue} > 5000), 'VIP', 'Standard')

// Either condition can be true
IF(OR({Status} = 'Overdue', {Priority} = 'Critical'), '⚠️ Needs Attention', 'OK')
```

**NOT()**\
Inverts a condition.

```airtable-formula
IF(NOT({Is Archived}), 'Active', 'Archived')
```

## Text Formulas

**CONCATENATE()**\
Combines text values. The `&` operator works the same way.

```airtable-formula
// Full name
CONCATENATE({First Name}, ' ', {Last Name})

// Same thing with & operator
{First Name} & ' ' & {Last Name}

// Formatted address
{Street} & ', ' & {City} & ', ' & {State} & ' ' & {Zip}

// Display label for records
{Company Name} & ' — ' & {Deal Stage} & ' ($' & {Deal Value} & ')'
```

**LEFT() / RIGHT() / MID()**\
Extract portions of text.

```airtable-formula
// First 3 characters (area code from phone)
LEFT({Phone}, 3)

// Last 4 characters
RIGHT({SKU}, 4)

// Characters from position 5, take 3 characters
MID({Product Code}, 5, 3)
```

**UPPER() / LOWER() / TRIM()**\
Clean and standardize text.

```airtable-formula
// Uppercase for codes
UPPER({Country Code})

// Lowercase for email normalization
LOWER({Email})

// Remove extra whitespace
TRIM({Company Name})
```

**SUBSTITUTE() / REPLACE()**\
Find and replace text within strings.

```airtable-formula
// Replace spaces with hyphens (for URL slugs)
LOWER(SUBSTITUTE({Title}, ' ', '-'))

// Remove unwanted characters
SUBSTITUTE({Phone}, '-', '')

// Chain substitutions for thorough cleanup
SUBSTITUTE(SUBSTITUTE({Phone}, '(', ''), ')', '')
```

**LEN()**\
Returns the character count. Useful for validation.

```airtable-formula
// Check if a field is too long
IF(LEN({Description}) > 500, '⚠️ Too long', '✓ OK')

// Verify phone number length
IF(LEN(SUBSTITUTE({Phone}, '-', '')) = 10, 'Valid', 'Check number')
```

**FIND() / SEARCH()**\
Find the position of text within a string. SEARCH is case-insensitive; FIND is case-sensitive.

```airtable-formula
// Check if email contains a domain
IF(SEARCH('@company.com', {Email}), 'Internal', 'External')

// Extract domain from email
MID({Email}, SEARCH('@', {Email}) + 1, LEN({Email}))
```

**ENCODE_URL_COMPONENT()**\
URL-encodes text for building dynamic links.

```airtable-formula
// Google Maps link
'https://maps.google.com/?q=' & ENCODE_URL_COMPONENT({Address})

// Pre-filled email link
'mailto:' & {Email} & '?subject=' & ENCODE_URL_COMPONENT({Project Name} & ' Follow-up')
```

## Numeric Formulas

**Basic Arithmetic**

```airtable-formula
// Profit margin
({Revenue} - {Cost}) / {Revenue} * 100

// Tax calculation
{Subtotal} * 0.0875

// Total with tax
{Subtotal} + ({Subtotal} * {Tax Rate})

// Discount price
{List Price} * (1 - {Discount Percentage} / 100)
```

**ROUND() / CEILING() / FLOOR()**\
Control decimal precision — essential for financial data.

```airtable-formula
// Round to 2 decimal places (currency)
ROUND({Revenue} * {Tax Rate}, 2)

// Round up to nearest whole number
CEILING({Hours Worked})

// Round down
FLOOR({Estimated Days})

// Round to nearest 5
CEILING({Price} / 5) * 5
```

**SUM() / MAX() / MIN() / AVERAGE()**\
Aggregate multiple fields within the same record.

```airtable-formula
// Sum of quarterly revenue fields
SUM({Q1 Revenue}, {Q2 Revenue}, {Q3 Revenue}, {Q4 Revenue})

// Highest score across criteria
MAX({Technical Score}, {Cultural Score}, {Experience Score})

// Lowest bid
MIN({Bid 1}, {Bid 2}, {Bid 3})

// Average rating
AVERAGE({Quality}, {Timeliness}, {Communication})
```

**ABS() / POWER() / SQRT() / MOD()**

```airtable-formula
// Absolute value (for variances)
ABS({Actual} - {Budget})

// Compound growth
{Starting Value} * POWER(1 + {Growth Rate}, {Years})

// Check if a number is even
IF(MOD({Row Number}, 2) = 0, 'Even', 'Odd')
```

**INT() / VALUE()**

```airtable-formula
// Convert decimal to integer
INT({Calculated Score})

// Convert text to number
VALUE({Imported Amount})
```

## Date and Time Formulas

**TODAY() / NOW()**\
Return the current date or date-time. Updates when the record loads (not in real-time).

```airtable-formula
// Days until deadline
DATETIME_DIFF({Due Date}, TODAY(), 'days')

// Is this overdue?
IF(IS_BEFORE({Due Date}, TODAY()), 'Overdue', 'On Track')
```

**DATETIME_DIFF()**\
Calculate the difference between two dates. One of the [three most essential Airtable formulas](https://www.gapconsulting.io/blog/the-only-3-airtable-formulas-you-actually-need-2026).

**Syntax:** `DATETIME_DIFF(date1, date2, unit)`

Units: `'days'`, `'hours'`, `'minutes'`, `'seconds'`, `'months'`, `'years'`

```airtable-formula
// Days between two dates
DATETIME_DIFF({End Date}, {Start Date}, 'days')

// Months since last contact
DATETIME_DIFF(TODAY(), {Last Contact Date}, 'months')

// Hours worked
DATETIME_DIFF({Clock Out}, {Clock In}, 'hours')

// Age from birthdate
DATETIME_DIFF(TODAY(), {Date of Birth}, 'years')
```

**DATEADD()**\
Add time to a date.

```airtable-formula
// Due date = 30 days from creation
DATEADD({Created Date}, 30, 'days')

// Follow-up reminder = 1 week after last contact
DATEADD({Last Contact Date}, 7, 'days')

// Contract renewal date = 1 year from start
DATEADD({Contract Start}, 1, 'years')

// Next billing date = 1 month from last invoice
DATEADD({Last Invoice Date}, 1, 'months')
```

**DATETIME_FORMAT()**\
Format dates for display.

```airtable-formula
// "March 30, 2026"
DATETIME_FORMAT({Date}, 'MMMM D, YYYY')

// "Mar 2026"
DATETIME_FORMAT({Date}, 'MMM YYYY')

// "03/30/2026"
DATETIME_FORMAT({Date}, 'MM/DD/YYYY')

// Day of the week
DATETIME_FORMAT({Date}, 'dddd')

// Time only: "2:30 PM"
DATETIME_FORMAT({Timestamp}, 'h:mm A')
```

**DATETIME_PARSE()**\
Convert text strings into dates.

```airtable-formula
// Parse a date from imported text
DATETIME_PARSE({Date Text}, 'M/D/YYYY')

// Parse ISO format
DATETIME_PARSE({ISO String}, 'YYYY-MM-DD')
```

**DATESTR() / TIMESTR()**\
Convert datetime to date-only or time-only strings.

```airtable-formula
// Extract just the date
DATESTR({Full Timestamp})

// Extract just the time
TIMESTR({Full Timestamp})
```

**IS_BEFORE() / IS_AFTER() / IS_SAME()**\
Date comparison functions.

```airtable-formula
// Overdue check
IF(IS_BEFORE({Due Date}, TODAY()), '🔴 Overdue', '🟢 On Track')

// Future event check
IF(IS_AFTER({Event Date}, TODAY()), 'Upcoming', 'Past')

// Same day check
IF(IS_SAME({Start Date}, {End Date}, 'day'), 'Same Day', 'Multi-day')
```

**WEEKDAY() / WEEKNUM() / YEAR() / MONTH() / DAY()**\
Extract date components.

```airtable-formula
// Day of week (0=Sunday, 6=Saturday)
WEEKDAY({Date})

// Is it a weekend?
IF(OR(WEEKDAY({Date}) = 0, WEEKDAY({Date}) = 6), 'Weekend', 'Weekday')

// Fiscal quarter
SWITCH(MONTH({Date}),
  1, 'Q1', 2, 'Q1', 3, 'Q1',
  4, 'Q2', 5, 'Q2', 6, 'Q2',
  7, 'Q3', 8, 'Q3', 9, 'Q3',
  10, 'Q4', 11, 'Q4', 12, 'Q4')

// Year-Month label
YEAR({Date}) & '-' & RIGHT('0' & MONTH({Date}), 2)
```

## Array Formulas

Array formulas work with Lookup and Rollup fields that return multiple values.

**ARRAYJOIN()**\
Combine array values into a single text string.

```airtable-formula
// Join linked record names with commas
ARRAYJOIN({Linked Projects}, ', ')

// Join with custom separator
ARRAYJOIN({Tags}, ' | ')

// Join with line breaks (for long form display)
ARRAYJOIN({Task Names}, '\n')
```

**ARRAYCOMPACT()**\
Remove empty values from an array.

```airtable-formula
ARRAYJOIN(ARRAYCOMPACT({Optional Fields}), ', ')
```

**ARRAYUNIQUE()**\
Remove duplicate values.

```airtable-formula
ARRAYJOIN(ARRAYUNIQUE({Categories}), ', ')
```

**ARRAYFLATTEN()**\
Flatten nested arrays into a single-level array.

```airtable-formula
ARRAYJOIN(ARRAYFLATTEN({Nested Lookup}), ', ')
```

## Common Business Formula Patterns

These are the formulas we use most often when building [Airtable systems for clients](/airtable-consultant).

**Overdue Alert with Severity**

```airtable-formula
IF(IS_BEFORE({Due Date}, TODAY()),
  IF(DATETIME_DIFF(TODAY(), {Due Date}, 'days') > 14,
    '🔴 Critical — ' & DATETIME_DIFF(TODAY(), {Due Date}, 'days') & ' days overdue',
    '🟡 Overdue — ' & DATETIME_DIFF(TODAY(), {Due Date}, 'days') & ' days'),
  IF(DATETIME_DIFF({Due Date}, TODAY(), 'days') <= 3,
    '🟠 Due Soon — ' & DATETIME_DIFF({Due Date}, TODAY(), 'days') & ' days left',
    '🟢 On Track'))
```

**Deal Win Probability**

```airtable-formula
SWITCH({Pipeline Stage},
  'Lead', 5,
  'Qualified', 15,
  'Discovery', 30,
  'Proposal', 50,
  'Negotiation', 75,
  'Verbal Commit', 90,
  'Closed Won', 100,
  'Closed Lost', 0,
  0) / 100
```

**Weighted Pipeline Value**

```airtable-formula
{Deal Value} * SWITCH({Pipeline Stage},
  'Lead', 0.05,
  'Qualified', 0.15,
  'Proposal', 0.50,
  'Negotiation', 0.75,
  'Closed Won', 1,
  0)
```

**Days Since Last Activity**

```airtable-formula
IF({Last Activity Date},
  DATETIME_DIFF(TODAY(), {Last Activity Date}, 'days') & ' days ago',
  'No activity recorded')
```

**Initials Generator**

```airtable-formula
LEFT({First Name}, 1) & LEFT({Last Name}, 1)
```

**Business Days Calculation (Approximate)**

```airtable-formula
// Approximate business days between two dates
INT(DATETIME_DIFF({End Date}, {Start Date}, 'days') * 5 / 7)
```

**Invoice Status**

```airtable-formula
IF({Paid Date},
  '✓ Paid',
  IF(IS_BEFORE({Due Date}, TODAY()),
    '🔴 Overdue by ' & DATETIME_DIFF(TODAY(), {Due Date}, 'days') & ' days',
    IF(DATETIME_DIFF({Due Date}, TODAY(), 'days') <= 7,
      '🟡 Due in ' & DATETIME_DIFF({Due Date}, TODAY(), 'days') & ' days',
      '⚪ Not Yet Due')))
```

## Formula Debugging Tips

When your formula returns an error:

1. **Check field name spelling.** Names are case-sensitive and must include spaces exactly as they appear.
2. **Handle empty values.** Wrap calculations in `IF({Field}, [calculation], [fallback])` to avoid errors on blank fields.
3. **Watch for type mismatches.** You can't do math on text fields — use `VALUE()` to convert text to numbers.
4. **Use single quotes for text.** `'Active'` not `"Active"`.
5. **Test incrementally.** Build complex formulas step by step — get the inner function working before wrapping it in another.

## What Formulas Can't Do (and What to Use Instead)

- **Cross-table calculations:** Use Lookup + Rollup fields, then reference those in formulas
- **Multi-step workflows:** Use [Airtable Automations](/tutorials/airtable-automation-examples) or [Make](/make-automation-agency)
- **External data:** Use [Make](/make-automation-agency) or [Zapier](/zapier-automation) to pull data from other tools
- **Complex aggregations:** Use Rollup fields for SUM, COUNT, AVERAGE across linked records
- **Dynamic current user:** Formulas can't detect who's viewing — use Interface Designer for user-specific views

Need help building formulas for your specific Airtable base? [We build Airtable systems](/airtable-consultant) for teams that need more than templates — including the formulas, automations, and integrations that make them work.


## Sitemap

See the full [sitemap](/sitemap.md) for all pages.