Airtable formulas let you calculate, transform, and generate values automatically within your records. Whether you're building a CRM, 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.
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)
IF({Status} = 'Complete', '✓ Done', 'In Progress')
Nested IF for multiple conditions:
IF({Days Overdue} > 30, '🔴 Critical',
IF({Days Overdue} > 7, '🟡 Warning', '🟢 On Track'))
Common business uses:
// 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])
SWITCH({Priority},
'Critical', '🔴 P1',
'High', '🟠 P2',
'Medium', '🟡 P3',
'Low', '🟢 P4',
'⚪ Unset')
Map stages to percentages:
SWITCH({Pipeline Stage},
'Lead', 10,
'Qualified', 25,
'Proposal Sent', 50,
'Negotiation', 75,
'Closed Won', 100,
0)
AND() / OR()
Combine multiple conditions.
// 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.
IF(NOT({Is Archived}), 'Active', 'Archived')
Text Formulas
CONCATENATE()
Combines text values. The & operator works the same way.
// 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.
// 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.
// 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.
// 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.
// 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.
// 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.
// 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
// 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.
// 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.
// 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()
// 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()
// 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).
// 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.
Syntax: DATETIME_DIFF(date1, date2, unit)
Units: 'days', 'hours', 'minutes', 'seconds', 'months', 'years'
// 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.
// 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.
// "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.
// 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.
// Extract just the date
DATESTR({Full Timestamp})
// Extract just the time
TIMESTR({Full Timestamp})
IS_BEFORE() / IS_AFTER() / IS_SAME()
Date comparison functions.
// 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.
// 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.
// 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.
ARRAYJOIN(ARRAYCOMPACT({Optional Fields}), ', ')
ARRAYUNIQUE()
Remove duplicate values.
ARRAYJOIN(ARRAYUNIQUE({Categories}), ', ')
ARRAYFLATTEN()
Flatten nested arrays into a single-level array.
ARRAYJOIN(ARRAYFLATTEN({Nested Lookup}), ', ')
Common Business Formula Patterns
These are the formulas we use most often when building Airtable systems for clients.
Overdue Alert with Severity
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
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
{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
IF({Last Activity Date},
DATETIME_DIFF(TODAY(), {Last Activity Date}, 'days') & ' days ago',
'No activity recorded')
Initials Generator
LEFT({First Name}, 1) & LEFT({Last Name}, 1)
Business Days Calculation (Approximate)
// Approximate business days between two dates
INT(DATETIME_DIFF({End Date}, {Start Date}, 'days') * 5 / 7)
Invoice Status
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:
- Check field name spelling. Names are case-sensitive and must include spaces exactly as they appear.
- Handle empty values. Wrap calculations in
IF({Field}, [calculation], [fallback])to avoid errors on blank fields. - Watch for type mismatches. You can't do math on text fields — use
VALUE()to convert text to numbers. - Use single quotes for text.
'Active'not"Active". - 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 or Make
- External data: Use Make or Zapier 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 for teams that need more than templates — including the formulas, automations, and integrations that make them work.
