10 most used Airtable formulas in 2024

Thierry Maout
/
Mar 3, 2025
/
15
min read

Formulas are one of the most powerful Airtable features. They allow you to reference other fields, craft conditional statements, and create operations to leverage the content of your base in unique, inventive ways.

To put things into perspective, let’s imagine you are using Airtable for project management. Airtable allows you to easily list each task, their due dates, and tag the stakeholders involved. Other examples of use cases for Airtable formulas include setting and assigning recurring events, calculating cost and budget, providing specific data based on time, displaying user-specific information, and creating advanced calculations. Once you involve Airtable formulas in the daily use of Airtable, it becomes more useful than ever before.

Before we go into specific examples of Airtable formulas, let’s take a look at how they work and what you need to know to get started.

Using Airtable formulas

Airtable formulas are a field type on Airtable. This means you can add formulas to your table like you’d add any other field type:

airtable formula field type

Next, you need to write an Airtable formula that accomplishes what you need. You can directly type it in the formula field, or research a pre-existing function, like one of those listed in this article.

The output Airtable formula will be a number, a date, or a string. Depending on whichever is the output format, the following options will be available to you, by clicking on the Formatting tab:

  • Number: decimal, integer, currency, percent, or duration;
  • Date: date format, an option to include a time field, and an option to use the same time zone for all collaborators;

When the output of a formula is a string, it contains a mix of dates, numbers, and/or other plain text. In that case, you’ll have no further options available to you in the Formatting tab.

airtable formula formatting

Most used Airtable formulas

Now that you know all you need to get started with Airtable formulas, let’s look at the 10 most useful examples of Airtable formulas. Whether you’re a new Airtable user or an established pro, these will help you make more out of your Airtable bases and supercharge your projects.

Extracting characters with LEFT() and RIGHT()

The LEFT() and RIGHT() formulas allow you to extract characters from a string, starting either from the beginning (left) or end (right). You can use these Airtable formulas as follows:

Syntax: LEFT(string, howMany) or RIGHT(string, howMany)

Example: LEFT ({Build client portals with Softr}, 5)

In this example, you would be extracting the 5 first characters from the “Build client portals with Softr” field. Alternatively, using RIGHT(), you’d be extracting the last 5 characters. This can be used with dates, specific strings of numbers, or even text fields.

Calculating Length with LEN()

The Airtable formula LEN() will return the length of a string. The syntax is quite straightforward:

Syntax: LEN(string)

Example: LEN(“Build internal tools with Softr”) will return 31

Getting the length of a string is useful in many use cases, such as:

  • Setting up an automated message when a chosen password doesn’t have the minimum amount of characters;
  • Ensuring text doesn’t exceed a certain character limit.
len formula airtable

Manipulating numbers using ROUND()

Numeric Airtable formulas are a great way to manipulate numbers, run operations, and ensure your numerical results are formatted as intended. One of the most useful of these formulas is ROUND(), which you can use when you need the numbers in a field to be rounded.

Syntax: ROUND(value, precision)

Example: ROUND(7.5, 0) will return 8

This formula will automatically round the numbers to the nearest integer at the specified precision. Alternatively, you can also use ROUNDUP() or ROUNDDOWN() to round the numbers up or down, respectively.

Replace with SUBSTITUTE() and REPLACE()

SUBSTITUTE() can help you replace every instance of a text in a field. To understand what that means, let’s put it in context:

Syntax: SUBSTITUTE(string, old_text, new_text, [index])

Example: SUBSTITUTE(“My favorite no-code platform is unknown”, “unknown”, “Softr”) will return “My favorite no-code platform is Softr”

REPLACE() will carry a similar task, but this airtable formula replaces not based on a specific text but the position in the string.

Syntax: REPLACE(string, start_character, number_of_characters, replacement)

Example: REPLACE("My favorite product is Facebook", 24, 8, "Softr") will return “My favorite product is Softr

These are simple yet useful Airtable formulas to reliably edit massive tables with consistent data.

Leverage date and time fields with date formulas

Date formulas are some of the most powerful Airtable formulas at your disposal. To find out more, we'd suggest reading our introduction to date formulas and our list of the 12 Airtable date formulas you must know. For example, date formulas can allow you to add time to a date, change the format of a date, set up recurring events, format date and time, and calculate the time difference between two dates.

One of the most important date formulas is the DATETIME_DIFF(). It lets you calculate the difference between two dates.

Syntax: DATETIME_DIFF([date1], [date2], 'units')

Example: DATETIME_DIFF({Start date}, TODAY, days)

The result is calculated by subtracting [date2] from [date1]. This can help you see the remaining time until a deadline, for example. The output is displayed in seconds by default, but replacing the "units" specifier will allow you to choose a different one.

Display time differently using SET_TIMEZONE()

Another useful Airtable formula associated with dates is the ability to set a specific timezone in a date field, using SET_TIMEZONE(). This is especially useful when your team or employees work in different time zones around the world.

Syntax: SET_TIMEZONE(Datetime, timezone)

Example: SET_TIMEZONE({Appointment time}, 'Europe/Oslo’)

Note that the default timezone will always match the device you’re using. However, you can change that, by toggling the Use the same time zone (GMT) for all collaborators option when editing a dates field:

airtable timezone all collaborators

Obtaining a record unique ID with RECORD_ID()

Each record in Airtable has a unique identifier, called Record ID. It can be obtained using the following formula:

Syntax: RECORD_ID()

Obtaining the record ID is essential when using Airtable for more advanced use cases, such as configuring API integrations, creating record-specific URLs, and setting up automation.

Joining strings together using CONCATENATE()

The Airtable concatenate formula allows you to combine the values in multiple fields together within a formula field. When concatenating text that includes “-” or “*”, ensure you surround it with double quotation marks; if you don’t, Airtable will use it as an operator.

Syntax: CONCATENATE(text1, [text2, ...])

Example: CONCATENATE(Name," - ", Age) might return John - 36

Concatenating in Airtable can be particularly useful to create a primary field that summarizes the content of other fields in the same table. If you’re using Airtable as an applicant tracker, you can use this Airtable formula to have the main information available in the first column:

airtable primary field concatenate formula

Setting up conditional statements with IF()

The IF() formula in Airtable might be the most powerful way to leverage all your other Airtable operations. Setting up a conditional statement is straightforward: it’s all about whether the specified logical argument is true or false.

Syntax: IF(logical, value1, value2)

Example: IF(Score > 100, "Win", "Lose")

You can take your conditional statements to the next level, by nesting various such statements. Nesting describes the action of having an IF function inside of another IF function, resulting in the Airtable formula outputting more than two responses.

Syntax: IF(Logical Argument 1, Value 1, IF(Logical Argument 2, Value 2, Value 3))

The Airtable formula above means:

  • If Logical Argument 1 is true, return Value 1;
  • If Logical Argument 1 is false and Logical Argument 2 is true, return Value 2;
  • If Logical Argument 1 is false and Logical Argument 2 is false, return Value 3.

This can go on and on with multiple IF statements, thereby increasing the complexity of the formula and opening up new possibilities for your projects.

Tips to avoid common mistakes

Working and experimenting with Airtable formulas is often about trial and error. However, by following some tips, you can avoid common mistakes.

Map out a formula before writing it

The most important piece of advice is to lay down the groundwork before you even start writing in the formula field. Here are some of our suggestions for you to try:

  • Write your Airtable formula as a fully-sentenced explanation. For example: “By calculating the difference between the start date and the due date, I want to display the number of days remaining in a project;”
  • Draw the Airtable formula’s logic on paper or in an online tool to help visualize it with a clear, high-level representation;
  • Formulate your idea using abstract letters (“A” and “B”), so that you can focus on the logic of your Airtable formula before applying actual values.

These will help you avoid creating over-complicated formulas that might result in mistakes.

Break a long formula into smaller ones

As you start working on increasingly difficult problems, you can easily find yourself creating Airtable formulas with some mistakes. To avoid this, we advise you to break down your formulas into smaller chunks that you can then put together.

Check the formatting

The most common errors in Airtable formulas are usually the most obvious. Forgetting a comma or a parenthesis is an easy mistake that is bound to happen.

To avoid formatting mistakes, remember the golden rule: when there’s an opening parenthesis, there should always be a closing one.

Make formulas easy to read

Finally, a great way to simplify your Airtable formulas is to write them more clearly. Organizing your formula can be the difference between easily identifying a missing parenthesis and adding a useless statement by mistake.

See the difference for yourself between these two ways of writing the same formula:

Version 1:

IF({Game Score} < 0, "Game Over", IF({Game Score} < 70, "You lose! Continue?", IF({Game Score} < 90, "You win!", IF({Game Score} <= 100, "Flawless Victory", "Error!"))))

Version 2:

IF(

 {Game Score} < 0,

   "Game Over",

   IF(

     {Game Score} < 70,

       “You lose! Continue?",

       IF({Game Score} < 90,

         "You win!",

         IF({Game Score} <= 100,

           "Flawless Victory",

           "Error!"

         )

       )

   )

)

Because the way you write your formulas can be so crucial when it comes to understanding them, keep this tip in mind.

About Softr

Softr is an easy-to-use no-code platform that turns Airtable bases into powerful web apps, internal tools, and client portals. Softr offers a way for you to authenticate your end-users, and control access to your data based on conditional rules, roles, logged-in status, subscription plans, etc. Plus, you can access free customizable templates that allow you to jumpstart the building of your web application.

What is Softr
Softr is the easiest way to turn your data into powerful business apps—no code required. Connect to your spreadsheet or database, customize layout and logic, and share with your team or clients.

Join 700,000+ users worldwide, building client portals, internal tools, CRMs, dashboards, project management systems, inventory management apps, and more—all without code.
Get started free
Thierry Maout

Thierry is a content marketer based in France. He has extensive experience writing about B2B SaaS, automation, and user onboarding. Originally from France, he has lived and worked in Ireland, the US, Germany, the UK and Canada as well as collaborated with companies from all over the world including UserGuiding, Make (formerly Integromat), and others. Thierry has a Bachelor's degree in International Affairs from Le Havre University (France) as well as a Master's degree in Law, Economics, and Management from the Institute of Evolutionary Science of Montpellier (France). Passionate about education and the no-code movement, Thierry has been featured in publications such as UX Collective and The Startup on Medium. A frequent Softr collaborator (freelance-based), he’s also a former startup co-founder and has, among others, co-founded and managed growth at Fairwai.

Categories
Airtable
Guide

Build an app today. It’s free!

Build and launch your first portal or internal tool in under 30 minutes
Get started free