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:
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.
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.
Create interfaces for Airtable bases in minutes
Softr lets you easily build powerful applications on top of your Airtable data, without coding.
Locating a character using FIND() and SEARCH()
FIND() and SEARCH() are two Airtable formulas that locate a character in a string. Although they serve the same purpose, 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:
Syntax: FIND(stringToFind, whereToSearch, [startFromPosition]}
Example: FIND("Softr", "Build your own web applications with Softr") will return 38
In this example, the Airtable 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 for a few use cases:
- Locating the file name from an attachment;
- Identifying whether a name is located in a sentence;
- Isolating a specific reference from a paragraph.
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:
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:
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.
Create interfaces for Airtable bases in minutes
Softr lets you easily build powerful applications on top of your Airtable data, without coding.
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.
Create interfaces for Airtable bases in minutes
Softr lets you easily build powerful applications on top of your Airtable data, without coding.
