Formulas

How can I translate the excel formula listed below into Airtable? I don’t see a COUNTIF or SUMPRODUCT fx option. I am trying to calculate the % a project is completed based on dates in multiple columns. Below is how I did it in excel: =IF(AY2="",COUNTIF(AS2:AX2,"<="&TODAY())*0.1667,COUNTIF(AS2:AY2,"<="&TODAY())*0.1429) Any help would be greatly appreciated!

Hi Im making quote with block When i add lot of text i need more than 1 page but i cant find a way to solve and make a quote with more than 1 page Can you find me a solution ? Thank you

Hello, AirTable community. I have an issue that I can’t seem to solve. One of my use cases for AirTable is both Work/ Cost Management. I create a new base for each new initiative. Within a base I have multiple tables; one which represents the master backlog where all Tasks are stored, and then individual tables for each person represented in the Master Backlog. Ideally, I would like to link the records to auto-update when edited from the tables that represent individual members of my team in the Master Backlog. I spend entirely too much time manually updating records and really need to find a solution to this. Any ideas? - Cheers and thank you ahead of time.

Hello! I’m trying to create a nested IF statement that uses a date field about an event {End Date}, to determine which quarter the event was in but I keep getting invalid formula errors. I am pretty well versed in excel formulas, but my knowledge does not seem to transfer to Airtable. Unfortunately, the quarters are weird and run Q1: 8/15-11/14, Q2: 11/15-2/14, Q3: 2/15-5/14, and Q4: 5/15-8/14. Ideally I would like this formula to continue to work for upcoming years without revision. Any ideas are much appreciated!

Hi, I’m doing some work for a children’s nursery have a field called “RATE PER HR” in the table STUDENT it is a currency and linked to a table called RATE. I need to do a simple calculation RATE PER HR * NUMBER OF HOURS I’m unable to do this without getting an error in the field. What am I doing wrong, how do I get around this ? Thanks Jason

Hello! Is it possible to filter items by adding the filtering formula inside a Vue.js attribute instead of a URL parameter? Like using v-if or similar… Thanks!

Hi there, I am working on a membership base and trying to create a formula field that will generate one of the following statuses depending on the membership expiration date {Expires}: Active (Any time 31-365 days before Expires) Time to Renew (30 days before Expires) About to Expire (15 days before Expires) Expired (up to 90 days after Expires) Lapsed (anything after 90 days after Expires) A couple of things to know: I have two tables in my base, a Transactions table and a Members & Donors table. The ‘Status’ field will live in the Members & Donors table and uses the ‘Expires’ field, which is a formula field that adds one year to the ‘Last Donation Date’ field, which in turn is a MAX(values) Rollup field to get the most recent transaction for each donor from the Transactions table. I have tried my hand at a formula following the most similar circumstances I could find in the Airtable community to g

Hey Guys I’m try to do the math on this. if shoot days is greater than 4 and less than 8 return 4 but if its greater than 8 and less than 15 return 8. IF({Shoot days} > 4,IF({Shoot days}<8,4),IF({Shoot days} > 8,IF({Shoot days}<15,8),{Shoot days})) thanks

I am trying to create a formula that will result in an updated balance. I have a balance amount from the previous record. I either add an amount in or take an amount out in the following record. I need to have it calculate the new balance. So basically I am using an amount in a cell above the current record to help calculate a result in the current record. Does anyone know how this can be done. It is simple in Excel, but I don’t see how in Airtable. Thanks!

Hi there, I’m new to Airtable formulas, and I’m having a difficult time constructing a nested if formula that automates labeling events as open, upcoming, and ended. All items have an Opening and Closing date entered in their respective fields, and I’m hoping to have them but I can’t seem to compare them to today’s date accurately - does anyone have any suggestions?

I have two tables. One is Equipment. There are fields: Barcode, Name of Equipment, Where borrowed (Linked). Other is Lending Equipment. There are fields: Lender, Barcode, Created Date, Where borrowed. How do I use a barcode to identify a device? How do I use formulas in clever way?

dear all this is (should be) so simple, but yet cant get it to work New Law regulations in Austria (01.01.2019) :confused: Gig worker MUST be calculated per day If someone workes the nightshift this woud have to be divided onto the 2 days, according to the worked hours, this all per month, of course If “Max” workes on 12.02 18:00 - 13.02 04:00 what would be the solution to this calculation? how many hours “Max” worked on 12.02 and how many on 13.02.? hier is a link to a test base, if any one wants to play with calc und formulas, i have found no solution. yet. Airtable

Hi there I have one field with value subdomain(.)domain(.)com, domain(.)com and etc. I need to get N fields separate source value by (.) The result will be for subdomain(.)domain(.)com field 1 subdomain field 2 domain field 3 com for domain(.)com field 1 (empty) field 2 domain field 3 com Thank you

Hello! Trying to create a formula to notate when 1 of 4 columns has text in it. For a project to be “complete” there needs to be date in either column A, B, C or D. I would love a column that would give me a check mark for it. Thanks, Eileen

Perhaps I’m not searching correctly, but I’m hoping the community can help. I’m looking to have a formula that concatenates multiple columns based on whether or not one cell contains a specific character, such as a question mark ?, but also contains other text. My formula already has a nested IF that is based on whether or not another cell is blank; see below: IF({UTM Content}="","",CONCATENATE({Link To},“string”,{UTM Content})) So, I want 2 results: If cell {Link To} contains a ? somewhere in it, then I want “string” If cell {Link To} does not contain a ? somewhere in it, then I want “string2” Appreciate any help!

Hi Airtable guru’s! Here’s what I’d like to do that I haven’t figured out. I’m teaching a series of classes that will have tests that are being held in Google Forms/Quizzes. I’d like to have these scores come into AirTable in one sheet to summarize all of these scores. Like a grade book. Email address | Test 1 | Test 2 | Test 3 I’ve stumped my self. HELP!

Hi - super basic formula question. Much forgiveness please :pray: So I have a bunch of tasks listed in a base as rows, with one column being “status” that has various dropdown options, including one called “Done”. I’d like the formula to return a 1 when the status “Done” is true and a 0 when it’s false. I have this: IF(Status=Done,1,0) But I get angry red text telling me to try again. I’ve tried with spacing, quotation marks, tried to look up more on IF statements, but can’t see what is probably a very simple mistake. Might be the late hour. Lil’ help? Thank you! C

This formula from the blog produces the result with at time: 9/30/19 12;00am What part of the formula needs to change to eliminate the time? DATETIME_PARSE(DATETIME_FORMAT({Period},‘MM/DD’)&"/"&YEAR(NOW())) Thank You

Hi – I’m building a Street Team in one table in Airtable. One view shows “assignments” which plots out what concert each street teamer is working on. Once they are done, they submit an Airtable form with their name, photo proof & the concert they worked on. This goes into a “review” view, which allows us to review to make sure they did the work. Since “Assignments” (generated by us) and “Review” (generated by the worker) need to match, I’m trying to figure out a way to match them. So if we assign Street Teamer A to work Concert X, then we need to know when Street Teamer A submits their proof for Concert X. So I created a match code field with the formula {name}&" - "&{concert}. In the example above, we’d know it’s a match because both OUR assignment AND their submission have the same match code (StreetTeamerA - ConcertX, in this example). But how do I write a formula that then can determine whether that Match Code is unique? Meaning, if there i

Hi! I’m hoping to create a ranking relative to the category an item is in and can’t figure out the best way. My dataset includes items in multiple categories, ranked 1-1000 overall based on sales. I also have a “Rank in Category” field that ranks the items in each category, and category totals are anywhere from 37 to 242. Is there an easy way to make a fractional calculation relative to each category? For example, item 4 overall is actually 1 for its category of 92 items. The output can be 1/92 as a decimal, I’m creating a scoring system from outputs and that will help normalize it. Thanks!

Hi, I have used ARRAYJOIN in rollups many times, but never tried it in a regular formula field, and I’m having some trouble. I was trying to use ARRAYJOIN to create a string I can parse with DATETIME_PARSE. I’ll simplify the formula I wrote here to get rid of some complicating factors. The formula I tried was: ARRAYJOIN([“2019”,“01”,“01”],"-") Airtable didn’t seem to understand square braces, curly braces or brackets, but I also couldn’t figure out any other wrapper for the array that would get it to work. All of the help articles I found are intended for rollups, where Airtable generates the array automatically. Anyone know how to do this? Thanks!

Question: I have a collaborator field with numerous collaborators per record, and I was hoping to add a field that would add up how many collaborators there are listed in that record. How would I do this? Thank you!

This formula is not working to pull data from the two formula columns (Days Actually Out and Backdata days out) I want. It seems so simple. If the DATE OUT is before or is today, pull this number. If the date is AFTER today, pull another. IF({Date Out} <= TODAY(), {Days Actually Out}, IF({Date Out} > TODAY(), {Backdata days out})) It currently only pulls the ‘Backdata days out’ data for this column. . . Can anyone tell me if this is a no-no in AirTable or if I’m doing something wrong?

Hello, I would like to send daily recurring reminder emails to my team members. I would like the email to be formatted to include the task due and also include specific fields that I have created including multiple select fields and due date. For example, within a table, “tasks”, I have a view which is filtered to include a single team members tasks grouped by due date and a multiple select field identifying the “importance” of a task, either “low, moderate, and high”. I have a formula column which prints the text “send reminder” if the due date field is today’s date: “IF({Due Date}=TODAY(), “send reminder”, “”)” I have set up a Zapier trigger using the “scheduler” zap, which is scheduled to trigger every morning at 5am. Next, I set up a Airtable action, “find record”, to search for the value of “send reminder” in the table. I then set up an action for gmail to send an email to the team member. I want to format the contents of

Hello, my first post to this community. This formula was entered and accepted with no errors. The issue is the second IF (in this case {Tax Type}='Sales Tax) does not produce a result. Here is the formula: IF({Tax Type}=‘Bank Rec’,IF({Qtr}=1,‘1/31’,IF({Tax Type}=‘Sales Tax’,IF({Qtr}=2,‘3/20’)))) My statement is: Statement 1: if the tax type is ‘Bank Rec’ and the Qtr=1 then the date 1/31 should display. Statement 2: if the tax type is ‘Sales Tax’ and the Qtr=2 then the date 3/20 should display. Here is what I have tried: If I change the tax types for e.g. put sales tax first and bank rec second then bank rec will be blank I have hand typed the formula in the field, I did not copy and paste I have tried changing the single apostrophe’s to double For some reason the second IF does not produce a result even though the formula does NOT error out.I have uploaded the image of the result. Thank you in advance. I ho