Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,942 Topics
  • 18,243 Replies
5942 Topics
K
KevinTNew Participant
asked in Formulas
Date Calculation Formula

I am trying to create a formal that adds a specific amount of days to a date field. The amount of days varies and is pulled using a lookup field. Initially, I was trying to use the DATEADD function but it doesn't seem to work when the added value is another field instead of a numeric value.The formula I have currently is DATEADD({Submission Date},{Health Plan SLA},"days")The field Health Plan SLA is a lookup field that pulls a number from another tab. I need this number to be what the formula uses. 

206
K
M
Melanie_LemniosNew Participant
asked in Formulas
Date Counting

Hello!I have a list of records that all have a start date and end date column for a number of employees.  I am wondering if there is a way to calculate the number of employees on a given day.  All the employees have different start and end dates.  So for example I want to know the total working on March 1 vs March 7.  I know there is a way to do this in excel using an IF/AND formula but can't figure out how to do it in Airtable. Appreciate any and all advice!  

710
M
K
KonstructionNew Participant
asked in Formulas
Costum ID

Hi there. I'm trying to make a custom auto number. The constant is kasse 00 and then want the autonember to be kasse 001, kasse 002, kasse 003 and so on. Can anybody send me in the right direction?And off cause will there be a problem when I reach kasse 010. It needs to be kasse 010 and not kasse 0010 best regards Thomas

113
TheTimeSavingCo
Kim_Trager1
Kim_Trager1Brainy
asked in Formulas
Replace multiple strings in a field with different strings

I'm trying replace the following text with different values within [] with values from single select columns[art medium], [main objects], [attribute], [expression], [key light], [detailing], [camera shot], [media], [engine], [artist]I thought this would be fairly easy, but somehow can't get it to work.I tried a Substitute() like this:IF(Medium = BLANK(),SUBSTITUTE(Label, "[art medium], ", "" ),SUBSTITUTE(Label, "[art medium]", Medium)) &IF({Main objects}=BLANK(),SUBSTITUTE(Label, "[main objects], ", "" ),SUBSTITUTE(Label, "[main objects]", {Main objects})) But it subs the text and then renders the remaining text. Does anyone have any pointers how I best go about this?

444
Kim_Trager1
F
Fahad_ShejiKnown Participant
posted in Formulas
Formula field to split text into 3 ways

Hey, I am looking for a formula field to extract city, state and zip code from a full address fieldThis is how a full address looks like "New York, NY, 10031". I want to split them 3 ways using formula, how can I do thatI used this formula to get the City   LEFT({Full Address},FIND(",",{Full Address})-1)   

1312
S
M
Motlaq_AlMutairParticipating Frequently
posted in Formulas
How many times a linked record has occured

I'd like to count how many linked records have occurred with another linked record. I have a table where each record is a contract between a company and a governmental entity. The company's column is a linked record and the entity's name is also a linked record. Shown below is an example table.(again, company and entity's columns are supposed to be linked record) As you can see, The company AF Engineering has two contracts with the Ministry of Electricity and one with the Ministry of Health. I'd like to find a way to count those occurrences. It can be from the Companies table, the Entities table, or the Contracts table (the one on the screenshot.) It sort of like the dictionary data structure in programming: {Ministry of Electricity: 2, Ministry of Health: 1}.  How can I do that?   

52
M
Nicole_Merwin
Nicole_MerwinInspiring
asked in Formulas
Split Formula

Hello,I have a rollup field with the following output:FirstName1  Last Name1- XXXX Title Company , First Name2 Last Name2 - XX Title CompanyWithout going back to the original table to pull the "XXX Title Company"  I need a formula to help split and display everything after " - " for each value. Any help is appreciated!

4802
Nicole_Merwin
P
planewryterNew Participant
posted in Formulas
How Use filterByFormula with Apple Shortcuts? URL via CURL Works. URL via Shortcuts: TABLE NOT FOUND

Dear Airtable + Apple Shortcuts Mavens,N00B, here with a N00B question.Using Airtable's API Encoder, I crafted a GET URL with filterByFormula.curl "https://api.airtable.com/v0/app[Redacted]/Items_Table?filterByFormula=%22%7BItem%7D%3D'D123'%22" -H "Authorization: Bearer [redacted]"The resultant URL works fine in CURL.When I store the URL (minus the Authorization bit) as an element in a DataJar Dictionary like this:https://api.airtable.com/v0/app[Redacted]/Items_Table?filterByFormula=%22%7BItem%7D%3D'D123'%22Shortcuts fails with a TABLE NOT FOUND diagnostic message.HOWEVER, when I store the URL as an element in a DataJar Dictionary like this:https://api.airtable.com/v0/app[Redacted]/Items_TableThe query works fine. The Table is found...except, of course, it returns all entries in the Items_Table, not restricted to item 'D123'.Baffled, I am.Any ideas? Suggestions? Thank you in advance for any assistance you can provide on this matter.   

10
P
S
Stephen11Known Participant
asked in Formulas
Delete first matching characters in a column

Hi allIs there a way to delete the certain matching characters that might appear in a column? For example I have a column of data (800+ cells) of company names, and 90% of them are prefixed with a 'number' (that run between 1 and 9), followed by a 'period', followed by a 'space'. For example "1. Some Company Name" or "8. Another Company Name".10% of the data in the column may not have any numbers or spaces, OR some of the company names may not have a 'space' after the 'period' for example "7.Yet Another Company Name" or "And Another Company Name".Amy advice would be greatly appreciated.Stephen 

874
S
Scott_Gardner1
Scott_Gardner1Known Participant
posted in Formulas
Multiple date roll-up to long text bullet format

Hello-I am trying to roll up 10 date fields that are all deadline dates to a long text field in a bullet format.Ideally I would want the field name to be visible as well as the date like this (Field name/Date):- Designer due date: 1-2-23- Submittal due date: 1-15-23- Final proofing due date: 1-30-23I saw this from kuovonneIF( COUNTALL(ARRAYCOMPACT(values)), "• " & ARRAYJOIN(ARRAYCOMPACT(values),"\n• ") ) Thank you!Scott

30
Scott_Gardner1
Cole_Wedemeier
Cole_WedemeierInspiring
asked in Formulas
Concatenate with IF statements, blanks, and inserting ";" in between data,

I have a series of fields I want to concatenate together. The final result needs to include a semi-colon in between each piece of data, but not all columns have data. So I was working to do an IF statement to check if the last of the columns (far right column of data I want) had data, if so, concatenate it with the other fields. The formula I came up with (but is not a valid formula and I can't figure out why) is:   IF({Revenue Rolodex}, CONCATENATE({Revenue Rolodex},”;”,{VIP Day Social},”;”,{4M Event},”;”,{Applied to DID},”;”,{DID Calculator},”;”,{VIP Day Bootcamp},”;”,{2022 Advent Calendar},”:”,{VIP Day Quiz},”;”,{FREEBIE: Roadmap},”;”), IF({VIP Day Social}, CONCATENATE({VIP Day Social},”;”,{4M Event},”;”,{Applied to DID},”;”,{DID Calculator},”;”,{VIP Day Bootcamp},”;”,{2022 Advent Calendar},”:”,{VIP Day Quiz},”;”,{FREEBIE: Roadmap},”;”), IF({4M Event}, CONCATENATE({4M Event},”;”,{Applied to DID},”;”,{DID Calculator},”;”,{VIP Day Bootcamp},”;”,{2022 Advent Calenda

884
pressGO_design
U
username1New Participant
posted in Formulas
Make an End-Date begins the day or after the Start-Date begins

Hello everyone, I would like to create a form where people can add their event dates (event begin at 1st January 2023 and end at 2nd January 2023) and prevent the possibility of the end date to be before the start date (event begins at 1st January 2023 and end at 31 december 2022).To sum up I would like people to not have the possibility to make mistakes with their event dates.Thanks for your answers ! 

352
bruceconsulting
B
Ben_GreenNew Participant
posted in Formulas
Search formula to identify record with specific value in 2 fields as part of a Zap

HiI am setting up a zap connecting stripe to airtable.I want the zap to find a record in Airtable that matches the stripe data in 2 fieldsTo do this I need to enter an AirTable search formula into the zapThe 2 Airtable columns I want it to search are “Email Address” and “Subscribed to”So let’s say that a change is made in stripe that fires the Zap.The zap will search Airtable for a record where both the “Email Address” field and the “Subscribed to” field match the data in both those fields in stripe. The combination of the 2 fields indicates it is the correct record to be updated with the changes made in Stripe.What search formula would get this result for me in my Zap?ChatGPT helped me get to this formula:"{Email Address} = {Stripe Subscription}[customer.email] AND {Subscribed to} = {Stripe Subscription}[items.data.0.price.nickname]"but I'm getting the message"Failed to find or create a record in AirtableError from halted execution: The formula for filtering records is invalid: Invali

473
TheTimeSavingCo
Jared_Wright
Jared_WrightKnown Participant
posted in Formulas
Progress Bar Formula

I looked at the posts about progress bar formulas and I have used them but my result is not looking correct. In the post, they said you need to use chrome and not safari I am using the latest version of chrome and its sill not working. Can some one please help me.

147
J
A
Alessandra_CarlParticipating Frequently
asked in Formulas
DATETIME_PARSE AND CONCATENATE

 Hello everyone,I have to create a calendar of course sessions and I have a table in which I enter in one column the date, in one column the start time and in another column the end time because different days might have different times.I use the formula DATETIME_PARSE(CONCATENATE(DATESTR(Date),' ',{Start Time})) to create a date & time that can be displayed on the calendar. However, the result of the formula returns the wrong time, increased by one hour.Am I doing something wrong or is it a bug?Thanks for any assistance!

320
kuovonne
K
kbob13New Participant
posted in Formulas
Parse specific records from a lookup field?

Hi, new to Airtable and I'm trying to parse out specific records from a lookup field and have Airtable display them in a new column. In one table called "Downloads",  I have a lookup field called "Guests" that is pulling records from a separate table called "Guests". The lookup field returns the names of all the "Guests" for a given entry. There a multiple guests per each entry. In the Guests table there is a variety of data about each individual guest (demographics, affiliation type, etc.). In my Downloads table, I want to create a new column that displays all the Guests names of a certain type (e.g. only Guests that have "Affiliation Type" = "Media"). How can I do this in Airtable? My attempts to create a formula aren't working as I can't get Airtable to return the Guests names. Thanks!

265
Soly
A
Alice_T1New Participant
posted in Formulas
How to calculate the mode for crowdsourced data?

Hi everyone, I’m trying to figure out how to calculate the Mode (ie. the most frequently occurring number in a series). I need this for the purposes of turning crowdsourced data into something useful. For example, if I ask 10 employees of one company how many days of holiday they get a year, I want to return the value most frequently cited, not the average (mean) because that would be inaccurate. In google sheets, it’s simply the function: MODE() but Airtable doesn’t have this. Does anybody know how I might do this?

121
J
A
Aysia_SaylorKnown Participant
asked in Formulas
How to configure a dateadd / weekday formula to return a time only within specified business hours

Hello, Is there a way to configure this formula to only return a time during specified business hours (7:30-4PM)? DATEADD(WORKDAY({Last Modified},1)) Thanks :slightly_smiling_face:

775
J
F
franzispoddiNew Participant
posted in Formulas
IF status matches status in other table pull info from other table

Dear Community,I am faced with the following problem:I want to pull data from a different table when the status in each table is the same.I am creating a text.Say the status in Table 1 is "First Text" then it should check in Table 2 for the row named "First Text" and pull the information from a specific cell in that roll.In sheets it looks like this: =IF(A4=Table2!A2;Table2!B2;IF(A4=Table2!A3;Table2!B3);0)How can I replicate that in Airtable?Thank you

61
bruceconsulting
M
mattstevensonNew Participant
posted in Formulas
Regex expression for matching previous capture group

I'm trying to clean up my text with Regex. Some ranges are listed as 3.0 - 3.0 which I want simplified to just 3.0 whenever the two numbers match. I tried using regex101 and MDN references, but Airtable seems to use some different syntax than either of those.The following expressions should be matching but aren't. Does Airtable not support these "back reference" expressions such as \g{n} or simply \n(\d.*) - (\g{1})(\d.*) - (\1)Desired Result:3.0 - 4.0 ==should remain 3.0 - 4.03.0 - 3.0 ==should change to 3.01.5 - 1.5 ==should change to 1.51.0 - 3.5 ==should remain 1.0 - 3.5 

202
Ben_Young1
Q
quentinlerouxNew Participant
posted in Formulas
Comparing two multiple value columns

Hi everybody !I would like to know how to add in column that features only the similar values found in columns A and B ?(In my case the A and B columns are Lookup from two différents tables but I guess it works the same).Thanks a lot for your help !

455
pressGO_design
Ashley_Jackson
Ashley_JacksonInspiring
posted in Formulas
Formula to calculate debits and credits for personal finance

Hey guys, I've got a new challenge in my quest to create a base for tracking personal finances.  A quick review:TablesCategories (I call them buckets)TransactionsDesignationsTransactions are either entered manually or bulk imported via csv from my bank.  Each transaction is assigned at least one category via the designations join table.In the Categories table, I have assigned each category a monthly budget.  Then I roll up the designations amounts filtered by month (this is my current setup - I'm working on a different option).  Now, you would think that a simple third formula field for [ budget - total spent (roll-up) ] would give me the balance for each of my budgets for the month.  Should tell me whether I've received all my salary for the month or if I've gone over budget for eating out, for example.Here's my confusion:  there are situations in which categories may have counter-intuitive balances.  For instance, we have a category for gifts receiv

505
M
S
Samuel_SaunierNew Participant
posted in Formulas
Problem with this formula

Hi there, I have a single select field name "Département" and I want to concatene the 2 first letters (later for automation).I have a problem with this formula :IF(  OR(    {Département} = "75",     {Département} = "78",     {Département} = "77",     {Département} = "91",     {Département} = "92",     {Département} = "93",     {Département} = "94",     {Département} = "95"  ),   LEFT({Département}, 2),   "")Erreur : Missing opening parenthesis "(".Can someone help me ?

91
Ron_Daniel
OpsDir_Cassie
OpsDir_CassieInspiring
posted in Formulas
Date & Time formula incorrect

Hello!I have created an estimates / intakes base for a client. One of the goals of creating this was to have an automation that sent a confirmation email to the customer once their estimate was scheduled. When I tried to insert the Estimate Date/Time field directly into the email automation, it was not working. Someone suggested creating a different field that makes the formula more 'readable'. However, for some reason, the date is not converting properly:As you can see, while the time converts properly, the date is way off. Here is the formula I'm currently using: Can anyone identify the problem for me and offer a solution? Thanks in advance! 

165
kuovonne
L
Laurie_AhmediNew Participant
posted in Formulas
Automatically calculate overtime?

Hi there,We recently decided to use Airtable as our payroll software, everything is fine but there's one problem and it's about the automatic calculation of overtime per week.We found a way to calculate those hours but it involves manually copying the sum of all the hours worked per week in the "Total heures travaillées" (total hours worked) column, we find this number of hours in the "sum" of the column "Durée" (duration). Thus, copying this sum in the column "Total heures travaillées" (total hours worked) allows us to see the total of overtime hours per week in the other column "Heures supplémentaires" (Overtime hours).In conclusion, we have to manually copy all the total hours worked per week to have the number of overtime hours so I wanted to know if there's a way that overtime can be calculated automatically without having to do anything and that we can delete the column "Total heures travaillées" (total hours worked)?For your information, in France employees can work a maximum of

284
Matthew_Carrano

Badge winners

Show all badges
Terms & Conditions