Formulas

Hi, I’m using Airtable to manage a conference. I have a table which has conference proposals with the authors’ contact info. Then I have another table with people that have subscribed to the conference. I’m trying to send emails to those that sent proposals in but haven’t subscribed. So I’m trying to accomplish that by comparing the emails from the Subscribed table with the ones in the Proposals table. These are the steps I followed so far: I linked the 2 tables and added a lookup field to the Proposals table to look for the emails of the Registered participants. In the Proposals table, the contact info is spreaded out in different columns, so I used CONCATENATE() to join all the emails with a separator. I realize that the lookup field returns an array while the CONCATENATE() function returns a string. Here is the tricky part. I’ve been trying to compare both groups of emails and it hasn’t worked. I need something that loops over the whole array/string and checks for matches and retur

I want to use a rollup to get an average of the existing numbers, but not all the fields in the column I want an average from have an existing number and return NaN. I want the rollup to ignore NaN.

Hi all, We have a table with records per customer, with multiple variables and their scores coming from typeform (auto-sync). The goal is to get out the 3 highest scoring variables and send the label of those variables to the customer. I can’t seem to find a way to get inside 3 fields the 3 column-names with the highest value of that record. Another solution I was thinking of, was when the auto-sync worked, to store the variables in different rows in another table but also with this I see no reason than to get the max 3 variables out of this table linked to the original one. Help would be much appreciated, thanks!

Here is what my formula looks like. I can not get Airtable to accept it. Can you say why? Current formula IF( ({Sales Rep}!="" ), AND( NOW()>={Followup Date}), “Delay Notification”) Proposed formula IF( OR( AND( {Sales Rep}!=BLANK(), {Followup Date}, NOW()>={Followup Date} ), AND( {Sales Rep}!=BLANK(), {Followup Date}=BLANK() ) ), “Delay Notification”, “Send Notification” )

Hey fellow AT users, I’m trying to set up a formula that calculates the renewal date of subscriptions based on if they are Monthly or Annual. I currently have 3 columns that I’m working with: Frequency, Billing Start Date, and Billing Renewal Date (which is where my formula is). I was testing the following formula: IF( {Billing Start Date}, IF( IS_BEFORE({Billing Start Date}, TODAY()), DATEADD({Billing Start Date}, 1, SWITCH({Frequency}, "/mo.", 'month', "/yr.", 'year')) ) ) The issue is that while it DOES show the next date based on the Frequency (i.e /mo. or /yr.), it’s not current. For example, a Billing Start Date of August 29, 2020, that’s set to renew monthly, is showing as September 28, 2020 instead of October 28, 2021 (since it’s currently 2021 and September is past). Does anyone know how I can get this to work? Do I need more columns for date comparisons or can this be done with just the 3 existing columns?

The formula is simple in that I want field A multiplied by field B to = field C What happens is because field B is rounded due to the limit I put in the field because I just want two places after the decimal, it is calculating based upon the original math and not what the cell shows. For example. 51 x 68.33 = 3,484.83 but the cell shows 3,848.70 bc of the original amount being 68.3275 How do I get the field B to use the actual data in the cell instead of the original so that if someone not in the know can see that 51 * 68.33 = 3,484.83 Thank you in advance!

Hi Airtable Community! I’m developing a base to manage a bike polo championship. So far so good, I could implement all the function that are required, but I’m stumbling on a formula that I can’t get to work. On this table I want to get the final score for both Teams. As for shown with the example on the last column which it was achieved with a Count field and a filter with the name of the Team ‘Test1’. That is not a solution for me, because what I want is to filter the count based on the name of the column ‘Team 1’ and not on a particular team name. I’ve tried to combine IF () with FIND(), IF(FIND({Team 1},{Which team scored?}),1,0), but then I get only a 1 or a 0. Does anyone have any idea on how I can then count the frequency of the string in the column ‘Team 1’ and than count it or Sum it? I’ve tried to add SUM() or COUNTA() in the formula, but unsuccessfully. I would very grateful if anyone can help me :winking_face: many thanks, Claudio

Hello! My use-case may be unique, as I am project managing a movie theater. I have a Calendar view of all confirmed films by Date Range defined by a “Start Date” and “End Date.” Some films are on runs (typically Friday-Thursday ie Start Date 10/29/21, End Date 11/4/21), and others are single events (ie Start Date 10/29/21, End Date 10/29/21). I have written a formula to categorize films by status (“Coming Soon”, “On Screen”, “Closed”) as follows: IF( IS_AFTER( {Start Date}, TODAY() ), "Coming Soon", IF( IS_AFTER( {End Date}, TODAY() ), "On Screen", "Closed" ) ) My issue is that single events will appear as “Closed” on the day they open (duh). This status is easily fixed by setting the End Date to the day after it opens, however this yields two-day-long entries for single events on the graphical calendar, which is obviously misleading for our staff. Any recommendations for rethinking my formula (an IS_SAME function?), date-range definitions for the calendar, or an entirely different ap

Hello team, am facing an issue in getting multiple columns values from a table and store in different table with multiple columns For eg: In a legal system , we have an one case number and case was failed by the party. so again the party appeal in high court for the same issue. so different case number issued at high-count so if new appeal is happening on this case, then if user select the old case number then it will auto fill to the columns fields with the previous values, means if old case number is 200/2020 and other field like case subject, claim amount, etc once new case comes with new number (201/20221) when goes to appeal, if user select the old case number as 200/2020 it will select or pull all the fields such as case subject, claim amount etc

I am wondering if anybody knows a way to change the way the field for date created is displayed in concatenate? This formula reveals the date as follows: 2021-10-29T20:28:27.000Z - Task Detail - Task Assigned to - Projects I am looking for something a little cleaner like: 2021-10-29 - Task Detail - Task Assigned to - Projects IF(Projects = BLANK(),“No Project”,CONCATENATE("",{Date created}," - “,{Task Detail},” - “,{Task Assigned to},” - ", Projects)) cheers!

So I have a base that has a lot of images as a part of it. I’m trying to have it so that in Table-3 if Option-A is selected from Table-2 then attachments from Column-A on Table-1 are displayed but if Option-B is selected then attachments from Column-B on Table-1 are displayed instead. However, I have tried everything I can think of to make this work and I can’t. I’ve tried multiple look-ups, formulas, everything I know how to do. I can not get a single column that displays the attachments and switches according to the selected option. Help, please.

Hello, What would be the easiest way to configure a formula that combines all of these? IF({Request Type}=‘New’, DATETIME_DIFF({DCML Issue Date Lookup},RequestDate,‘days’) IF({Request Type}=‘Major Update (Procedure Change)’, DATETIME_DIFF({DCML Rev Date Lookup},RequestDate,‘days’) IF({Request Type}=‘Minor Update (Formatting, Spelling, Grammar, etc.)’,DATETIME_DIFF({DCML NCC Date},RequestDate,‘days’) IF({Status}=‘Incomplete / Closed’, DATETIME_DIFF({Status Last Modified Date},RequestDate,‘days’) I tried a formula that combined the first two, and the system accepts it, but it doesn’t return anything. Thanks :slightly_smiling_face:

I have a field that includes a number range for each record: “5-10”, “15-20”, etc. On another table, I would like to do a rollup that adds together all the first and last numbers in those ranges, displaying “20-30” essentially. Is there a rollup formula that can find the first and last numbers in a range? The only thing I’ve thought of so far is separating that range into 2 fields and then combining them into the other table with a concatenate, but I’m hoping there’s a solution that keeps the range in a single field

I am managing ad contracts and needing to include the year of their creation date plus the contract number in the first column. I use a formula that pulls data allowing me to have at-a-glance info for tracking our ads. Example: the column is called CAMPAIGNS with the following formula: CONCATENATE({Contract #}, ": “,Account,” - ",{Ad Size}) This results in a field that might read: “21 004-BQEA: Bruce’s Quick Eats - 1/8 page” When blazing through production, I can see the year the contract started, that it was the fourth contract of the year, the account code and full name of the client, and finally the ad size they ordered. We also use the 21 004 to populate part the invoice numbers for billing in each ad cycle. (Contracts generally cover multiple ad cycles). This also informs our file-naming for ad image files, so we can find accompanying assets quickly and easily. I intially built contracts for each year in their own tables, because I needed to reset the contract number to zero every

Hello folks, I am trying to extract two numeric values from text. One of the values is directly after “$” and varies in length. The next is directly before a word “endpoint” and also varies in length. Here is an example of text: • $24,200 + Professional Services ARR • 650 endpoints • Blah came to Blah wanting to move away from Blah • Huge shoutout to Blah for helping with Blah and answering all their technical follow up questions. In this instance, I need 24,200 and 650 each in their own columns. I am totally lost on how to do this and have been trying for hours. Any help is seriously appreciated.

Hi, I am new to AirTable, there are still some points I can’t figure out. Now I will explain my question in detail. I’m sure it’s very easy for you guys, but I certainly didn’t succeed. I can never find the solution. I have two questions. The first is this: The table you see in this picture is the class list. Write the names of the students. I linked this table with the “Dersler” table. And this is the “Dersler” table. Classes and dates. I add the students who entered the course next to the course. And I created a formula row in the “Class List” table. Here I wrote this formula. The form is working. But I want the “/2” part of this formula to change after the third lesson is made in the “Dersler” table and after I start to enter students there. Since the number of lessons changes after each lesson, the success score needs to change. For the success score, I need to calculate from “the number of students attended the course/all courses”. Can I have the formula change as I complete th

I need help with a conditional statement working with dates. So in the table there is a time slot however the conditions lie on the right where the start and end times actually are. So if a slot is taken at 10:30 for instance how would I formulate a statement saying if appointment time is between then “booked”

Hi all! I think this would be super helpful to have programed as a default. "within (number of days) of date chosen, date will highlight as (choose color) Short of that, is there a way to script this? Thank you, B

Hello all I have these two fields the is the “amount used” and “Status” what I want to do is when the “amount used” is empty I want “pending” when the “amount used” is “0” I want “Void” and when the “amount used” is greater than “0” I want “Used” On status is a single select! Screen Shot 2021-10-22 at 9.13.54 PM|173x500

Hello. I have a lookup field which lists the age groups that are related to all the linked records. I have a formula field which I’d like to show ‘Senior’ if the lookup field contains Senior, ‘Intermediate’ if it contains Intermediate (but not Senior) and ‘Junior’ if it only contains Junior. The formula I’m using is this: IF( FIND( ‘Senior’, {Lookup Correct Section} ), ‘Senior’, IF( FIND( ‘Intermediate’, {Lookup Correct Section} ), ‘Intermediate’, IF( FIND( ‘Junior’, {Lookup Correct Section} ), ‘Junior’ ) ) ) If it is listed “Intermediate, Senior” then the formula works perfectly however if it is “Senior, Intermediate” then it returns Intermediate (even though Senior is in there). I’m sure it’s something really simple that

Hi, I’m trying to build an IF statement with an OR in it but I’m not getting the expected result. I have created a simple table for tracking hours worked and calculates overtime. Columns are as follows… Date - is just the date in question. Calculate Weekday - WEEKDAY(Date) So far so good. Because the standard work hours vary from day to day (7.5 for Mon, Tue, Wed, Thu / 7.25 for Fri / 0 for Sat, Sun), I wanted to create an IF query to return the correct hours for that day number. Rather than going overboard with nested IF, I tried to simplify to the following, but the OR in this doesn’t seem to be doing what I expected… IF(WEEKDAY(Date)=5,7.25,IF(WEEKDAY(Date)=OR(1,2,3,4),7.50,0)) From what I can see, the day 5, 0 & 6 work okay. But my problem comes with the OR as only day 1 works; the rest come back as 0 hours. Am I misusing this function? I was expecting the OR to then return 7.50 fo all of 1,2,3, and 4.

Hi all, Please see the screenshot below - I’d like to display a message in the Is Absent Today column if today’s date is among the dates in Absences. Currently I’m using the following formula but it only works if there’s a single date: IF(DATETIME_DIFF({Absences}, TODAY(), 'days') < 1, "Yes", BLANK()) Any help is much appreciated!

Hi, I’m trying to convert a number to a string. I have a field called ‘Version number’, which is formatted to take two decimals. I want to use this version number in another field as a string because I’m concatenating to generate a document filename. So for e.g. if the version number field is 1.00, I’m trying to write a formula that generates a string “myfile_v1.00”. The problem I’m having is that when I use the approach suggested in the documentation, 1.00 is being converted to “1” and so I’m getting “myfile_v1”. I want to retain the decimal value in the type conversion. I suspect had the decimal part been something other than .00 it would have also been converted to string, but I want this to be the case even if the decimal part is .00 I could use some help with this. The formula I’m using is: {Document name}&" v"&{Version number}&"" Document name = mydocument Version number = 1.00 Formula result = “mydocument v1” Desired formula result = “mydocument v1.00” Thanks.

I’m working on creating a Table that lists Expenses. I’m currently using the following formula to populate a Title for the first column in the table based on columns: Vendor, Frequency, Price, Per Member Price?, Users and Total Due. The “Per Member Price?” is a checkbox field and the Total Due column is calculated by multiplying Price x Users. CONCATENATE({Vendor}, " ($", {Total Due}, Frequency, IF({Per Member Price?}, " per member", ""), ")") This results in data displaying like this: App 1 ($52/mo.) if the “Per Member Price?” is not checked OR App 1 ($52/mo. per member) if the “Per Member Price?” is checked. The problem is, is that regardless of if it’s supposed to be per user or not, the Total Due is always present vs conditionally showing the per member price as defined in the Price column. I THINK I might need a nested condition here within the CONCATENATE… something like: CONCATENATE({Vendor}, IF( {Per Member Price?} = 0, "$", {Total Due}, Frequency, IF({Per Member Price?}, "$",

Hello - I’m quite new to Airtable and trying to prototype out an invoicing application, but I’m running into an snag and could use some guidance. My base: Clients Table (name, id, project lead, etc) Items table - this is a granular list of the items for an entire project - name, descirption, costs, % complete, status, last updated datetime, and a link to the clients table Invoices - In this table my goal is to be able to create a monthly invoice for a client based on the items table with the following conditions. Where the CLIENTassociated with the ITEM = CLIENT associated with the ITEM AND ITEM’S Last Updated date is BETWEEN {invoice start date} & {invoice end date} In SQL I would join on the client ID for both tables, but it’s not yet clear to me whether this kind of join can be done in Airtable. I’ve looked at other invoicing examples and it looks like many have the ITEMS associated at the invoice, but in my use case that may be too laborious with the number of line items a