Formulas

I’m very new to using Airtable. I’ve got a table that includes survey respondents each listed as primary records and then a multi-select field that has various themes they each indicated in their responses. After I analyzed and put everything in the multi-select, I realized I should have had these themes as linked fields on a separate table, so now I’m trying to import the respondents on Table 1 by adding the themes as primary records on table 2 and I can tally the respondents who selected each by using the Count field. I’m hoping I don’t have to add by hand the linked record themes from Table 2 for all 200+ records. Is there a way to create a formula that pulls all respondents on Table 1 who indicated each of the themes in the multi-select to auto populate the records in the matching theme record on Table 2? (I did find that the Pivot Table block tallied each of the themes when I used that, but I’m having trouble customizing the view on the order the themes appear and

I want to allocate a list of numbers (product limited editions) to users. They will pick one by one their selected number and I want this number not to be avaliable anymore after the selection. Do you know how to do that ? Thanks a lot

Hi Everyone, I am new to Airtable and enjoy it (for the most part:). I am trying to concatenate two fields (done) and then replace or substitute the spaces " " with dashes “-”. I having trouble placing the substitute syntax and getting a circular reference error. Thanks in advance for your help!

Hello, I’m dealing with DATETIME_DIFF. It worked until today, but now it return “NaN”, what’s happen?

Hello, I have 2 fields, Collaborator and Formula. When I select a collaborator(any) I want the formula to show last modified time. Similar to this(this is for a checkbox, so it doesn’t work): IF({PAGO} = 1, LAST_MODIFIED_TIME({PAGO})) I have been messing arround with the formula but to no sucess. Thank you in advance.

Hi everyone! New to Airtable but already love the product! I’m hoping you can help. To determine which fish can go with another fish in a fish tank I created a table with two columns. Column A is the species of fish and Column B is a self-linked field of the fish that can go with it (simpatry fish). I want to make sure I don’t overuse any particular simpatry fish. I want to create Column C to count how many times my column A fish is used in any of the values in all of Column B. So row 5 (Heros severus) in my screenshot would have a value of 2 in Column C since it has simpatry with the fish in row 3 and row 4. That way I can find out how many times any fish in column A has been used as a simpatry fish. I hope this makes sense. Thanks in advance!

Hi, im trying to insert a space into a concatenate formula in the primary field of my table. ive seen some similar posts, but the answers dont seem to be working for me. my formula is: CONCATENATE({First Name}, {" "}, {Middle Name}, {Last Name}) {" "} is where i want the space. Any help is appreciated!

Hello all, I have imported data from gmail and it came under this date format in text. "Mon, 11 May 2020 14:31:44 -0500" and I need it in date format like this “5/11/2020”.. Can you suggest a solution for me ? Thanks

Hello - I am very new to Airtable (meaning…this morning :)) and I’m very much a newbie to any kind of spreadsheet. I’m trying to create a formula and am hoping I can get some guidance here . I’ve attached a screenshot to help illustrate what I’m trying to achieve - if the column Pay(X) is checked, then the amount in column Balance Due will be put into column Amount to Pay. Finally, the Amount to Pay should be totaled and put into column Bills to Pay Total. Thanks in advance for any assistance that can be provided! Sarah

Hi, Here is my problem: I have a table of SONGS. In addition to the song title, there are the Composers, Lyricists and Publishers fields. Each of these three fields are populated through a link to my Contacts Table. Of course, there can have more than one composers, lyricists and publishers for any given song; sometimes only one person taking all these roles. Now, the idea is to assign to each of these shareholders (this is how to call them) their specific percentage (the total, of course, amounting to 100%). These numbers may vary from one record to the other, even when we have the exact same shareholders. So, is there a way to assign a specific percentage to each of the multiple shareholders for each record? In other words, I’d like to get something like: Composer 1: 12,5% Composer 2: 12,5% Lyricist: 25% Publisher 1: 25% Publisher 2: 25% For example… :slightly_smiling_face: Thanks!

Hi All, I am hoping to find a way to select an option from a dropdown box in TABLE A which triggers the creation of a new record in TABLE B. It doesn’t have to be a dropdown, could be a checkbox. The ultimate goal is to get a new record to show up in TABLE B to trigger Zapier to send an email with the relevant data to my customer from TABLE B. I.e. IF ‘send email’ is selected in TABLE A, THEN create new record in TABLE B. > send email when new record created. I guess TABLE B would end up being like a sent items folder, but I don’t care if I have to clean it out every now and then. Any help is appreciated!

Hi all just trying to decide if AirTable will work for our businesses. So far so good except for this snag. We are in childcare and NEED to calculate our babies ages in Months not years based on TODAY(). This is the section of the child table so far, I want the Age field to return ages under 3yrs (<=35months) in months and the rest in years… Formulas so far M_age DATETIME_DIFF(TODAY(),{C1_DOB},‘M’) &“M” (works fine) Y_age DATETIME_DIFF(TODAY(),{C1_DOB},‘Y’) &“Y” (works fine) Age I’ve tried… to get under 3 years in Months and over in years: IF(M_age <= 35,(DATETIME_DIFF(TODAY(),{C1_DOB},‘M’)&“M”),Y_age) IF(M_age <= 35,M_age,Y_age) No matter what I do the age field returns age in years.

Hi everyone, I’ve got a table that I’m having trouble with. In the “Act” column, I have a single-select with Act 1, Act 2, Act 3, and Act 4 as options. When one of those Acts is selected, I want the next column (“Act Dynamics”) to return a string of text. I’ve gone through similar questions from other users and have tried a few different possibilities, but each time, Airtable says my formula is invalid. Can anyone see what the issue is? Here are the 3 best guesses I have: Possibility 1: IF( FIND( 'Act 1', {Act} ), IF( FIND( 'Act 2', {Act}, ), IF( FIND( 'Act 3', {Act}, ), IF( FIND( 'Act 4', {Act}, ), 'Denial to anger;Rejecting opportunity;Ordinary world', ) 'Bargaining and losing;Wanderer' ), 'Bargaining and winning;Warrior' ) 'Martyr;' ) Possibility 2: IF(MID({Act},12,3)=“Act 1”,“Denial to anger;Rejecting opportunity;Ordinary world”, IF(MID({Act},12,3)=“Act 2”,“Bargaining and losing;Wanderer”, IF(MID({Act},12,3)

Hi there, I am using the following formula to calculate total donations received within a period of time: IF( AND( YEAR({Received Date}) = YEAR(TODAY()) ), {Amount} ) However, I would like to modify this, so that instead of “TODAY()” - which gives the current year (2020) , I can set a time period like March 1, 2020 to February 28, 2021 (which matches our financial year). How do I do this in existing formula?

I am concatenating multiple fields into one comma separated field, and even though the formula format is 2 decimals, it’s showing as a whole bunch of them in the concatenated field. I’ve tried rounding but I can’t seem to get it right. Included pic.

When a partial record is submitted but there are other associated records still pending I want to avoid processing the submitted until the entire group is submitted. I would love to have a 1 of 5, 2 of 3, 5 of 5, etc. I don’t think word count will work as these records are referenced with a generated number (9 digits long) and currently have 2000 records and every day more records are inputted.

I’ve been trying to make new linked records from a list of data using the method described here: By having a long list or records and copying them into a linked field. I’ve done this a few months back where it worked perfectly, however now it does not work at all. If I open the linking field and click ‘Create new record’ I can make one at a time, but not too sustainable with a-lot of records. I’ve also tried to use both desktop and browser version but still the same. Is there some setting that does not allow me to create records from a linked field?

Hello, newbie to Airtable. I am trying to build a CRM tool that also forecasts future period recurring revenues. We have projects that often span 2-3 years. I would like to allocate the future revenue (equal monthly amounts is fine) across 12/31/XX fiscal year time periods. My inputs are: Start date End date Monthly revenue I’d like my outputs to be: Prior Period Revenue (any revenue prior to this fiscal year) Current Fiscal Year (e.g. 12/31/2020) Revenue Next FY Revenue Backlog Revenue (anything beyond next fiscal year) Let’s assume a hypothetical project as follows: Start date: July 1, 2019 End Date: March 31, 2022 Monthly revenue: $10,000 I would expect the following results: Prior Period Revenue: $60,000 Current FY Revenue: $120,000 Next FY Revenue: $120,000 Backlog Revenue (anything beyond next fiscal year): $30,000 I ha

Hey Everyone, I am trying to write a formula - if a certain type of encounter is successful then a specific cell (we call the anchor date) will enter that encounter “time/date” - we cannot get this to work?

How can I achieve the FLOOR.MATH function that exists in Excel into Airtable? FLOOR and ROUNDDOWN are not taking the precision as I expect. I would like floor with a 0.25 precision: FLOOR.MATH(11.78, 0.25) is returning 11.75 whereas in Airtable: FLOOR (11.78, 0.25) is returning 11.80 ROUNDDOWN(11.78, 0.25) is returning 11.25

I’m trying to automate going through and tagging customer responses (long text) I have a list of words or phrases I want it automatically tag with another phrase eg. Find and tag the following words “Specials” or “bargains” or “price” = Price, “menu” or “search” or “find” “navigate” =Navigation Done manually it looks like this I think the code would be something like this IF it contains “X” or “Y” or “Z” =Z, IF it contains “A” or “B” or “C” =A I’m not sure if I can set this up using the IF formula because it may not contain any of the words or it may contain more than one. Any advice? Should I be using a script instead?

I’m hoping someone can assist me with creating a formula. The formula should look at the field Article Title. We have thousands of articles. If any article contains a particular phrase, the field should return a specified URL. For example, we may have 10 articles with the word Movies in the title. For those ten articles, the formula should output https://www.imdb.com. More specifically, the article with the title Top 10 Movies of All Time would result in https://www.imdb.com in the field. This is what I have thus far and it’s not working, at all. IF( FIND( “Movies”, {Article Title}, ‘https://www.imdb.com’, ) ) Thanks in advance.

Hi everyone. I have a base where there are some status to define where are the content that are being created. But, I have to know the date and time when the status “Completed” was added. Do you know how to do this?

Hello! I’m doing my nut in trying to collect info from 2 (formula) columns into one, with the right (date) formatting. Here’s the sitch: I have subscribers who can register for 1 month, or 3 month blocks. I want to show their renewal date in one column. I’ve started with creating a “Month Start” field (the date their month-long registration started) and a “3 Month Start” field (the date their 3-month registration started). Next I’ve made 2 DATEADD columns: “Month End” adds 1 month to the “Month Start” date. “3 Month End” adds 3 months to the “3 Month Start” date. So far so good. Then, I’ve created a new column called “Renew Date”. I want it to pull the dates from “Month End” and “3 Month End” (where they exist; some cells in each column are blank). This works, to an extent, using CONCATENATE… {Month End}&{3 Month End} This pulls the info in, but it’s using that bastard format “2020-07-08T00:00:00.00

I’m trying to create a formula that looks for either of 2 fields to be checked. Can someone tell me what I’m doing wrong? I can get it to work with either/or but trying to look up both, I’m stumped. Thank you! IF((Agreement =1) OR(IF{Umbrella Agreement}=1)),“ :smiling_face_with_sunglasses: ”