Formulas

I’m probably overthinking this, but struggling to find the right formula to accomplish the following: I have a list of addresses in abbreviated format “123 Main St”, but some addresses have numbers on the end (“123 Main St #2A”). I’m trying to find a way to extract the “St” without affecting addresses with street names like “State” or “Sterling”. It doesn’t need to be perfect, so addresses with St that have a number after them can remain in that format. I’m thinking I need a formula that combines IF, RIGHT and SUBSTITUTE, but can’t quite get it. IF “ST” is contained in the last two characters, extract them. Thanks, Airtable!

Hi everyone, struggling to pin down a formula… My goal is to track investments over time (i.e. date of deposit, how much was deposited, what the total amount was in my wallet at the time of that deposit, and finishing with profit/loss since I began). The formula I need is for profit/loss since starting. I figure this value is “Total Amount After Deposit” minus the SUM of all values in the “Converted” field (which are all deposits converted from EUR to USD on the investment platform, i.e. Converted1, Converted2, etc)… So how do I get the SUM of ALL Converted values into the formula? (See screenshot for reference) Any questions/clarification required, please let me know. Thanks! Christopher

Hello Airtable community! I’ve been cobbling together a Base for managing my freelance business which includes tracking bank transactions, expense categories, clients, projects, billable hours, etc. I’m attempting to recreate the 2021 Tax Rate Schedule for the Single filing status from the 1040-ES. I believe I’ve gotten most of the income tax bracket arguments to trigger properly but am stuck for any amount over $209,425.00. Adding even $0.01 to that quantity ends up with a seemingly empty result. Where am I goofing up my formula? IF(AND(INCOME >= 0, INCOME <= 9950), (INCOME - 0) * .1 + 0, IF(AND(INCOME > 9950, INCOME <= 40525), (INCOME - 9950) * .12 + 995, IF(AND(INCOME > 40525, INCOME <= 86375), (INCOME - 40525) * .22 + 4664, IF(AND(INCOME > 86375, INCOME <= 164925), (INCOME - 86375) * .24 + 14751, IF(AND(INCOME > 164925, INCOME <= 209425), (INCOME - 164925) * .32 + 33603, IF(AND(INCOME > 209425, INCOME <= 523,600), (INCOME - 209425) * .35 + 4

I have 2 different kinds of {App Pseud} formats, e.g. kay_obsessive goblin fic (Kiestan) Then I have a formula to spit out a URL, where the concatenate is the important bit: IF(OR({Work Submission Pseud}!={Synced Participant},{Assigned Writer(s)}=BLANK()),BLANK(),CONCATENATE("https://autoao3app.firebaseapp.com/#/mfd2021_round2/user/",SUBSTITUTE(TRIM({App Pseud})," ","%20"))) And it spits out these URLs: https://autoao3app.firebaseapp.com/#/mfd2021_round2/user/kay_obsessive https://autoao3app.firebaseapp.com/#/mfd2021_round2/user/goblin%20fic%20(Kiestan)n The extra character on URLs where the app pseud has a parenthesis always matches the character just before the ). I don’t see what is causing it in the formula. Help!

Hi, I’ve got the same issue as this https://community.airtable.com/t/calculating-monthly-revenue-from-start-and-end-dates. But I can’t locate the answer. So I have a calculated column which shows what’s left to pay on a contract and I have a calculated column showing number of months left. I’d like to build this out to show the monthly revenue based on a equal monthly amounts. How do I then get it to populate months from there? Hope that makes sense. Shilpa

Hi there, Is it possible to activate/disactivate buttons linked to script depend on status in another column? The case is: I have buttons: run, pause. Each button has own script to do. If status is “to do”, only 'run" button is active. If else ‘in progress’ show ‘pause’. I tried to use additional column for that with IF(),but when i’m put the column with the button to TRUE/FALSE condition formula field convert it to link that is not working. Could you help me to find a way to solve it?

Hi, I want to create a url that contain the contents of a field. Ex: NAME - Google Search NAME is the field, so I can to search on google the content of the field How can I do? I try this (“https…q=NAME”) but it doesn’t work Thanks

Hi! I’m trying to create a prefilled form URL, but I believe the prefill isn’t working because I’m trying to prefill information with hyphens. Any ideas on how I can make this work? Example: Field Name: Type Name Example data: Apples - Michigan Form URL: “https://airtable.com/shrxxxxxxxx?"&“prefill_”&ENCODE_URL_COMPONENT(“Type Name”)&”="&ENCODE_URL_COMPONENT({Type Name}) How do I make sure “Apples - Michigan” doesn’t break the URL (and in turn, not prefill the form)?

Hello! I am organizing and archiving all the films shown from our local cinema. We started in Google Sheets andhave now imported into Airtable. Now we have seperate fields for the Year the film was shown, the month and the day. Is there a formula that combines the numbered fields to a date field? I can’t seem to find it anywhere. Can anyone help? I asume this isn’t the hardest formula, but I don’t know how to do it. Thanks!

Hi guys, I need help, I have a “statement” column and every month I need to add an x ​​value to that statement, can you guys help me with that?

I have a Google Sheet currently that uses an Array formula and I want to move to Airtable for a more stable and long term build. Let’s say I have a type of product (user chooses crayons for example) and how they use it (they choose stick figures) the combination of those two has a certain % rate. How do I get Airtable to lookup and return that rate to my client table based on those two inputs? Thanks in advance!!

So when I first built this IF statement, it brought back “Drabble” correctly when the Word Count was 100 words: IF({Word Count}=100,“Drabble”,IF({Word Count}=200,“Double Drabble”,IF({Word Count}>39999,“Novel”,IF(AND({Word Count}>17499,{Word Count}<40000),“Novella”,IF(AND({Word Count}>7499,{Word Count}<17500),“Novelette”,IF(AND({Word Count}>1000,{Word Count}<7500),“Short Story”,“Ficlet”)))))) Word Count was using the formula provided by airtable help (which is bunk), that is IF({Text}=BLANK(), 0, LEN({Text}) - LEN(SUBSTITUTE({Text}, ’ ', ‘’))+1) When I pasted in a long text that I already knew was 200 words and it brought back 185, I knew I was in trouble. Long story short, it turns out that word count formula in the official help counts the number of spaces and only works if you limit yourself to a single paragraph. So… Now I have the Space Within Paragraph Count field: IF({Text}=BLANK(), 0, LEN({Text}) - LEN(SUBSTITUTE({Text}, ’ ', ‘’))+1) And the Paragraph Count

Hey there, I’ve just added two new fields to my table which will be used to track modification time using Last Modified date. It load dates for all modified records as expected, however, when auditing the values I noticed that some differ from what’s on Activity tab. For example, one of the monitored record’s field was edited 7mo ago (2019-09-25) but the value captured by Last Modified Date is 2020-03-11. Any ideas why this is so different? Thanks

I have an Airtable with 3 tables: a list of readers, a list of books, and a list of genres. The books table has a lookup relationship with both genre and readers. Each book can have multiple genres. I’m trying to display the reader’s favorite genre based on the books they have read. In other words, of the books the user has read, which genre (or genres in the case of a tie) occur(s) the most number of times? Here’s how I tried approaching the problem: Make a hidden lookup or rollup field that aggregates all of the genres from the linked books then find the genre that occurs the most number of times. However, I’m not sure how to do that second step. Is this possible in Airtable? I would appreciate any suggestions! :slightly_smiling_face:

I think this may not be possible based on Access fields from linked record in formula without creating Lookup fields - #2 by Kamille_Parks but I’m going to try anyway! I have a pretty complex base based on some longterm needs and incoming records and trying to squeeze things into Airtable that aren’t quite possible. Right now, I’m dealing with two tables that are linked together and linking existing records together. Table 1: Main table. Table 2: Form submission. Table 1: Over a years’ worth of information about people, including linked records of people who are in relationship with one another. Table 2: People who exist in Table 1 one are submitting updated information about themselves AND other people that exist in Table 1. When someone submits information via a form, we link their record to their record in table 1 (usually by the VLookup automation, but since this breaks a lot, often manually.) Table 2 has an important field called “Can you continue to pay for groceries?” that is fi

Hi Team, Looking for some assistance on a formula. I want to know if the created date and time is within the last 12hrs. Thanks in advance!

Hi, I’m trying to replace %20 with hyphens in my Zillow URL formula. How do I do this? Thanks for your help. Example: https://www.zillow.com/homes/123-Main-St-Macon,-GA-31217_rb

I need to calculate a number based on selected single select option in another field. For instance if the users chooses “Excellent” then the number should be 10, if the users chooses “Good”, then the number shoud be 7, if the users chooses “Fair”, then the number should 5, if the users chooses “Poor”, then the number should be 3, other wise it should be blank. It so that I can then tally the score of nine different fields for a total score. Suggestions?? Formula? I’m fairly new to Airtable and I’ve trid numerous formulas toget this to happen an so far have failed.

Hi Basically, when a row is reordered in a view, I need to update the “Last Modified” field. Since it does not update on its own, I want to use something similar to =ROW() so that the record is automatically modified with the new row number and thus the Last Modified field will also update. That’s the best way I can think to do it but I’m happy to hear other ideas!

Currently, BLANK() function returns true even for the cell where the value is 0 (zero). It will be more useful if the BLANK() function returned true strictly if the cell is empty.

Hi - I created a column with an IF statement that pulls phone numbers entered from a survey into the field IF we haven’t recorded the phone number ourselves manually (the survey column is called Phone Number and the manual column is Phone # to differentiate). The formula is IF({Phone Number}, {Phone Number}, {Phone #}) When the numbers are pulled, some of the formats are in the form that the client filled out XXX.XXX.XXXX but we want it in (XXX) XXX-XXXX. Is there a way to add formatting to the formula to force the phone number to be in the format we want?

Hi! In my base I have 3 fields where various dates are entered, based on when a client was contacted. Basically a first, second, and third touch point in communication. I have a fourth field that currently pulls the most recent date based on what’s entered in the other 3 columns. If only the first contact field has a date, then the most recent outreach is that date. If the second contact point has a date, then it assumes that date is most recent. Same with the third contact date… if anything is entered into that field, it assumes it’s the most recent. My issue is that sometimes, 6 months to a year down the road, the contact points may start over, and that first date may become the most recent date. (But because there are still dates entered into the other two fields, the 4th field assumes they are still the most recent, when they aren’t) Is there a way for it to calculate within the system which of the three dates is actually the most recent date? I hope that makes sense!

So I watched an excellent Youtube video and figured out how a user can edit table data with a form. However, I got the Record ID to prefill, but I can seem to get the others working. Here is the code. It’s a mess. Hard to write it in since a tiny box. CONCATENATE({Form URL},'?prefill_Parent%20Record%20ID=',{Record ID},'&prefill_Doll%20Complany=',{Doll Company},'&prefill_Company%20Logo=',{Company Logo},'&prefill_Country=',{Country},'&prefill_Status=',{Status},'&prefill_Doll%20Lines=',{Doll Lines},'&prefill_Sub%20Lines=',{Sub Lines},'&prefill_Resin%20Colors=',{Resin Colors},'&prefill_Will%20they%20custom%20color%20resin?=',{Will they custom color resin?},'&prefill_Official%20Webpage=',{Official Website},'&prefill_Official%20Taobao%20Store=',{Official Taobao Store},'&prefill_Official%20Measurements=',{Official Measurements},'&prefill_Skin%20Color%20Comparison=',{Skin Color Comparison},'&prefill_Notes=',{Notes}) Company Logo is an attach

After searching here and looking at the nested ifs reference, I believe I’m close, but not quite there. I want the formula to look at values in two fields (likelihood of a problem; severity of a problem) and return a third value (combined level) depending on the combination. A sample of part of it is below. Example formula so far: IF( AND({Likelihood} = “Improbable”, AND({Severity} = “Acceptable”)), “Low”, IF( AND({Likelihood} = “Improbable”, AND({Severity} = “Tolerable”)), “Medium”, IF( AND({Likelihood} = “Improbable”, AND({Severity} = “Undesirable”)), “Medium”, IF( AND({Likelihood} = “Improbable”, AND({Severity} = “Intolerable”)), “High” ) ) ) )

I’m pretty new to Airtable & formulas. Was hoping someone could help me find a solution to cleaning up data for a phone field. I’m pulling data from multiple sources and while most numbers are formatted consistently, there are a few outliers I want to be sure to account for. Most common format: Dirct: XXX-XXX-XXXX A decent number also have an extension on the end in the format of " ext. X" , which are always one character. Some other formats that throw things off: There are just a few that are entered as (XXX) XXX-XXXX A handful of others that are Cell: XXX-XXX-XXXX And a small percentage that include multiple phone numbers as Dirct: XXX-XXX-XXXX, Dirct: XXX-XXX-XXXX My goal: If there are two phone numbers listed, I’d like to move the second one into a new column for an additional phone field. My guess is to parse this by comma somehow, but again – new to Airtable and having trouble figuring this out! I’d also like to remove the cell: or dirct: in front of all numbers. They can rem