Most Airtable users stay in the shallow end of the formula pool. IF statements, basic text concatenation, a DATEADD here and there. That's useful, but the functions that actually save significant time are the ones that feel intimidating at first: REGEX, SWITCH, DATETIME_DIFF, ARRAYUNIQUE, VALUE.
Here are seven Airtable formulas worth learning, with practical examples for each.
Try Airtable and apply these formulas to your base today.
Disclosure: some links in this post are affiliate links. If you sign up through them, GAP Consulting earns a small commission at no extra cost to you.
REGEX (regular expression) functions let you extract or validate specific patterns from a text field. REGEX_EXTRACT({Field}, "pattern") pulls the first match of a pattern from a string.
Practical use: extract a phone number from a messy notes field, pull the domain from an email address, or isolate a numeric ID from a longer reference string.
Example: REGEX_EXTRACT({Email}, "@(.+)$") extracts the domain from an email address. If the email is gareth@gapconsulting.io, this returns gapconsulting.io.
REGEX feels complex but 80% of real-world uses follow the same two or three patterns. Once you learn those, it becomes a reliable tool.
If you've ever written a formula with four nested IF statements, SWITCH is the cleaner alternative. SWITCH(expression, case1, result1, case2, result2, ..., default) evaluates an expression against a list of cases and returns the matching result.
Example: mapping a status field to a color label:
The same logic in nested IFs is harder to read and maintain. SWITCH is cleaner and easier to update when you add new cases.
DATETIME_DIFF(date1, date2, unit) returns the difference between two dates in whatever unit you specify: days, hours, weeks, months.
Practical use: how many days has this deal been open, how many business days until a deadline, how old is this support ticket.
Example: DATETIME_DIFF(TODAY(), {Created Date}, 'days') returns the number of days since a record was created. Pair with an IF to flag records older than 30 days as overdue.
When a Lookup field returns multiple values from linked records, ARRAYJOIN turns that array into a readable string. ARRAYUNIQUE removes duplicates from an array before you join it.
Example: a project record has multiple linked tasks, each with a tag. A Lookup field returns all the tags. ARRAYJOIN(ARRAYUNIQUE({Tags Lookup}), ", ") produces a clean, deduplicated comma-separated list of tags on the project record.
This is far more readable than a raw array and useful for display fields, email bodies, and exports.
VALUE() converts a text string to a number. Useful when numeric data comes in as text (from form submissions or imports) and needs to be used in calculations.
ROUND(number, precision) rounds to a specified decimal place. Combine them: ROUND(VALUE({Budget Text Field}), 2) extracts a number from a text field and rounds it to two decimal places.
This pair is essential for cleaning imported data and making formula-based calculations work on fields that look like numbers but are stored as text.
IF(field = BLANK(), "fallback", field) prevents empty fields from breaking downstream formulas or displaying ugly empty strings in interfaces.
Use it anywhere a field might be empty: IF({Due Date} = BLANK(), "No due date set", DATETIME_FORMAT({Due Date}, "M/D/YYYY")). Clean output regardless of whether the field has a value.
CREATED_TIME() returns the exact timestamp when a record was created. No automation needed — it's a formula that's always accurate.
Pair with DATETIME_DIFF(TODAY(), CREATED_TIME(), 'days') to get a live "age" counter on every record. For support tickets, leads, or projects, knowing how long something has been sitting in a particular state is often more useful than knowing what date it was created.
Build smarter Airtable bases — these seven formulas cover the majority of advanced data manipulation you'll need in real-world builds.
Gareth Pronovost is the founder of GAP Consulting, an Airtable-first consultancy that helps businesses replace manual workflows with systems that scale. A former corporate finance analyst turned no-code educator, he has taught hundreds of thousands of builders through his YouTube channel and led systems builds for organizations ranging from solo founders to Disney and Ticketmaster.
The leading no-code consultancy. We build Airtable, Zapier, and Fillout systems for businesses that are ready to stop doing things manually.
