Formulas

Goal: Calculate Character count limits for ppc ad sheet. Character Count field is the number of words in the copy for that ad Character Limit is the number of words allowed in the ad IF({Character Count} < {Character Limit}, “ :white_check_mark: PASS”) IF({Character Count} = {Character Limit}, “ :white_check_mark: PASS”) IF({Character Count} > {Character Limit}, “ :x: FAIL”) Please help! Thanks in advance

Hi everyone! I am having a hard time with some formulas due type of cells and blank cells. Hope you can help me. This is the Nested IF I wrote: IF( Cuota=PagoEnero, “Pagado”,IF(PagoEnero=BLANK(),“No” , IF(PagoEnero=0,Cuota-PagoEnero ,IF(Cuota-PagoEnero>0, Cuota-PagoEnero)))) The formula returns the same result if the cell is blank or is =0. I think this is because the statement will always be true in the second one but i dont know how to fix it My second issue is that, I do the same formula multiple times (Enero, Febrero…) and i need to summarize only the results that returns a number (not text) and make a sum For example: Enero=0, Febrero=No, Marzo= , … Returns: Enero CONCATENATE(IF(Enero=BLANK(), “”, IF(Enero=0, “Enero”, IF(Enero>0, “Pagado”))),IF(Febrero>0,"",“Febrero”)…and so on) I think due the column “Enero” is a formula does not recognize the result as a number, this is not working. I have tried to change text to number and it fixed but brings other problems. Could anyo

I have two fields (both are rollups - sum’s) that results in numbers: Projection Views Actual Views I have set up another field with this formula: SUM({Projected Views}-{Actual Channel}) But the formula returns a NaN. Can someone help me understand what the problem is here and how to resolve. The rollup fields draw on two separate tables, if that makes any difference.

Hi I am trying to group empty linked field but not sure which formula to use. Any help much appreciated. Thanks, Maria

I have a field that contains two strings that will always be constant: " - Device S/N: " and " - Dongle key S/N: ". I want to extract the serial number from in between these two constants. My biggest issue is that the character positions will always vary. Any ideas on how I can do this? MID, LEFT, MATCH, don’t seem to work at all. Here is a sample of the cell contents that I am working with: Device name: NewTom GO 3D - Device S/N: 708K0078 - Dongle key S/N: 1-1555622170

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 get the different statuses with no luck yet. Any help on this is greatly apprec

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 Airtable: Organize anything you can imagine Airtable works like a spreadsheet but gives you the power of a database to organize anything. Sign up for free. BR :m:

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 is more than 1 of the sam

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!