Table of Contents
- How to use formulas in Airtable
- Extracting character with LEFT() and RIGHT()
- Calculating Length with LEN()
- Locating a Character Using FIND() and SEARCH()
- Manipulate Numbers Using Numeric Formulas Such As ROUND()
- Replace Content or a String With SUBSTITUTE() and REPLACE()
- Leverage Date and Time Fields With Date Formulas
- Display Time Differently Using SET_TIMEZONE()
- Obtaining a Record Unique ID With RECORD_ID()
- Joining Text Strings Together Using CONCATENATE()
- Setting Up Conditional Statements With IF() Formulas
- Tips and Common Mistakes With Formulas
- Conclusion
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 Airtable 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 Airtable formulas work and what you need to know to get started.
How to Use Airtable Formulas?
At their core, Airtable formulas are a field type. You can easily select formulas from the list available in your table:
From there, writing an Airtable 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 Airtable formula will be a number, a date, or a string.
This result can be formatted as needed by selecting the “formatting” tab from field type and choosing one of the options:
- 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.
We will share more tips and best practices towards the end of the article. Right now, this is all you need to get started, so 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 Airtable user or an established pro, these will help you make more out of your bases and supercharge your 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 either 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.
The following video by Ben Green will walk you through these functions. So, check it out if you want to see specific usage examples.
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).
Turn your Airtable data into a website with Softr!
No designer or developer needed. Zero learning curve.
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. 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 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 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. One of the most 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 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 Airtable 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. 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 to a date;
- Change the format of a date;
- Set up recurring events;
- Format date and time in various ways;
- Calculate the difference between the two dates.
One of the most important date formulas is the DATETIME_DIFF(). It lets you calculate the difference between two dates:
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 Airtable 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. However, that can be changed by toggling the Use the same time zone (GMT) for all collaborators option:
You can find the complete list of supported timezone identifiers in this article.
8. Obtaining a Record Unique ID With RECORD_ID()
Each record in Airtable has a unique identifier called a Record ID. The Record ID can be obtained using the following 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 automation.
9. Joining Text Strings Together Using CONCATENATE()
The Airtable 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. Otherwise, Airtable can use it as an operator.
The syntax of the formula is simple:
CONCATENATE(text1, [text2, ...])
Example: CONCATENATE(Name," - ", Age) => John - 36
The Airtable concatenate 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:
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 Airtable 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.
If you'd like to dive deeper into the topic, below is a more detailed breakdown of the IF() formulas along with SWITCH() by Gareth Pronovost of GAP Consulting.
Tips and Common Mistakes With Formulas
We hope these Airtable 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:
- Write 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. 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! These formulas will come in handy when trying to solve business problems, when collaborating with team members, and in numerous other situations.
About Softr
Softr is an easy-to-use no-code platform that turns Airtable bases into powerful web apps, membership websites 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!
