Formulas

I’m trying to grab a variable amount of text from between two consistent characters, > and <, to format HTML. I’m using Zapier to convert rich text to HTML and then using an Airtable formula to modify the URLs with UTM codes. Example below. Starting Text Cooperative Currency Desired Output Cooperative Currency From this ">Cooperative Currency< To this Cooperative%20Currency">Cooperative Currency< Thank you! ~Brian

Hello, I am trying to do a simple DATEADD function. Add X number of days to Y date. I have a date formatted column and an integer number column. The formula column has this formula in it: DATEADD({Last PM},{Interval},’days’). I’ve tried every variation of the DATEADD function that I could think of, and nothing works. I’m getting this error: “Sorry, there was a problem saving this field. Invalid formula. Please check your formula text.” I can use the WORKDAY() function, without an issue. But this isn’t what I need. Not sure what I’m doing wrong. Thoughts?

Hello airtablers, I have a field {RESPONSE} with 5 possible choices A,B,C,D,E I need to be able to give a score based on each response. So if response is A OR B score is 4 if response is C OR D score is 1 if response is E score is 3 This would be easy to solve with a switch function but the cell with responses returns all the responses concatenated in a single array (A;B;C;D;E). Is there a way to score the responses? Thanks a lot

Hi Airtable Friends, I want to combine four things in my primary field: (1) Story ID#, (2) Date, (3) Storyteller, and (4) Story Title – all separated by a " - ", of course. I was able to get the first two things with this formula: ({Story ID#} & " - " & DATETIME_FORMAT(Received, ‘DD/MM’)) & " - " But when I follow this up with {Storyteller} & " - " & {Story Title}, I get a message that says: “Sorry, there was a problem saving this field. Can’t save field because it causes a circular reference.” To be clear, here’s the full formula that keeps getting rejected: ({Story ID#} & " - " & DATETIME_FORMAT(Received, ‘DD/MM’)) & " - " {Storyteller} & " - " & {Story Title} I’ve tried a number of variations using parenthesis and curly brackets, but always get the “sorry” message. Grateful to anyone who can help me get past this irksome obstacle!

I think the formula posted to the Airtable blog here, under the heading " Figure out which date comes first :soon: " is way too complicated. The formula on the blog is posted as: IF(IF(XOR(BLANK(),{Expiration Date},{Date})=1,"",IF(OR({Date},Expiration Date)=1,IS_BEFORE({Expiration Date},{Date}),"")),“ :warning: Expired”,“Ok”) Mistake #1 is obviously that the second Expiration Date needs {}. Simple enough. But couldn’t it be written more simply as: IF(IS_BEFORE({Expiration Date},{Date}),“ :warning: Expired”,“Ok”) ? I get the same results. Even if we stick to the same original formula, I can delete all of the bolded bits and return the same results: IF(IF(XOR(BLANK(),{Expiration Date},{Date})=1,""

Hi all, pretty new here! I just want to set up a field to capture grams and kilograms (weight). Do I set cutomize the field to number? I’d like to to display KG or g like it would if I added a currency $/£/€ etc. Is this possible? Thanks for any help you can provide!

Hi, I’m writing an item order table and have been successful in pulling prices from a matrix based on qty input using the Tiered system, but I find I now need to include the ability to override the price. The Tier Formula is below and works - it populates the name of the tier based on the quantity input IF(Quantity > 1, IF(Quantity < 5, “Tier 2”, IF(Quantity >= 5, IF(Quantity < 10, “Tier 3”, IF(Quantity >= 10, IF(Quantity < 15, “Tier 4”, IF(Quantity >= 15, IF(Quantity < 25, “Tier 5”, IF(Quantity >= 25, “Bulk”)))))))), “single”) Once the tier is populated, I’m using a SWITCH formula to pull in the correct price based on the price tier SWITCH((Tier), “Single”,{Single Unit Cost}, “Tier 2”,{2-4 Units Cost}, “Tier 3”,{5-9 Units Cost}, “Tier 4”,{10-14 Units Cost}, “Tier 5”,{15-24 Units Cost}, “Bulk”, {Bulk 25+ Plus})

I have a formula I can’t figure out. My column that I want as a formula is called “Forecasted SCIP Received” To calculate this date, I need to use another column called “Actual Site Walk Date” plus a “Duration” column. However, if “Actual Site Walk Date” is empty, I need it to use “Forecasted Site Walk Date” instead, so: If the “Actual Site Walk Date” is not Empty, then “Actual Site Walk Date” + “Duration”. But if Actual Site Walk Date is empty, then “Forecasted Site Walk Date” + “Duration” How do I write this?

Hey, im trying to get the end result to change based on the input from the other inputs, I’ve currently got a simple version of this where it ticks if everything is signed off and if not then X but what I want is some form of hierarchy. e.g if everyone says they are signed off but one is blocked then the main status is blocked… similar if all are signed off but one in progress and one back burner then the status is back burner. Is this possible :slightly_smiling_face: ?

Hi everybody, I am new to using Airtable. I am sure that there is a formula for this but I can’t seem to find it or I don’t write it in a correct way. With a API service of a third party software I pull data from my Airtable. This data is dynamically filtered by using filterByFormula. The data I need is in the column called “groupcode” and has numeric values varying from 100 and 900. These numbers stand for product categories. Normally if I need products from the category with the number 212 I use the following formula. {groupcode"} =“212” But now I need products from category 212 and 268. Is there a way I can display only the records of two different specifc values ? Any suggestions or tips are appreciated! Thank you in advance!

I’m searching for the occurrence of a specific character in a text field with SEARCH but it doesn’t return any answer, can you help?

Hi everyone! I have a problem to solve =/. I need to find matched items between BUCKET and PERSONA tasks, but I don’t know how to do this.

I am having trouble with a formula. I need to bring back an exact minute time but cannot figure out how to make it read the minutes in this equation. Can someone help?

Hey all, I’ve been trying to work this out on my own but it’s providing super difficult. I have a birthdate field and I want to find out the age. I am using DATETIME_DIFF(NOW(), Birthday, ‘years’) & ’ years ’ and I see the age however those that haven’t filled in the birthday field say Nan. I would love some help. Thank you

Hi! So I have a main table which has a series of streams of work. There is a second table with ALL the activities related to those streams. The tables are linked such as on the main table it brings in ALL the activities in a string. I want to bring forward just the next activity for each stream. The roll up function works for the date but I can’t seem to get the text description in. I basically need to say. Of all the activities related to this record, give me the next one. But because they are in different tables, I’m stuck. Any tips would be very much appreciated

I am attempting to calculate asset depreciation using Airtable. I am calculating using the following Fields: Purchase Date: Date field Unrecovered Basis 1: A formula which is returning a currency value 1 Year (from Depreciation Conventions): This is a lookup to another table which has the 1 year depreciation amount. This is a numerical value (decimal) I have reduced the formula I am attempting to use down to it’s simplest form. There will be more switch states, but it doesn’t even work with the 1, so I didn’t want to confuse matters by adding more. The formula returns a result just fine, but I am unable to format it as a number (currency) and thus, am unable to use the value in future formulas. The Formula is this: SWITCH(DATETIME_FORMAT({Purchase Date}, ‘YYYY’) - 2015,1,{Unrecovered Basis 1}*{1 Year (from Depreciation Conventions)},BLANK()) When attempting to format the field, it state

I have a field that is bringing in names using the web clipper block. The problem I am having is the names from the site come in with additional header, and extra space between them. Is there a way to clean this up in a formula or in the web clipper on import? Here is the example: Clients: Bob Jenkins Sarah Hunter I need it to look like this: Bob Hunter, Sarah Hunter Thanks as always!

I have a table with 200 records and two fields: cost and sold. Im trying to link to another table using the link to records to calculate the average sold price but I have not managed to make it work. Does it have to bring all the new records in the second table? ITs like i have to select 1-by-1 for all of them Thanks

Hi, Airtable newbie here. We are trying to setup a table that calculates farm type based on size of farm. The result we are looking for is this: Acres…Farm Type Empty…Unknown 0.0…Landless 0.1-5.0…Small 5.1-20.0…Medium 20.1+…Large Trying to use this formula: IF(Acres = "", "Unknown", IF(Acres >= 20.1, "Large", IF(Acres >= 5.1, "Medium", IF(Acres >= 0.1, "Small", IF(Acres >= 0.0, "Landless", "Unknown" ))))) This is causing a conflict between Unknown and Landless. Any idea why and how to fix this? Please help. Thanks in advance.

I am exporting orders from Woocommerce using Zapier into Airtable. The Order from the customer is received into two separate cells like this; [Apple,Orange,Pear] and [1,3,5] I need Airtable to recognise that order is for; 1 x Apple 3 x Orange 5 x Pear I also need to know how many I require for a certain City. For example if I receive the data like this; [London][Apple,Orange,Pear][1,3,5] [Manchester][Orange,Banana][2,5] [Manchester][Apple][10] [Birmingham][Orange,Pear][3,4] [London][Pear,Orange][3,7] I need to know; London - Apple x 1 Orange x 10 Pear x 8 Manchester - Orange x 2 Banana x 5 Apple x 10 Birmingham - Orange x 3 Pear x 4 - Total - Apple x 11 Orange x 15 Pear x 12 Banana x 5 Please note that I will not be able to input the list of Cities and it would be Difficult to list all o

Hi, I have a table with several categories and they all have a month assigned to them (essentially types of activities being undertaken in that month). I want to create a view that shows one category across specific months. For example “Real Estate” in March, April and May. I can’t do it with filters, because I would need to combine AND OR filters. I saw a post about combining formulas: https://support.airtable.com/hc/en-us/articles/221561507-Combining-AND-and-OR-filters but can’t seem to write the correct formula for this. I would really appreciate any help! Thank you so much!

Can I create a formula for calculating Interest like the one I use in Excel: UPB*{Interest Rate}*(({To Date}-{Bill of Sale Date}))/365 In Airtable, this formula generates a NaN error message: I got it to work by breaking it out into two cells and using the formula recommended for differences in dates from Airtable’s support page on NaN errors: Cell labeled “test:” IF( AND( {Bill of Sale Date}, {To Date} ), DATETIME_DIFF({To Date},{Bill of Sale Date},‘days’), BLANK() ) And in another cell, this formula: UPB*{Interest Rate Total}*(test/365)

I have a field named Postcode Area which is type formula and has nested IF statements. Is there any way to add a second action to an IF statement such as: IF (A=B, (action 1 & action 2), action 3) I would like to take the value that is returned from the IF statement and paste it into another field within the same table so that I can use it in another lookup. If there’s no possible way to do that in the IF statement, is there another way to duplicate the value of Postcode Area into another field? I have done this successfully with a script but I don’t know of a way to trigger the script when a form is submitted.

Hello Airtable Community! My table has a single select field with 3 options. The field is called SVC Broad Category: There is another single select field with 20 options called SVC Area: When we add tasks to the table, we assign them to a SVC Area and to a SVC Broad Category. I am hoping for assistance with an IF Statement for SVC Broad Category, so that whatever is selected in SVC Area (e.g. Alcohol) is automatically assigned to the right SVC Broad Category (e.g. Ops/Logistics). The image below shows the Areas and Categories. As always, any assistance is greatly appreciated.

hi, I am new to airtable, and been trying with no results to update a heavy equipment fleet location when in a different table I create a record that moves a piece of the fleet from jobsite A to jobsite B, could anyone point me in the right direction…thanks.