Formulas

I want to provide a workaround solution given that Airtable natively cannot do much with the change log, and I want to create a column that would update another column (within the same table) every 1 or 2 days. For example, something like the below: Column B = IF( Last Modified Date < Today - 1, = Column A, IF FALSE DO NOTHING). Through this I am hoping that even though Column A is continually changed, I can see the previous value of Column A via Column B. Thank you in advance, and please let me know if I should explain further.

Hi! I’m going crazy trying to come up with an NPS formula. I am planning to have a formula return -100, 0, or 100 depending on the value so I can then average that column on dashboards. I can get my NPS just fine, but I can’t figure out how to exclude blank cells in an IF statement. In words, I want the following: If {Program NPS Value} is between 0 (inclusive) and 6 (inclusive), -100 If {Program NPS Value} is 7 or 8, 0 If {Program NPS Value} is 9 or 10, 100 If {Program NPS Value} is BLANK, “” There’s gotta be a way to factor out these blank cells. I’m getting -100s for all my blank cells with this formula: IF({NPS Value} < 7, -100, IF({NPS Value} > 8,100,0)) I appreciate your help!!

Hi there, Im looking for a bit of help with a formula. I’ll try and explain it as best I can… I have six fields as follows: 1st Payment Date (date type) (e.g 1st Jan 2020) 1st Payment Amount (Currency) (e.g £500) 2nd Payment Date (date type) (e.g 3rd Jan 2020) 2nd Payment Amount (Currency) (e.g £500) 3rd Payment Date (date type) (e.g 5th Jan 2020) 3rd Payment Amount (Currency) (e.g £500) What I would like to do is create another field with a formula that would basically sum all the payment dates which fit within a specific date range (an example date range relating to the fields above would be the 1st to the 31st Jan 2020) Would anyone be able to help with this? Thanks in advance!

I have a bunch of different strings of text and want to remove different phrases, but I’m unsure of the formula. Here is an example of what it may contain: 123.jpg 234.eps 345.ai 546%20(1).eps I want to remove every instance of “.jpg”, “.eps”, “.ai” and “%20(1)” Is there a way to do so?

I am trying to use an IF statement and I can’t get it to work either with an AND or an OR formula (or both)… Essentially I have a calculated price for my products and I want to create a formula field for a selection of the products IF ‘Sub-Category = X OR Y’ then take ‘Calculated Price +0.1’ to add 10p to the calculated price. I have tried this which returns a result for all products and not just those where the Sub-Category is “Coffee Retail Beans” or “Coffee Retail Ground”. IF(OR({Sub-Category}=“Coffee Retail Beans”, “Coffee Retail Ground”), {Calc Price}+0.1) Any other combos I try using AND statements where I have followed other similar examples on different threads, but they always come back with a formula error. Basically I have a list of wholesale and retail products each with a calculated price and for customers who only buy retail I want to add 10p to their price, but I want this to only apply to products with a retail ‘sub-category’. If the ‘sub-category’ isn’t a retail one th

Hi. I’m currently wondering if there is a way to extract a section of text from a URL. http://...Drink%2FSeafood%20%2D%20shutterstock_524277715%2Ejpg&...Images%2FFood%20%26%20Drink I want to Airtable to automate searching the link for “shutterstock_” and then display the following characters up to “%2E” The link will always have a different amount of characters before “shutterstock_” and after “%2E” and may contain a different length of characters between the two. Is there a formula for something like this?

Hi, I’ve been using Airtable for quite a bit since I started my new role and I need help with this particular thing: I have a table that has the following fields: Line items (Linked Record to Products - Multiple allowed) Customer Type (Single Select with options A, B, and C) Payment Type (Single Select with options CASH and CARD) Lookup Value (Formula) Charged Items (Linked Record to Pricelist - Multiple allowed) Products has the sample data: Product1 Product2 Product3 Pricelist contains prices for all permutations of Product-Customer Type-Payment Type and has the following fields: Transaction (Key, Formula - concatenation on Customer Type, Payment Type, and Product) Price (varies per permutation) For example, a transaction with Product1, Customer Type A, Payment Type CASH would yield “CASH A Product1” as the key for the record. Now back to the very first table - I’d like to customize the formula in Lookup Value to create comma-separated permutations of each linked record in Line Items

Hi All, I’m trying to use LEFT() to convert the following input: 2020-09-30T17:30:00-04:00 to 2020-09-30 Can anyone help me with it? Thank you.

hi everyone, is there a formula that can compare 2 dates (a due date and the completion date of a project) and fill in if it was finished on time, early or late?

Hi there, I have table named “Groups” which contains fields about a Group Therapy Session along with two Lookup columns to another table called “Session Master” which contains the materials(i.e. books) used for the session in the group. Now, in the Session Master table the fields to which the Group’s lookup columns are pointing to are named “Pre-Session Materials” & “Post-Session Assets” respectively. Those two are actually links to multiple records of another table named “Content Library”. What i want to do is to find all the Records from both the Pre-Session Materials & Post-Session Assets and combine them into one column within the Groups table. Is that possible? I have this formula already which just joins the contents of both of these as a string array but i need them to be record links so that i can show them in stacker as interactable content. The formula is the “All Session Materials” column in the Groups table and its formula is ARRAYJOIN({Materials from Sessions}

I have a formula set up to calculate and display whether a customer has paid for the product they’ve taken in full, and, if not, how much they still owe. It looks like this: IF(SUM({Cases Out}*60-{Money In})=0, “Paid Up”, “Owes $” & SUM({Cases Out}*60-{Money In})) It works, but the problem is that Airtable seems to take a long time to calculate the answer. When I update the Cases Out field for instance, it’s common to see the output from this function go through two or three “intermediate” answers, where it will display one number, then another, until finally it gets to the right final result. It isn’t a big deal, but it takes a few seconds and makes the user initially feel like it isn’t working right at all. It’s also strange: why is a simple calculation taking so long? I’ve never seen a function output be anything less than instantaneous in Excel or Google Sheets. Is something about the way I’ve written this taking an outsized amount of processing resources?

I need to add an additional carriage return within this formula where the red maple leaf is in the below formula. I’ve tried multiple different ways but I’m unable to find success. If anyone has any solutions would be most appreciative!!! CONCATENATE(Patient, “\n”, DATETIME_FORMAT(SET_TIMEZONE({Start Date}, ‘America/New_York’), ‘LT - dddd :maple_leaf: MMM DD/YYYY’))

Hello everyone, I would like to have a formula column in a table where I have a code name as the first column today. This formula should check the code and give me a determinated result. Today my codes are SFPAPXXXXRI004, or SFPAPXXXXBA005. I would like a column that says “Ring” if there is an “RI” on the code or “Bracelet” when there is a “BA” and so on. Is this possible. Many thanks for your help!

I am new to airtable and trying to complete a table that has a single select choice in column A for make of the car and I want to have a single select choice in Column B that is contingent on the choice made in Column A…i.e. If Ford is chosen in Column A, then the only choices that are present to you in Column B are Explorer, Escape, Expedition, Fusion, Mustang, Thunderbird. But if your choice in Column A is Mercedes then the choices you have to choose from in column B are C Class, E Class S Class, or SUV, etc thanks shari

We have a field called Products which is a link to our “Product” table We have a field called Cost which is a lookup field. (looks up the cost from the Product table) In our “ORDER” table, we have a field called Total Cost In that Total Cost field we would like to multiply the Cost by the Qty (quantity) but if there is a value in the Misc Cost field then we would like to multiply that field by the Qty to calculate the Total Cost Here’s the kicker… Basically, we want to use the Misc Cost to override the Cost x Qty total, if there if something is entered in that field. Usually a product is full price for us. Sometimes we get a massive discount on a certain quantity or we get some of a product for free. I still want to link to the existing product from our database, but I want to have this Override field and have my “Total Cost” field use the Misc Cost field, if it’s exists. Sorry if I didn’t explain this very well. Any help here would be great!! :pray:

I read several posts on what I hoped was this if/then topic, but there are *so many if/then questions out there and I still didn’t find my answer (it all depends on my own keywords, of course). Please combine this into the right thread if it already exists. I need to track a biannual speaker event into am/aft/eve sessions over 2 days. I have info duplicated because: I’m still in the experimentation phase to see which formats work best I need the info to be sortable/filterable/analyzable in many different views any columns can be hidden as long as the essential result is achieved (yes messy but I’m an amateur) I’m looking for a way that a checkmark in one of 5 columns returns a unique label in one column. I’m fine if the labels are returned in text form or multiple selection form; that’s less important. I figured out the if/then assuming an on/off for one column, but I’m lost as to how to create an if/then for on/off between 5 columns. I included a screen shot of what I’d like it to

In {#Gew uit} i want to know how many times 25 (= teamnummer) is in {resul.gew.wedstr.uit} I don’t know what formula to use. I tried FIND({teamnummer}, {resul. gew. wedstr. uit}) but that counts all the nummers.

I have a duration/run time field I’m importing, currently stored in Milliseconds. Anybody have a quick formula to help convert that to mm:ss? Thank you.

I am trying to create a priority score for a team travel destination based on a series of checkboxes. This is what I tried: SUM(IF({1 Must Do}5,0),(IF({2 high on list}4,0),(IF({3 would like to do}3,0),(IF({4 maybe if in the area}2,0)) This isn’t being accepted. Any help appreciated.

I’m new to this but I am trying to check if a text field includes a set of key phrases. I have a Customer Table with a text field called “Notes” where someone could type a note in a cell. There is a Phrase Table with a text field “Phrases” that holds a set of predefined phrases. I would like to check if any of the phrases are found in the note. This is what I got, the field is located on the Customer Table. It returns False in all cases. IF(FIND(Phrases ,Notes)>=1,“True”,“False”)

Hello- I have a formula field that multiples values from two other rows. It functions properly when multiplying values without decimal points, e.g., 1, 2, 3, 4, etc. But fails to account for values with decimal points, e.g., 1.5, 2.5, 3.5. The odd thing is that for those values (the ones with decimal points), it actually accounts for the numeric portion of the values without the decimal value. Specifically, for 1.5 or 2.5, it will multiply based on 1 or 2, respectively. It ignores that there is a .5 tacked on as well… Here is the formula: IF({Mins}=15,".5",IF({Mins}=30,“1”,IF({Mins}=45,“1.5”,IF({Mins}=60,“2”,IF({Mins}=75,“2.5”,IF({Mins}=90,“3”,IF({Mins}=105,“3.5”,IF({Mins}=120,“4”," ")))))))) Not sure if this is helpful, but when I click on the “formatting” tab within the field, here is the response: “Your result type is not a number or a date. Formatting options are currently only available if your result type is a number or a date.” Thanks in advance!

Hi! I’m working on a database that tracks kittens placed in foster for a shelter. I know how to use DATETIME_DIFF to get weeks, but am hoping to get weeks and days. Ideally, I would like to have it read XwYd. Can anybody help?

I am trying to calculate the age of a member by counting the number of days between two date: Birthdate and the current date (auto entered) and divide by 365

Hello, I asked for some help with a formula to convert a single-select field to a date field, and was sent a link - but unfortunately I cannot access it. Can someone please provide a way to access the support article I was directed to? The topic is how to use the DATETIME_PARSE formula to convert that to MM/YYYY . Here’s the support article link I was sent: support.airtable.com Airtable - Support Thanks for your help! V Let me know if that helps!

Hi everyone, What is the simplest way to convert a single-select field, which is the full word month (i.e., “January”), to a usable date field that provides the current MM/YYYY (i.e., “01/2020”)? I’m integrating to Airtable from JotForm, and the field in Airtable has to match the format of the JotForm field. So my JotForm field is formatted as Dropdown, and my Airtable field (Field 16) is formatted as Single select. My Airtable integration works for the Field 16, but I need a formula for my Activity Month field to convert that to MM/YYYY. Thanks so much for your help! V