Formulas

The setup: I have one table which is used to schedule marketing emails (each record contains a single date). I have another table which is used to schedule multiday promotions (each record contains a start date and and end date). What I’m looking for: For each record in my email-scheduling table, I would like a field that links to the records of all the promotions from the promotion-scheduling table that are ongoing during the day the marketing email is scheduled. Any help would be appreciated. Thank you.

I’m trying to combine multiple values from a multiple fields. For example. Column A: Type A, Type B, Type C (multiple values in a single cell) Column B: Option1, Option2 (multiple values in a single cell) Result: Type A - Option1, Type A - Option 2, Type B - Option1, Type B - Option 2, Type C - Option1, Type C - Option 2 Is this possible? Please help. Thanks!

I am trying to concatenate two columns, a percentage (based on a formula) in one column and its associated product in the other column. When I concatenate, the percent is converted to a decimal. I know to multiply this result by 100 and add ‘%’ to get the result I want. And it works for most values, but some of the decimals turn out like this: 15.920000000000002%. How do I get all resulting decimals to show as XX.XX%?

I have several tasks where a date field is filled for whatever date that task is completed. I tried to build a formula where if no date field is blank, it sets the date to TODAY() so that I can know when everything is done. It seems like this formula keeps re-updating to whatever today is everyday. If all the tasks were done Tuesday, it still keeps changing to whatever today is in that IF(***, TODAY(), BLANK()) statement. Am I doing something to cause the table to think these values are changing or do I need to use an automation trigger to accomplish this?

Hello, I’m trying to create a formula to return a value of “early deadline” if a field I have named {Application Date} contains a date less than or equal to 12/15/21. I’d also like to return a value of “Regular Deadline” if the date in the Application Date field is after 12/15/21. This is a variation of the the formulas I’ve tried: IF ({Application Deadline} <= (12/15/21),“Early Deadline” I’m wondering if I need to add anything to qualify the 12/15/21 date. The date functions I’m finding look like they compare dates in two fields on the table, where my issue is that I only have one date on the table and am comparing it to another date that is not on the table. When I try to save my formula I get an invalid formula error. Thanks in advance!!

Hi There, Apologies as I am very new to airtable. Essentially, I have a column which contains a series of numbers which indicates how many businesses make it through a particular process. This number automatically updates based on my input in other areas of the table. What I would like to be able to do is have the success rate automatically update in the next row. I can do it on excel, but with airtable being a database I am not sure how to go about doing this. In excel it looks like this : How can I do this in airtable? The point of this is because I would like to then create a chart which keeps updating with this information, showing how many as a percentage make it through each stage. Thanks L

Hello! I am trying to find a formula that will extract a customers name and address from a body of text and create new single line text fields with this data. The data is coming into airtable via an email automation, and the body of the text data coming in is always consistent in format, with four pieces of data that change, the name, address, market, and URL for the customer. There are other keywords within the body that could be used to identify the before and after for the desired text - but the left, right, and mid formula’s will not work because each body will have varying lengths based on the customers info.

Greetings All. I am new to Airtable, but getting around pretty well. I have a Base with multiple tables. I am trying to automate Prospect tracking as much as possible. I have a situation where when I enter a Zip Code I want it to populate a District, Regions, POC Name, POC Email, and POC Phone. I was able to do that with Zips I coded with a linked table and associated lookups. However, I had to restructure the table and index by District rather than zip. I just cant figure out how to do a sort of VLOOKUP function based on Zips in Airtable because the zips are no longer the index on the table. So, I figured I could use formulas instead. This is the full text of my formula: “If the value from the “Zip Code” column in the “Prospects” table appears in the “Zips” column on the “List of Districts” table, then enter the value in the “District” column of the same “List of Districts” table.” My issue is I cannot write the formula to save my life!! Is this possible? I hav

Hello community, I’m trying to average geographic coordinates. First I split the coordinate into lat & lon fields, for example: 41.3310413 and 19.7828039 In Google Sheets I’m using average() with correct result In Airtable the rollup with average() fails with NaN Does anyone know another way of averaging coordinates? PS: Also tried to remove the dot with SUBSTITUTE(Lat,".","",1) but am getting an error.

I came up with the following formula. IF({Registration date}=BLANK(), ‘’, SWITCH({PLAN}, ‘A’, SWITCH({TYPE}, ‘1’,DATEADD({Registration date},30, “days”), ‘2’,DATEADD({Registration date},30, “days”), ‘3’,DATEADD({Registration date},30, “days”) ), ‘B’, SWITCH({TYPE}, ‘2’,DATEADD({Registration date},30, “days”)), ‘3’,DATEADD({Registration date},30, “days”), ‘5’,DATEADD({Registration date},30, “days”) ) ) ) Depending on the PLAN and TYPE, we want to return the value of {Registration date} plus the number of days. And if {Registration date} is blank, I want the value of the formula to be blank as well (to avoid #ERROR). However, the returned value is not recognized as a date and is displayed as follows 2021-12-17t00:00:0.000z If I remove the IF statement at the beginning, it is recognized as a date format. Is there any way to resolve this?

Hello, I am still very new to Airtable so maybe this is easier than I think but would still like help. In general, I am looking to write a formula so that when a field (that is a single select option) is selected to “deleted” it will then clear many fields (that are multiple select) of all of their data. So in the picture included below, when the field Deleted is selected to “Deleted” I want all of the data in the other rows to be cleared and back to like nothing had been selected. How would I go about this? Thanks

Hi Currently, I have 3-columns Due date (date field) Overdue (formula) IF(IS_BEFORE({Due Date},TODAY()),“ :rotating_light: OVERDUE :rotating_light: ”,"") Due Now (formula) IF(IS_SAME({Due Date},TODAY()),“ :gift: NOW :gift: ”,"") I would love to see both results/formulas in the OVERDUE column. Then it is not necessary for both columns. That said, I am not finding the right way of merging these two formulas in one field/column. Any help will be much appreciated. Thank you

I am trying to combine two fields in a base. Both of these fields are Linked Records. Is there a way to combine the two fields and retain the records. Currently I am getting text if I use CONCENTATE or ‘&’ Here is a demo base with the example https://airtable.com/shr1OM2ygBXu8ygke Thanks for the help!

Hi, I’m making a database of coffee beans. Roasters often sell in different units of measurement like grams, pounds, kilograms and ounces. I have a column for weight (number) and another for unit of measurement (single select). I also have price. Is there a way I can use a formula to calculate price per pound without requiring extra work from contributors?

Hi all, I have a table where I want to have a “Cleared?” formula box that looks up if the current record also exists in a view, “Outstanding items” In plain language, if an employee in “Pre-service” view also exists in the “Outstanding Items” view, place an :x: . Otherwise, if they don’t exist in the Outstanding Items view, place a :white_check_mark: I am thinking the RECORD_ID() formula might work (catching an exception) but I can’t quite wrap my head around how to use it with a specific view.

Hi Am fine with IF(FIND) formula when I want different outcomes but not sure how to set up when I want all outcomes to be the same. I know the outcome from AND/OR will be 1 or 0 which I can then work from but need help with how to write the actual formula! Below is my template formula I use to remember the IF(FIND) formula and adapt to whatever I need - if it helps to show me what I need to add where please use IF(FIND(“search#word”, {search#field}), “the#result”) Any help really appreciated have been searching but not finding yet. Thank you

Hello all. I want to do a simple sum on a group by. In my example, I am building a recipe calculator and want to find out the proportion of cost for each ingredient out of the total cost. My formula would be : cost/sum(cost group by recipe) In sql this is call a window function but I am not sure if there is the equivalent in Airtable. I have a screenshot, but seems like I cannot upload it. Must be because I’m a new in the community. Anyway let’s say i have recipe_a ingredient_a $3 recipe_a ingredient_b $6 recipe_a ingredient_c $4 recipe_a ingredient_b $2 I want a new column that would output that ingredient_b stands for 1/3 of the cost of recipe_a A workaround is to do a pivot table but it seems like a lot of trouble for something simple (plus not a very scalable solution) If you have any input, I’ll gladly take some. Thank you Stay safe !

Hi everyone, I have a table where user X can endorse user Y on a particular skill Z. The final table will be something like this: John | Carol | Painting Carol | John | Drawing Fred | John | Drawing Bill | John | Writing Then I will have a separate table with user profiles, and I would like John’s profile to display “Drawing (+2), Writing (+1)”. The way I would achieve this using a pure SQL mentality would be to group by name, skill, do a count, and then JOIN it with my user profiles table. The way I’m trying to achieve it with Airtable is to create a new column on every row with the value {Name} & “(+” & something & “)”. This would essentially be doing a row-level PARTITION and GROUP BY COUNT, and would allow me then to link to these records from the Profiles table and select the unique. In this case, we would have repeated Drawing (+2), Drawing (+2) and Writing (+1) for John, and the desired resu

My goal is to have a DOB field concatenate with a name and calculated age field. Currently the concatenate works fine with CONCATENATE(Child_First,", “,Gender,”, ",Age) = Rohnan, Boy, 10M When I add DOB field I get Rohnan, Boy, 10M, 2020-12-15T00:00:00.000Z How do I get rid of the T00:00:00.000Z at the end? When I use the Date_time format I get gibberish. CONCATENATE(Child_First,", “,Gender,”, “,Age,”, ",DATETIME_FORMAT(DOB,‘dd-mmm-yyyy’)) = Rohnan Boy 10M Tu-000-yyyy What I want to see is: Rohnan Boy 10M 15-Dec-2020

Hello, I’m writing with a question on how to set up a REGEX Extract. I assume it should be relatively straightforward but as a beginner I’m finding myself out of my depth. Any help would be appreciated! Goal: Extract a SKU number from a Webflow Ecommerce string. String Example: count: 1 height: 0 length: 0 productId: 618573ee195d39c0e1ba4199 productName: Test Board productSlug: test-board rowTotal: {‘unit’: ‘USD’, ‘value’: 100, ‘string’: ‘$\xa01\xa0USD’} variantId: 618573ee82825fd59dc4b5bc variantImage: {‘fileId’: ‘6181a0f79b29435921241715’, ‘url’: ‘https://uploads-ssl.webflow.com/618058264546c2bae56f2188/6181a0f79b29435921241715_Screen%20Shot%202021-10-14%20at%208.46.20%20PM.png’, ‘alt’: None} variantName: Test Board variantPrice: {‘unit’: ‘USD’, ‘value’: 100, ‘string’: ‘$\xa01\xa0USD’} variantS

Hi! I’m looking for help writing a formula. I need something that will automatically check off boxes in separate fields for 2/9 through 2/21 if individuals will be present based on the entered arrival/ departure dates. “if x date is between the range of arrival date and departure date, insert checkbox, otherwise blank” For example if the arrival date is 2/12/21 and departure is 2/20/21, I’d want it to check off all of the dates in between, but not 2/9-11 or anything after 2/20

I’m trying to put together a form for screening, however I cannot figure out a way to create a formula that would enable me to give an automatic output based on the form response. In short, if someone writes a symptom I want the last column to output a false, or a fail message. I have tried doing this but haven’t been able to. Here you can find a look into the form: And a formula I put together to try to get an automatic output:

I would like to create a formula that will: Sum the {Amt} fields only from records that have the identical data in another field {ticket/voucher #}. For example: Records 1, 7 and 14 have the identical data in {Ticket/Voucher #}. The formula would sum the amounts only from those records 1717.40 980.00 250.00 and put the total 487.40 in {Formula field}. The same would go for records 10, 12 and 15 and 11, 16 and 13. See link to image of my table. https://hctny-my.sharepoint.com/:i:/g/personal/office_highclasstravel_com/EesBZqFYxQpIvxnKzbAVLu8Bs6pO4BEkdNQWgzHui4Gj_g?e=4zSeTF

I’m using Eazyform app to get time slots separate from the date/time field. I am having trouble then figuring out what formula to use to calculate total hours for employee scheduling. Additionally I would like to be able to view in a Calendar view.

This ought to be easy. I want to display records on a calendar by a single date field, but the date field for display is calculated: It’s the DueDate if there is one entered, otherwise it’s CreatedDate. IF( DueDate=BLANK(), CreatedDate, DueDate ) Dates in, dates out, right? Nope. A formula field using the above formula cannot be used in the calendar, apparently because the result of the formula is not recognized as a date. Now the following DOES work: DATETIME_PARSE( DATETIME_FORMAT( IF(DueDate=BLANK(),CreatedDate,DueDate),"MM/DD/YYYY" ),"MM/DD/YYYY" ) Seriously? Is there not an easier way to do this?