10 Most Used Airtable Formulas

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, tag the stakeholders involved… but once you involve formulas into the mix, things can get much deeper. 


Things you could implement include:


  • Setting and assigning recurring events;
  • Calculating cost and budget;
  • Providing specific data based on time;
  • Display user-specific information;
  • Creating advanced calculations։


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

How to Use Formulas in Airtable?


At their core, Airtable formulas are a field type. You can easily select formulas from the list available in your table:

airtable formula field type

From there, writing a formula is easy. You can directly type it in the formula field, or research a pre-existing function. We advise you to play around with formulas a little bit before jumping into serious projects, as they are quite fun to meddle with.


The output of your formula will be a number, a date, or a string. 


This result can be formatted to your preference by selecting the “formatting” tab in the field type configuration pane and selecting one of the options available:


  • Number: Decimal, integer, currency, percent, or duration;
  • Date: Date format, option to include a time field, and option to use the same time zone for all collaborators;
  • String: The output most likely contains a mix of dates and numbers.


airtable formula formatting

We will share more tips and best practices towards the end of the article. In the meantime and since this is all you need to know to start writing formulas, let’s look at some of the essential ones you need in your arsenal. We’ve gathered 10 of the most useful Airtable formulas. Whether you’re a new user looking to start with Airtable or an established pro in need of a refresher, these will help you make more out of your bases and hopefully supercharge both your Airtable and Softr projects. Let’s dive in!

1. Extracting Character with LEFT() and RIGHT()


The LEFT() and RIGHT() formulas allow you to extract characters from a string, selecting to start eother from the beginning (left) or end (right). You can use it as follows:


LEFT(string, howMany)

RIGHT(string, howMany)

Example: LEFT ({Social Security number}, 5)


In this example, you would be extracting the 5 first characters from the “Social Security number” 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.

2. Calculating Length with LEN()


Similar to the previous functions, LEN() will return the length of a string. The syntax is quite straightforward:


LEN(string)

Example: LEN(“MotdePasse2021”) => 14


What is the point of getting the length of a string you might ask? Turns out it can prove useful in different scenarios including


  • 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, (e.g. 280 characters for Twitter, as shown below).


len formula airtable

3. Locating a Character Using FIND() and SEARCH()


FIND() and SEARCH() are two functions that allow you to locate a character in a string. They serve the same purpose, and the only difference between the two is that SEARCH() provides the position only if it can find it, while FIND() will return zero if it doesn’t find anything. The syntax goes as the following:


FIND(stringToFind, whereToSearch, [startFromPosition]} 

Example: FIND("Softr", "Build your own web applications with Softr") => 38


In this example, the formula will find the first occurrence of “Softr” in the sentence. This might not seem like a high-priority task, but coupled with other formulas, it can be quite useful to


  • Locate the file name from an attachment;
  • Identify whether a name is located in a sentence;
  • Isolate a specific reference from a paragraph.


4. Manipulate Numbers Using Numeric Formulas Such As ROUND()


Numeric functions are a great way to manipulate numbers, run operations, and ensure your numerical results are formatted as intended. There are a lot of pre-written functions available on Airtable, but one of the mot useful ones is ROUND(), which you can use when you need your data to be rounded.   


ROUND(value, precision)

Example: ROUND(7.5, 0) => 8


The result will automatically be rounded to the nearest integer at the specified precision. Alternatively, you can also use ROUNDUP() or ROUNDDOWN().

5. Replace Content or a String 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:


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

Example: SUBSTITUTE(“My Favorite platform is Facebook”, “Facebook”, “Softr”)

Result: “My Favorite platform is Softr”


REPLACE() will carry a similar task but this time replacing not based on a specific text but on the position in the string.


REPLACE(string, start_character, number_of_characters, replacement)

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

Result: My favorite product is Softr


This is a simple yet useful formula to reliably edit massive tables with consistent data.

6. Leverage Date and Time Fields With Date Formulas


We’re getting into the heavy hitters, as date formulas are some of the most powerful tools at your disposal in Airtable. We’ve written extensively about them and advise you to read both 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 to a date;
  • Change the format of a date;
  • Set up recurring events;
  • Format date and time in various ways;
  • Calculate the difference between two dates.


One of the most important date formulas is the last one in the list above, automatically helping you calculate the difference between two dates using the DATETIME_DIFF() function:


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.


Note: The output is displayed in seconds by default, but replacing the "units" specifier will allow you to choose a different one. Other specifiers can be selected using abbreviations you can find in this table.

7. Display Time Differently Using SET_TIMEZONE()


Another useful formula associated with dates is the ability to set a specific timezone in a date field. This is especially useful nowadays, as remote work is rapidly growing and teams are increasingly spread out around the world.


SET_TIMEZONE(Datetime, timezone) 

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


The default timezone will always match the device you’re using, but it can’t be changed by toggling the “Use the same time zone (GMT) for all collaborators'' option:

airtable timezone all collaborators

You can find the complete list of supported timezone identifiers in this article.

8. Obtaining a Eecord Unique ID With RECORD_ID()


You might not know this, but each record in Airtable has a unique identifier called a Record ID. Recovering that Record ID is fairly straightforward using this formula:  


RECORD_ID()


Now you might think: "Why would I even want to know that information and identify a record’s ID?" You might not need it yourself, but developers or collaborators might require it from you, as it can be useful for advanced use:


  • Configuring API integrations;
  • Creating record-specific URLs;
  • Setting up automations.


9. Joining Text Strings Together Using CONCATENATE()


The concatenate formula allows you to join together text arguments into a single text value. If you’re trying to concatenate static text like “-” or “*’ for example, make sure to surround it with double quotation marks so Airtable doesn’t use it as an operator. The syntax is simple:


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

Example: CONCATENATE(Name," - ", Age) => John - 36


This formula can be particularly useful to create a primary field summarizing the content of the key fields in the table. Let’s consider a user research database where we want to get the main information at a glance in the first column:

airtable primary field concatenate formula

10. Setting Up Conditional Statements With IF() Formulas


Last but not least, IF() formulas might be the most powerful way to leverage all your other Airtable operations. Allowing to set conditional statements, the IF() formula’s syntax and logic are quite straightforward. It’s all about whether the specified logical argument is true or false.


IF(logical, value1, value2)

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


But things get more advanced with nested IF statements. Nesting describes the action of having an IF function inside of another IF function and allows the formula to output more than two responses. 


An IF() formula with two nested statements looks like this:  


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


In simple language, the formula means that


  • 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, increasing the complexity of the formula and opening up new possibilities for your projects.

Tips and Common Mistakes With Formulas


We hope these formulas are clearly defined and easy to implement. Even though, working and experimenting with Airtable is often about trial and error, especially with custom setups that can become quite overwhelming. Here are some best practices to refer to whenever you’re implementing formulas.


Map Out a Formula Before Writing It


Probably the most important piece of advice is to lay down the groundwork before you even start writing in the formula field. Depending on your preference and what works best for you, we advise you to try:


  • Writing your 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;”
  • Drawing the formula logic on paper or in an online tool to help visualize it with a clear, high-level representation;
  • Formulating your idea using abstract letters (“A” and “B”), focusing on the logic of your formula before applying actual values.


This will help tremendously in avoiding headaches, mistakes, and useless time spent on overcomplicated formulas along the way.


Breaking Your Long Formula Down into Smaller Formulas Before Putting Them Together


As you start working on increasingly difficult problems and combining formulas, wires can easily get crossed. We advise you to always break down your formulas into smaller chunks that you can then put together.


This will be easier than doing the opposite: breaking down a long formula to identify which part is broken. Trust us on that.


Check Your Formatting 


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


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 formulas is to write them more clearly. Especially with IF() functions that get quite messy and confusing, organizing your formula in a clear way can be the difference between easily identifying a missing parenthesis and adding a totally 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!"

          )

        )

    )

)

Conclusion


Congratulations, you now have a solid base (pun intended) to implement formulas in Airtable! From formatting to powerful operations, these formulas will come in handy when trying to solve business problems or collaborating with team members and will allow you to supercharge your Softr projects.


by Thierry Maout


About Softr


Softr is an easy-to-use no-code platform that turns Airtable bases into powerful web apps and client portals. Softr offers a way for you to authenticate your end-users, control access to your content and data based on conditional rules like roles, logged-in status, subscription plans etc. If you're using Airtable as a product catalog you can use a Softr template to build your e-commerce website. Or maybe you'd like to build a custom website for your travel journal, there's a template for that too!