Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,942 Topics
  • 18,244 Replies
5942 Topics
F
Francoloco23Known Participant
posted in Formulas
Formula to calculate Due Date

Good day Airtable Team,I would like to ask for your assistance in a formula to compute for the due date based on the following scenarios:1. Due Date if Rush Tag is Yes: Variables to be used would be "Extracted for Ticketing" and "Rush TAT".2. Due Date if Rush Tag is No: Variables to be used would be "Extracted for Ticketing" and "Rush TAT".Thank you   

27
F
Y
yuannc12New Participant
asked in Formulas
How to count single record's completion rate

Hey I would like to know if there is a way to create a  summary per record instead of per filed? like, how may empty per record? Many thanks!! 

53
Ben_Young1
A
areisingNew Participant
posted in Formulas
Calculate GPA

Inside this URL, I need to create a formula if PHYS is 100 or above and ASTR is 200 and above then divide the column of quality points by the column of credits. I also need to not include P, NE and add 4 credits if there is an F. Any ideas?

10
A
C
chapelnoNew Participant
asked in Formulas
Help w/ search formula to find records that contains two values

Airtable novice here, need help crafting a search formula that will find the record that contains both a First Name and Last Name.  So in the attached screenshot, I will provide "Harley" and "Comber" and need Airtable to flag record 3 as satisfying the search.  I can't search based on an exact match because sometimes the name as it exists in the table includes a middle initial, full middle name, jr., sr., etc. that the document I'm pulling the first name and last name off of to do the search does not include.  But I will always have the first name and last name available to search.  This is going in a Zapier routine that will provide the first name and last name for the search and then return the correct record so I can use the corresponding Matter ID in future steps.  So, basically I need a formula that instructs Airtable to find the record that includes both the [First Name] and [Last Name] variables.  Thanks for your help.

332
C
K
Kosta_KondratenInspiring
asked in Formulas
Lead Scoring by Calculating Multi Select Field Value Rollups

I am looking to score my leads in my Airtable which I use as a CRM. I have a multi select field called 'Journey' which has a number of events in the lead life cycle - for example an IOI (indicator of interest) of the prospect asking about the price, or the timelines - what I would like to do is rollup those Journey steps which are in the 'Touchpoints' table and have a lookup field for the Contact into a Contact rollup field and calculate the lead score which would involve assigning unique values to the Journey steps. For example an IOI - Pricing might be 5 points, an IOI - Timelines - might be 7 points (as an example) - adding all those points together and having a lead score. Hope that makes sense. Any input of how to make a formula like this would be much appreciated. Video below for reference also:https://share.getcloudapp.com/yAuAwJdW

252
K
A
alternazInspiring
posted in Formulas
IF and multiple choices

Hello community!I have a column with 3 simple choices "number of trucks" and I wants to convert these choices into figures in a "quantity" column. IF({number of trucks} = "3 trucks of more", "0", )IF({number of trucks} = "1 truck (24 tons)", "24",)IF({number of trucks} = "2 trucks (48 tons)", "48",)I can't close my formula, do you have an idea?

41
TheTimeSavingCo
L
LanNew Participant
posted in Formulas
How to get the months that have not yet been paid

Currently, the yyyy-mm that were paid are stored in a lookup field named Month Paid For.For instance, we only paid the vendor until 2022-11 even though we were supposed to pay them as of 2023-2. How do I obtain the months that have not yet been paid? ( 2022-12 , 2023-1 , 2023-1). Thanks !

00
L
M
Marika_VallierParticipating Frequently
posted in Formulas
Formula for conjunction between leave for people in the same team

Hello!Here is what I am trying to create:A formula that results in telling me if the same collaborator's scheduled leave is going to conjunct with another collaborator in the same team.This is what I am working with:leave start dateleave end dateTeam Slack ID which is a lookup field directly connected to the collaborator selection eg. if Mary JONES selects her name, the Slack ID for her teams HR and Marketing will show.I am trying to build a formula that will:be able to identify conjunction between leave periods.search if this conjunction is happening between collaborators who work in the same team.Has anyone some ideas to share?

62
M
M
MaxGNew Participant
posted in Formulas
Importing Signup Genius Custom Question Field

Hello all, I have linked a SignUp Genius signup to populate a student table in my Airtable database automatically.  I have 3 custom questions, and Signup Genius exports those ALL in one custom questions field separated by commas.  I need to separate those responses into 3 separate fields in my base and cannot figure out how to set that up. One of the fields is a linked field to another table in my base. I'm assuming that I have to import that into one field in my table, then set up a formula in a different field that separates the comma-separated values.    I have no clue how to do this. 

90
M
K
krhotenNew Participant
posted in Formulas
Ranking Projects and adjusting other project rank numbers automatically

Hope you guys can help! I have a list of projects ranked 1-28. If a new project is entered and the rank is set at #8, I would like all the following numbers to be adjusted. (i.e. original #8 becomes #9, original #9 becomes #10...).  Is there a clean way to accomplish this? Field type is "number" currently. Hoping to find a formula but that might not work.  

40
K
E
EvanCaseyNew Participant
asked in Formulas
Formula Error - Switch Function

Hi all, Semi-new user to the tool here, and still learning the formulas. I have the following issue: I am using the switch function to categorize certain single select options into different categories (in this case, which statuses in the HR systems relate to an overall designation of accepted, dispositioned, interviewing, etc)My full formula is below. The issue is the very last line item: 'Doesn't Meet Min Qual', 'Dispositioned'This line has the apostrophe in the word "Doesn't", and that seems to be invalidating the formula. Everything worked great until I added that line, and then it breaks. I unfortunately can't change the wording of the option, since it comes directly from our system. Is there any way to get the formula to work, and ignore that specific apostrophe? SWITCH({Current HR Status}, 'Prepare Offer - Pro', 'Accept', 'Send Offer for Approval - Pro', 'Accept','Hold&n

20
E
R
rbnNew Participant
asked in Formulas
Multiple Task Completion

I have three training sessions, and participants need to have completed all three in order to proceed. I'd like to have a formula that shows 'completed' when all three = completed and 'incomplete' when none/not all are complete.Thank you!

11
Jack_Manuel
E
evernetNew Participant
posted in Formulas
How do I join an URL column with a text colum

I want to link my URL column with my title column so that the Title is linked to the URL. Instead of having the URL, I have the Title but it is link to the URL, creating an hypertext link. 

30
E
H
hsindelarNew Participant
posted in Formulas
Auto-populate date based on entering one date at the top

I'm looking to auto-populate some date fields. Right now, you add the date and the day of the week auto populates but I'd like to be able to enter 2/14/23 in the "EST Release Date" field and have the rest of the dates in the column auto-populate. Is this possible.I'd also be copying and pasting multiple sections into one grid so each chunk would have to be independent of another chunk...if that makes any sense. I know how to figure this out in excel but I'm hoping this is possible in airtable, too! Thanks!   

11
TheTimeSavingCo
T
Trisha_Poole2New Participant
posted in Formulas
Calculate distance between zip codes

I'm looking for a workaround to populate the distance between two zip codes. I need to identify which office is closer for each employee. I have input all the zipcode coordinates with a linking structure in the base. https://airtable.com/shrwDRIKVyoBphDBk/tblNsf1iP5agssIZS/viw5teuUhoedEPQA6?blocks=hide The excel formulas I would use don't work in Airtable. See the formulas in the base under the "Miles to office ONE" and "Miles to office TWO"I'm a beginner when it comes to formulas in Airtable so I'm hoping someone can help. I don't have any experience with JavaScript or coding. 

231
WilliamPorter
H
Hairtable11Known Participant
asked in Formulas
Error using WORKDAY in formula with holiday dates to exclude

I'm puzzled by an error I am coming up against ... We pay our service providers' invoices (received by Monday) ten working days later (so, typically two weeks later). Using this formula, I get the result I want for a date of Monday 20/2/2023 being Monday 6/3/2023:  WORKDAY({Send your invoice by}, 10, '01-01-2023, 02-01-2023, 07-04-2023, 10-04-2023, 01-05-2023, 08-05-2023')  When I add the next public holiday date into the formula, I get an ERROR: WORKDAY({Send your invoice by}, 10, '01-01-2023, 02-01-2023, 07-04-2023, 10-04-2023, 01-05-2023, 08-05-2023, 29-05-2023')​ I don't understand why adding that date breaks the formula, can anyone advise please?

402
H
M
mku90New Participant
asked in Formulas
Extracting free form text from a long text field

In jira we have an Acceptance Criteria field with LOTS of text and we sync this to airtable because airtable offers more fields necessary for the work we do.I want to be able to create a formula that finds and returns the free form text after a specific string of text in the long text field.For example from the long text example further below I want the formula to SEARCH the {Acceptance Criteria} field for  “Creative_Type: " and return the text after it (maybe use “RIGHT”?) but before a hard return and the result in this case would be SIG I’ve created rules where the options are a static list (for example “Email Type: “can only have a certain selection so my formulas account for that but when the text is free from I just want that field to populate with the free form text that shows after “Creative_Type: “  I assume it would have to be a RIGHT and calculating the length or isolating anything after the "Creative_Type: and before the hard return or do i have to create a combo f

202
M
C
corb1Inspiring
posted in Formulas
Help in counting integers within a rollup field array

I have a list of unique ID+count information in a rollup field. See sample data below.16389-0*4, 70895-0*2, 53429-0*2, .....Everything before the * is an ID, and the integer following the * is a count for that ID. I would like to strip everything prior to the star, leaving:4,2,2,....Then I'd like to sum those values.Is that possible?

113
TheTimeSavingCo
P
paulineeeeNew Participant
posted in Formulas
How to create a filter with a date range? (e.g. filtered by past month)

How to create formula that filter the rows by last month?The filter function doesn't allow a date range (which is strange). e.g. If I want to filter rows with dates fall within February 2023, how do I do that? Thanks a lot!

131
TheTimeSavingCo
H
Hanna_Kirana_ApNew Participant
posted in Formulas
Please help with the formula

I have column A with the breakdown option and have columns B and C, what is the formula if someone chooses A1,A2 it will link to column B and if choose A3,A4 it will link with column C?

31
TheTimeSavingCo
P
Pinkxana12New Participant
posted in Formulas
0 code

hello, I need help, I have my airtable automate with make for my bnb reservations. my lock generates a temporary code then add it in airtable. msis if the code starts with 0 , the 0 disappears this is very problematic for customers. how to do ? the code always has 10 numbers 

121
pressGO_design
F
Francoloco23Known Participant
asked in Formulas
Formula to Extract a Certain Text

Good day team,  I would like to ask let's say for example I have these email addresses and I want to use a formula to get "ABC" to "ABC Company Philippines." What would be the correct formula?For your reference, this is the formula that I am using right now. IF(RIGHT({Email},LEN({Email})-FIND(".",{Email},FIND("@",{Email})))="abc", "abc company Philippines",IF(RIGHT({Email},LEN({Email})-FIND(".",{Email},FIND("@",{Email})))="bcd","bcd",IF(RIGHT({Email},LEN({Email})-FIND(".",{Email},FIND("@",{Email})))="cde","cde", RIGHT({Email},LEN({Email})-FIND(".",{Email},FIND("@",{Email}))))))

125
F
A
allannahjacksonNew Participant
posted in Formulas
Calculate total in list view of values in the level below

I am trying to use the new list view to create a system for putting together quotes for events. I have a list of event enquiries in table 1, and in table 2, have created a list view, where I am adding line items to events using levels. I would like to have an auto calculated field in the event enquiry item that gives me the total cost for all the line items I've allocated to that event. I've attached a screenshot of an example. I assume the "Total event cost" field needs to be a formula - can anyone help with a formula that will do this calculation?

913
TheTimeSavingCo
H
Howard_GoldsteiKnown Participant
posted in Formulas
Extract Somewhat Inconsistent Text and Delete

Hello!  I have text of the form:10 x 8 inches (image)25.4 x 20.3 cm16 1/8 x 13 5/8 inches (frame)41 x 34.6 cmin a field called Image Size.  This is how the text came in from an Excel spreadsheet.  There did not seem to be a Delimiter I could successfully use to separate the text previous to my importing it to Airtable.  I want to take the string on the line that precedes "(frame)" and that follows "(frame)" (basically the inch and cm dimensions of the frame as opposed to the image size) and extract it to a new field (the formula field), while, hopefully also deleting it and the cm dimensions following "(frame)" on the next line from the original Image Size field.  Sometimes "(frame)" shows as  "(frame size)".  Thank you very much in advance for any assistance!

32
H
P
Philippe_Creve_New Participant
asked in Formulas
Help with a formula

Hello Everyone,Is there a formula for multiple values I set to equal "Success"? ( put several values like "1234" which would be equal to "Success") please thank you in advance,Best regards,

124
Ben_Young1

Badge winners

Show all badges
Terms & Conditions