Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,939 Topics
  • 18,228 Replies
5939 Topics
A
Anna_YangNew Participant
posted in Formulas
How to Cross-Check Linked Records (a Record Has No Links)

I keep track of articles that I publish online and their internal links to other articles I’ve written. So the table is structured like this: Field 1: Article Name Field 2: Related field with multiple select, choosing articles from Field 1 In real life, it’s something like this: Article 1 has internal links of Articles 2, 3, and 4 Article 7 has internal links of Articles 1, 3, and 2 I want to know which records from Field 1 do not exist in Field 2. In other words, which articles have I published that have not been internally linked in anything else I’ve written. In the above, Article 7 would be an example of the data returned - it has not been an internal link of another article (exists in Field 1 but not in Field 2). I’ve tried a few different things but can’t quite get the results I’m looking for. Can anyone help?

41
TheTimeSavingCo
B
Brenda_SniderNew Participant
posted in Formulas
Formula for totaling check in and check out hours for Study Hall

Hi Everyone, I know very little about formulas and only a little about designing a base. I am trying to create a table to track the hours of those attending study hall. Here is the screen shot I have a form set up so the student can check in then check out. I could not figure out how to make the form reappear so they could check in and later check out on the same form. So I created one form and they have to check in and when they leave check out. This formula is wrong but maybe you can get an idea of what I want to do: IF(IDs = IDs, {Check In Date-Time} = {Check Out Date-Time}DATETIME_DIFF({Check Out Date-Time} - {Check In Date-Time})

192
ScottWorld
L
Luke_ConcannonParticipating Frequently
posted in Formulas
What can we expect from Record_Id string?

It starts with ‘rec’… but what else can we guarantee? I want a solid Regex or set of expectations to guarantee finding a Record_id string. So far I just have 'starts with ‘rec’ and Regex is made of 12 or more alpha-numeric characters.

10
L
D
Denys_Saisse1New Participant
posted in Formulas
Substitute Multiple Strings

Hi everyone, Hope you’re going well. I’m struggling with a formula. I have 3 colomns: #1 - All Time Slots Rollup A, B, C, D #2 - All Booked Time Slots Rollup A, C #3 - Formula Colomn that substiture Colomn #1 data with Colomn #2 strings. B, D I found the formula to make substitute one string. SUBSTITUTE(colomn1&"",colomn2&"","") Unfortunaly this formula doesn’t work when there is more than 1 string in colomn2. What formula should i use ? Thank you in anticipation :grinning:

93
Alexey_Gusev
A
Airtable_ClerkKnown Participant
posted in Formulas
Github Copilot and Airtable Formulas

I use VS Code for Airtable formulas as well as for my scripting. Airtable formulas are close enough to JavaScript in syntax that I just tell VS Code my formula is JavaScript. I noticed that Copilot often gives me some reasonable suggestions as I’m writing up a complex formula. I wouldn’t pay for Copilot just for formulas, but it’s a tiny added bonus. Anyone else use Copilot and seeing decent suggestions for formulas?

21
B
J
JBorgNew Participant
posted in Formulas
Nested IF stmt to calculate a tax rate

I’m trying to create a formula field that’ll output a sales tax amount based on another field in the base. So: IF {Tax Type} = HST, multiply {Subtotal} by 13%, IF {Tax Type} = GST, multiply {Subtotal} by 5%, IF {Tax Type} = “Zero-rated”, type “0%”, else leave BLANK I’ve tried several iterations of this nested IF statement and always have issues. The latest was: IF( {Tax Type} = “HST”, “13%”, IF( {Tax Type} = “GST”, “5%”, IF({Tax Type} = “Zero-rated”, “0%”, BLANK ) ) ) And I keep getting errors about the closing parenthesis, however, this was based on the Airtable Support article for nesting three or more IF statements in a row, so I’m confused. Can anyone help? Thx.

221
V
S
Sahil_MehtaNew Participant
posted in Formulas
Need help with if formula with multiple options

Hello, I need quite help with a formula. Currently, my formula is as follows which says If FIELD A = “Hubspot Invoice”, then use Field B to populate cell, otherwise use field C. currently, my formula is IF({FIELD A)}=“HubSpot Invoice”, {FIELD B}, {FIELD C)}) however, I’d like it to say if “FIELD A = HUBSPOT INVOICE” OR FIELD A = “ZOHO INVOICE”, Field B to populate cell, otherwise use field C. How do i capture the two conditions in the If field?

71
V
I
Isabel_SanchezNew Participant
asked in Formulas
Several formulas in one field

Hi, im just beggining my learning journey with Airtable and im trying to configure a field wchich should pick a value from a switch and also if it finds an option on a multiple selection field then add a number to the result. This is what i ve been trying so far, but it doesn´t work. SWITCH({Clases a la semana},1,35,2,65,3,90,4,110,5,130,6,150,7,170,8,190,9,205,10,220,11,230,12,240) & IF(FIND(“Tips Club”, Horario), +40,0) Thank you in advance

832
I
P
Paul1Inspiring
posted in Formulas
Data corruption when using LEN and FIND with emoji's

Airtable is measuring emoji length differently under functions LEN and FIND. In the screenshot, the two fx fields have the formula as the field name. With LEN, Airtable is measuring the length of “ :stop_sign: 23” as 3 [as if :stop_sign: =1]. With FIND, Airtable is saying the position of the “3” is at position 4 [as if :stop_sign: =2]. Emojis have differing character lengths, but it should be consistent within Airtable. When using LEN & FIND to extract data from an array, the results fail. In a large array, adjusting with a ‘remove the emoji>then put it back’ strategy is a huge sloppy mess. Any better ideas?

101
V
L
Lucie_TennisNew Participant
posted in Formulas
Rolling Average?

Hi there, I’m trying to figure out how to calculate rolling 3, 6, 12 month burn rate. What formula can I use to automatically calculate and update burn rate (or other average) based on number of past months? Thank you!

143
kuovonne
T
Tendi_MuchenjeNew Participant
posted in Formulas
Getting latest discount for client in table of transactions

I have a table that has among other fields , OWNER, DATE OF MEETING, DISCOUNT. It has several different owners who I have met at different dates and given different discounts. I want to create a view that will show me only the OWNER and the last DISCOUNT I gave them at the most recent DATE OF MEETING. Nothing else. I have tried to create a column with formula MAX(DATE). Didn’t work. I also tried to group the records by owner then apply a Max, but I may have done it wrong because it did not work either. A Any assistance with a solution would be greatly appreciated.

102
ScottWorld
F
Fred11New Participant
posted in Formulas
Create a view with partial (but closes) matches from 2 differents tables

Hi all! Would it be possible to use Airtable to find partial (but closes) matches in 2 differents tables (specific cells) and show the associated lines data? Ex. In table 1, in the choosen column for this query, there is a cell with ‘behind the word mountains’ In table 2, in the choosen column there is a cell with ‘behind words mountains’ I would like to have a view of the datas of both lines from both tables, when cells match partially.

21
kuovonne
Scott_Gardner1
Scott_Gardner1Known Participant
posted in Formulas
Attachment formula - len

Hello- I have this formula: IF({DESIGN: UNDER REVIEW},SUBSTITUTE( RIGHT(‘’&{DESIGN: UNDER REVIEW},1+ LEN(‘’&{DESIGN: UNDER REVIEW})-FIND(‘https:’,‘’&{DESIGN: UNDER REVIEW},LEN(‘’&{DESIGN: UNDER REVIEW})-133) ),‘)’,‘’ )) And what it does is it takes 20 files in one cell and extracts the latest image in on the right to always show in a new field. The issue is that it’s not recognizing some and the URL is blank that I am using the automation. Does it matter if I have PNG, PDF, MP4, GIF files all in one - wonder if that is causing it to break. Thank you!

173
Scott_Gardner1
A
Abdelraouf_AlkaNew Participant
posted in Formulas
Calculate the difference between two dates and add the first and last day

Hey all! I have recently started using Airtable. I have two date fields that show the number of days between the two dates, and I want to add the first and last day .

61
V
M
maradasNew Participant
posted in Formulas
Finding the Next Upcoming Date in a Rollup Field

Hello all! I have a somewhat tricky formula question that I’ve been banging my head against the wall over. I have a rollup field that compiles a list of dates. I’d like to pull the next date from that rollup field; in other words, the upcoming date that’s closest in time to today. Here’s an example of the field: And what I’m looking for is a formula field that will output the following: Row 1: 3/11/2023 Row 2: 11/30/2022 Row 3:12/29/2022 Does anyone have any ideas?

202
Kamille_Parks11
R
Rabbi_Yaacov_SeNew Participant
posted in Formulas
Barcode Point System Help

I am trying to make a point system for my students. I want to use barcodes to look up student names and to enter how many points they have. I tried setting it up but I don’t know how to cause a barcode input to find an populate a value into a record. Please help me understand what I am doing wrong.

404
TheTimeSavingCo
A
Allan_MayesNew Participant
asked in Formulas
Display date duration with a decimal format rather than whole numbers

I’m hoping someone can help me with a simple formula. I currently have a formula that counts the months between a start date and today’s date and displays it in months as whole numbers (ie, 2 months, 5 months, etc). The issue with just a whole number for months is that at 3 months and 29 days, it will show “3 months”, and at 3 months and 1 day, it will also show as “3 months”. A more accurate figure would be helpful. I also need the figure displayed in months, so the display in just weeks (ie, 9 weeks) or just days (ie, 52 days) wouldn’t be helpful in this case. I’m hoping there is a way to make it display in months with one decimal place (ie, 2.3 months, 5.7 months). I’d also settle for something similar such as “2 months 14 days” or even “3 months / 2 weeks”. I’ve tried using the formatting function within the settings and set it to “decimal” with a precision of “1.0” but it still just shows it as the whole number for the month with a “.0” at the end. For example, 3 months and 2 week

512
A
D
Duane_VillaNew Participant
posted in Formulas
How to implement ArrayFormula(Vlookup)) in Airtable

I have 2 Tables. Table A contains a field for a start date and a field for an end date and a unique ID field. Table B contains a date field. What I want is on Table B, when I input a date, I want it to return the unique ID field on which the date in Table B falls in, relative to the Start Date and End Date in Table A. This is Table A This is Table B My input field in Table B is Col B, and the output is Col C. The formula I found on Google Sheets to make this work is =ARRAYFORMULA(IFERROR(VLOOKUP(A2:A,‘INPUT - Live Streams’!A2:C,3,1))) Is there a way I can implement the same setup in Airtable?

20
D
WilliamPorter
WilliamPorterBrainy
posted in Formulas
Identifying family groups

Base is registration and records system for small private school that’s been operating for 60 years. The two tables I’m working on now are PARENTS and CHILDREN. In another thread here, I was trying to figure out how to copy addresses from linked records, but I think I’ve solved that question by using a formula field that calculates an address for each parent or child based on what records its linked to. In other words, if parent1 has an address, and parent1’s children and spouse do not have addresses, those records will all pull an address from Parent1. Minimizes duplicate entry and seems to work okay. The question My question is, in the PARENTS table, how can I identify marital pairs? I’d like to group them. One thing I have tried (that doesn’t work well)… For some reason (mystery to me), grouping by the Address formula field does not seem to work. Say Mary and John are married and reside together. Mary’s record, which was created first, stores the address at which she and her husba

246
WilliamPorter
A
Alex_Long1New Participant
posted in Formulas
Is there an easier way to create Airtable formulas programmatically rather than typing SQL-ish strings?

Having to type formula strings when using something like Node.js to interact with the API is super difficult and tedious, especially with more complex queries. Since the formulas are strings the editor can’t provide any syntax correction tips about missing parenthesis or valid/invalid uses of various functions like FIND, AND, etc. Is there any easier way to create these queries? I put an example query I’m trying to create below which I’m completely failing to get working, although I have no idea why because there’s zero syntax feedback. I made it by joining an array of indented strings to provide myself with a little debugging sanity but that only helps so much. const filterConditions = [ "AND(", "OR(", `AND(FIND('${userId}', {uploader}), FIND('${teammateId}', {recipient}))`, ", ", `AND(FIND('${teammateId}', {uploader}), FIND('${userId}', {recipient}))`, "), ", "IS_AFTER({createdAt}, DATETIME_DIFF(TODAY(), -14, 'days'))", ")", ].joi

129
kuovonne
A
Alix_MarieNew Participant
posted in Formulas
Compare and match 2 columns in 2 different tables

Hi everyone, I have 3 different tables: prospects (looking for a place) Landlords (renting a place) Matching table On the 1 and 2nd table, I have a column named “type of place” that is a single select option. People can then fill “House” or “flat” or “shop” for example. I would like on my 3rd table to be able to indicate in a column if there is a match between the answer of a prospect on table one column “type of place” and a landlord table 2 column “type of place”. I will then have a column : “Name of prospect” + a column “Name of landlord” + a column “Matching place” and the name of the matching criteria (eg: house). Someone knows how to do that? :slightly_smiling_face: Thanks a lot! Alix

572
M
S
Sobhy_SonbolNew Participant
posted in Formulas
Extracting a number from a strig

Hi all I am trying to convert strings with numbers in them to just numbers. So the string is in that form R.N.A.(335,000) I just want the number The number ranges from 0 to 10,000,000 Thank you in advance

426
ScottWorld
J
Juraj_IvkovacNew Participant
asked in Formulas
Formula - if statement and concatenate

Hey clever people, I got following single line txt fields in my table: {Prefix}, {First Name}, {Last Name}, {Suffix}. I already use a formula to concenate this data: CONCATENATE({Prefix}," “,{First Name},” “,{Last Name},” ",{Suffix}) … this works fine. My problem/question, is it possible to make a formula that will put between {Last Name} and {Suffix} a comma if {Suffix} is not empty/null, instead of the space like above. If I replace the space with a comma it will obviously be everywhere, what I don´t want. For example: Prefix (Dr.), First Name (John), Last Name (Miller), Suffix (M.Sc.) - Formula Result: Dr. John Miller, MSc. Prefix (Dr.), First Name (Leroy), Last Name (Thompson), Suffix (null) - Formula Result: Dr. Leroy Thompson I hope you understand what I mean :grinning_face_with_big_eyes:

287
Alexey_Gusev
C
Chelsea_Boss1New Participant
posted in Formulas
Creating Sumif in Airtable

I am attempting to perform a task in which I’d typically use a Sumif formula. I’m trying to “sum” the extended price “if” the sales rep column equals the reps name. For example: Extended Price Sales Rep $400 Mike $250 Justin $150 Mike Result: Mike = $550 Justin = $250 Is there a formula / work around for this?

31010
A
L
LAZY_APACHENew Participant
posted in Formulas
Airtable simple maths, please help! I will put in the start record everyday, and the used KG from yesterday. a formula will calculate the yesterday finish record. please see picture

72
ScottWorld

Badge winners

Show all badges
Terms & Conditions