Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,947 Topics
  • 18,257 Replies
5947 Topics
E
emdeeseeNew Participant
asked in Formulas
Nested SWITCH Formula not returning a value

Hi Airtable Community!I’m trying to set up a formula that will convert a printed price to its digital price equivalent, but the rules for those calculations are wonky. I was able to successfully tell the formula that for items under 10.99, it should subtract 1 or 2 from the printed price (depending on the original format), but my attempts to code a SWITCH clause for any titles over $10.99 (and thus subject to a specific price grid) are just returning empty values.I’m trying to argue that if a book is hardcover and greater than 10.99, then the digital price from a grid should be swapped in based on the print price. Same for if the book is a paperback, but the grid values are different, so it requires its own clauses.Here’s what I’ve got so far: IF({US Price (Printed)}="",CONCATENATE(""), IF(AND({US Price (Printed)}!="",{US Price (Printed)}<'10.98',{Format Type}="Hardcover"),{US Price (Printed)}-'2.00', IF(AND({US Price (Printed)}!="",{US Price (Printed)}>'10.98',{Format Type}="Har

212
TheTimeSavingCo
Graham_Lindsay
Graham_LindsayParticipating Frequently
asked in Formulas
How to update monthly price changes

I run my market garden business on airtable. The market price for my vegetables change monthly or seasonly. At the moment I use a formula field to calculate the return on my production each month . I have a table for each vegetable price but when my prices change how do I stop the new price altering all my previous months. Do I need to use a script?

284
Graham_Lindsay
pixelchef
pixelchefNew Participant
asked in Formulas
Project Status based on Nested formulas

 Working on row status for projects based on conditional fields pulled from external sources but am bad at formulas. I think I am close. Any help on this? . Issues with greater than zero and start dates in the pastIF( {Effort Current Total Estimate}=BLANK(), 'Scoping'IF( AND( {Effort Current Total Estimate} > '0', {Is Approved} = "checked" ), "Dev Pending"IF( AND( NOW()>{Target Start Date}, {Has Human Interface Impact}= "checked" ), "In Design"IF( {Substate}='Nominate','Intake',IF( {Substate}='Investigate','Engage',IF( {Substate}='Fix','In Dev',IF( {State}='Integrate','Feature Complete',IF( {Substate}='Review','Feature Complete',IF( {State}='Verify','With QA',IF( {State}='Closed','Closed',IF( {Substate}='Review','Feature Complete',))))))))))) 

334
Alexey_Gusev
Ken_Szczepanski
Ken_SzczepanskiNew Participant
posted in Formulas
Japanese Year Conversion Formula

I was looking for something like this, but couldn’t find an airtable formula so I thought I would share mine. This only covers the last 3 Japanese era’s.I would be great if something like this could be added to the Set_Locales function for Japanese, but I didn’t see any others that changed the year numbers so it might not be possible.Administratively I have deal with a lot of dates that need to passed to the local government and for birthdays we need to use the era years instead of the Gregorian years.And if anyone has a better one please share.IF(Birthday,SUBSTITUTE(SUBSTITUTE(IF(    IS_BEFORE({Birthday},DATETIME_PARSE("Jan 8, 1989"))=1,"昭和"&(DATETIME_FORMAT({Birthday},"YYYY")-1925)&"年",        IF(        IS_BEFORE({Birthday},DATETIME_PARSE("May 1, 2019")),"平成"&(DATETIME_FORMAT({Birthday},"YYYY")-1988)&"年","令和"&(DATETIME_FORMAT({Birthday},"YYYY")-2018)&"年"))&DATETIME_FORMAT(Birthday,"M月D日"),"令和1年","令和元年"),"平成1年","平成元年"),BLANK())

101
Mike_AutomaticN
Sean_Lake1
Sean_Lake1Inspiring
asked in Formulas
TimeDiff Calc

Hey all,so weirdly enough, when I setup the formula for a total time calculated it shows this: 120 minutes, correct?However the resultant display shows this:Just curious, why is it showing up as 0:02 instead of 120, even though in the sample it shows it’ll be 2?

172
Alexey_Gusev
S
swadminNew Participant
asked in Formulas
Extract text after comma

My primary field is an employee’s full name in this format: Last, First. The catch is that some employees have a middle initial or a middle name, so it looks like this: Last, First Middle.I want to create two separate columns that separately extract the last name and the first name. I want the separate first name column to include the middle name/initial (if they have one).I’ve been able to create the last name field with this formula: LEFT({Person Name}, (FIND(",", {Person Name}, 1)-1))However, when I use that same formula for the first name field except with the RIGHT function it doesn’t pull out the first name correctly for everyone. RIGHT({Person Name}, (FIND(",", {Person Name}, 1)-1))Any ideas?Solved it: SUBSTITUTE( {Person Name}, LEFT( {Person Name}, FIND( ",", {Person Name} ) + 1 ), "" )

522
Alexey_Gusev
P
PickThisBerryInspiring
asked in Formulas
Extract email from User Field

Is there a way to extract a users email address from their account info?Context...I have a form built in "interfaces" and have toggled on "see who submitted response".Ideally I'm trying to extract the domains for the users as a method to sort with different views.Ex. forms submitted by "domainA.com"forms submitted by "domainB.com"

2333
B
C
Caleb_DicksNew Participant
posted in Formulas
Button - Individual URLs

Okay guys, ive seen a similar post about this but didn’t answer what i was looking for. I am wanting to take a url (google.com) and add it to a “open URL” button then another record have another url (youtube.com) and add it to another “Open URL” button. What formula could i use to target the url inside of the button field? SCREENSHOT: https://snipboard.io/A3ucNd.jpg

361
Mike_AutomaticN
B
BenniexNew Participant
posted in Formulas
New Chrome Extension: Airtable Formula Builder with AI 🚀

Hi everyone,I always found Airtable formulas a bit tricky, so I built a Chrome extension called Airtable AI Formula Builder. It lets you describe what you want in plain English (e.g. “combine first name and last name”), and it instantly generates the correct Airtable formula.👉 Extension link: Ai formula builder It’s a freemium tool: you can try it once for free to see how it works, and then unlock unlimited usage with a small upgrade.This is the very first version (MVP), and I’d love your feedback: What formula types do you use the most? What extra features would make this more useful for you?Thanks a lot, every bit of feedback helps me improve it 🙏

395
Mike_AutomaticN
D
dhigbeeParticipating Frequently
asked in Formulas
Combining Two Formulas To Test New Functionality While Not Breaking The Old

We have an intake form to generate URLs with UTMs parameters. I have created a new form with additional functionality. During testing, I need both the old and new forms to work.My current Formula field concatenates the URL (Root Link) with the UTM parameters and values. There is also a check to determine if the URL provided in the Root Link field contains a "?". If it does, then the concatenation output is handled differently:IF(FIND("?", {Root Link})=0,{Root Link}&{Source Parameter}&{Source Input}&{Medium Parameter}&{Medium Input}&{Campaign Parameter}&{Campaign Input (Child Campaign Name_Linked)}&{Content Parameter}&{Content Input},{Root Link}&"&utm_source="&{Source Input}&{Medium Parameter}&{Medium Input}&{Campaign Parameter}&{Campaign Input (Child Campaign Name_Linked)}&{Content Parameter}&{Content Input})My new form has the option to create some additional link types based on content. If you choose that option, the

2110
T
Z
Zabee PashaNew Participant
asked in Formulas
Form

In airtable form the column should be visible when 2 column values together should be 100%. 1st Question:For example, as shown in the screenshot below, in the Airtable Form, when a user updates Tag 1 and Tag 2 so that their total equals 100%, Tag 3 and Tag 4 should no longer be visible in the form. If the tags do not total 100%, all three remaining tags (Tag 2, Tag 3, and Tag 4) should remain visible. 2nd Question:Is there an option or way in Airtable forms to prevent users from submitting the form if the total Tag does not equal 100%? 

444
MargeM
P
PainMed_AdminisNew Participant
posted in Formulas
Simple way to highlight duplicate entry

@ScottWorld , I went through a few options to look at ways to just highlight the duplicate entries as they get entered into a database. I noted u came up with a simple one but there were no steps written down. We have a unique patient ID which is in the primary column , we dont want to duplicate a patient file. Every time we enter we wish to have a look and see if there is a duplicate entry and delete if its highlighted. makes sense?

2146
ScottWorld
felipe-saucedo
felipe-saucedoInspiring
posted in Formulas
Please AIRTABLE improve your formulas

Just wanted to pitch this, and see if you are seeing this as much of a need as I see it. I am a heavy Notion user, which I quite a lot but I overall preffer Airtable except for one thing. NOTION Formulas as far superior. To give an example, you can use dot notation in Notion formulas  sum((Pagos.filter(and(current.Tipo=="Cuota",formatDate(current.Date,"MMMM")==(formatDate(today(),"MMMM")))).map(current.Monto)).first())!-->Here I am getting fro my list of members, all the categories they belong to, and from there all the events there are, and from there all the dates. All within one single formula. This would be so great in Airtable.If I have a level hierarchy Company, Office and ContactThere is no way to bring the contact email to the Company record unless i create a roll up of a rollup so I am creating 1 extra field in Office, whereas in Notion I could simple go for:Office.map(current,Contact.Email) I can even apply filters making it much easier to set up.Am I the only one with thi

333
Mike_AutomaticN
G
GegHNew Participant
asked in Formulas
Currency Formatting removes decimal places

I’m trying to get text and currency to combine and show properly with 2 decimal places even if they are 0.00.I have a product field (with text), a value field (with Currency)If i did a simple Formula {Product} & “ - $” & {Value} the result makes $18.30 from {Value} turn into $18.3 and $20.00 turn into $20, removing any 0’s after the decimal.Through research and help we can do the following equation:CONCATENATE({Product}, " - $", IF({Value}, ROUND({Value}, 2) & IF(FIND(".", ROUND({Value}, 2) & " ") = LEN(ROUND({Value}, 2) & "") - 1, "0", ""), "-.--")," "&{/Unit})This will put in 2 decimal places unless they are .00, then it reverts to suppressing the two 0’s after the decimal place, how do i get them back?

232
G
S
SabrinaTableNew Participant
asked in Formulas
Calculate the average of top 20% and bottom 20%

Hi 👋How can I get the average of the top/bottom 20%? For example, I have this table:Name | Sales Sabrina | $10 John | $5 Maria | $20 Ann | $100 Ben | $5 Sabrina | $10 John | $5 Maria | $20 Ann | $50 Ben | $1I want to see:Top 20% (average sales from top 20% sales): $75Average: $23Bottom 20% (average sales from lowest 20% sales): $3 Thanks!

1096
Alexey_Gusev
T
thelusInspiring
asked in Formulas
Formula when an event ends - optimizing NOW()?

I’ve got a base with an “Event Finished” formula field that works like this:IF(DATETIME_DIFF({End}, NOW()) < 0, TRUE(), FALSE())It resolves into a checkbox, and I use it to filter out finished events from admin list views. My base currently sees about ~15-20ish events a day, which have times attached, and the folks that need to monitor the views have told me they find it really annoying when finished events aren’t filtered out of a given list view.And it works fine! I could be using IS_AFTER rather than DATETIME_DIFF, but otherwise, it does the job. However, I know NOW is a function to avoid. Its inaccuracies aren’t a big deal to me (if the formula clears out an event 15 minutes later than it should have, that’s not the end of the world), but its computational cost is becoming more of an issue. I assume NOW continues to recompute for every record, even those that are well beyond completed at this point. I’m trying to optimize things so NOW gets called less, so here are some question

533
T
R
rmmKnown Participant
posted in Formulas
Small-caps in rich text field

Hi there, I am wondering if it is possible to have small caps in an AirTable rich formatted field. If so, how can it be done? Can it be done with a formula? How about inline css? Would this work? <p><span style="font-variant: small-caps;"></span></p> Any help would be greatly appreciated. Thank you.

326
Mike_AutomaticN
C
christophKnown Participant
posted in Formulas
Access the URL of an Airtable attachment using a formula (after attachment update)

Hello everyone,Who can tell me the formula to get the attachment URL in the new https://v5.airtableusercontent.com/... format? The one below only returns the format https://dl.airtable... which no longer works. IF( AND( {Attachment}, FIND( ",", {Attachment} ) = 0 ), RIGHT( LEFT( {Attachment}, LEN( {Attachment} ) - 1 ), LEN( LEFT( {Attachment}, LEN( {Attachment} ) - 1 ) ) - SEARCH( "https://", {Attachment} ) + 1 ), IF( {Attachment}, RIGHT( LEFT( RIGHT( {Attachment}, FIND( ",", {Attachment} ) - 1 ), LEN( RIGHT( {Attachment}, FIND( ",", {Attachment} ) - 1 ) ) - 1 ), LEN( LEFT( RIGHT( {Attachment}, FIND( ",", {Attachment} ) - 1 ), LEN( RIGHT( {Attachment}, FIND( ",", {Attachment} ) - 1 ) ) - 1 ) ) - SEARCH( "https://", RIGHT( {Attachment}, FIND( ",", {Attachment} ) - 1 ) ) + 1 ) ) )  Thank you!

28214
contact82
Y
Yves56New Participant
asked in Formulas
Testing a single-select field where the options have the same name ?

Hello,I really need tour help for this trick 🙏 !I have a single-select field where there is no text, only different color to choose, displayed in interfaces as a colored-circle.Now I want to create an other field, with a formula using the “value” of this previous field.But impossible to test the otion’s rank, for instance.I cant insert any text in my option, but I really need to reuse this result… Does somebody could help me ? Thanks ! 

454
Y
K
kellymoore1244New Participant
asked in Formulas
Date (rollup) field not syncing to another base

All fields are operating as expected in the original base.  I have synced the table to another base and all fields operate successfully except for the date (rollup) field.  The date field is not syncing properly.  Below is how the rollup field is set-up in the original base.I have confirmed the date formatting in all bases are identical. Any ideas on why this might be happening? Thanks!  Rollup field in the original base (successful) Original Date Field  

364
K
T
Tiago CabrialNew Participant
asked in Formulas
Formula for linked records

Hi Team,Need help to get this function running.I’m trying to build a formula that could work as linked field.So far, I managed to create the formula to combine the columns into “Product” column. However, I still need to copy past to the column “Products” to link to the Products table.Wondering if there is any way to create formula on linked field to make it all in one.Or at least, a script to create the links without need to copy past every time. 

504
Alexey_Gusev
D
Drug_ServicesNew Participant
asked in Formulas
Create formula or automation to Combine duplicate records and concatenate record details

I have a table with 8000 records that lists out where in our warehouse we keep our items. I’d like to group the locations of the same item by listing the locations in one cell and deleting the duplicate records. How could I go about doing this? ExampleItem                                      LocationItem 0001                             Shelf 1AItem 0001                             Shelf 1CItem 0002                             Shelf 1BItem 0002                             Shelf 3AItem 0002                             Shelf 5CItem 0003                             Shelf 1A Desired outputItem                                      Location        Item 0001                              Shelf 1A, Shelf 1CItem 0002                              Shelf 1B, Shelf 3A, Shelf 5CItem 0003                              Shelf 1A

362
D
F
FeralhogNew Participant
asked in Formulas
Is there a formula to sort cell items based upon the decimal value?

I am no geek when it comes to sheets, most of the stuff I do comes from watching a video on youtube. I have a retail booth at a flea market and I get  my items from various resources, Some items come from estates that I buy, some from garage sales, some from a local auction house etc. I use the cents in the pricing to help identify where the items were purchased. For an estate sale, the price would be, for example, 10.31, so I can look at the daily sales report and immediately identify every item sold ending in .31came from the estate sale. What I’m wanting to do is  list the different sources I use and have sheets track and total each item based up the cents.  So in the Cell that has a heading Estate Sales, create a sum formula with the criteria of searching the sales report looking for every item sold that ended .31 Thanks in advance for the replies.  If this isn’t possible, is there a way to copy and paste selected random cells.  When I highlight multiple cells using the ctrl tab, c

392
F
Melissa_Frank1
Melissa_Frank1Known Participant
asked in Formulas
Swaps and Stats

Hello! I am struggling to create an easy database template for importing data from a manual CSV export with some weirdness to it. Then an interface for data visualization that once was done using a platform that linked directly with the app generating the data. The app generating the data exports two CSV files, one for a list of “interactions” and one for a list of “buttons”, so I started with a table for each with the same fields. Each interaction row contains a lot of data pertaining to the user, so I added a 3rd table of Users in Airtable, and just made those fields in the Interactions table lookup fields to pull from Users. The Interactions can have up to 12 Buttons, and the export has several columns for each button - some of it is unique to the interaction and some applies to every use of that button, so I have put that info in the Button table and the fields in Interactions are now a lookup. The Interactions records also include date and time of the start of the interaction, and

13022
J
H
hhopkinsNew Participant
asked in Formulas
Filtered Rollup Column

Hi,One table has the entire list of our shows with an ad budget attached to each. Another table has an entire list of each ad placed, but our ads include multiple shows in each one so there’s already a linked record there, then a formula to calculate total ad spend versus how many shows are in that one ad so that we know how much is spent on each show per ad.Is there a way to use a rollup field or formula field to add up our ad spend per show and attach it to our show list in the first table? I need it to calculate correctly for each show, not just spit out the total amount.  

374
Mike_AutomaticN

Badge winners

Show all badges
Terms & Conditions