Formulas

The following formula in the Airtable blog post here, under the heading “Find the next occurrence of a specific day :hourglass_flowing_sand: ” with the description: “if you just need to know the nearest Tuesday to a given date—even if that Tuesday has already passed—this simpler formula will do the trick:” is as follows: SET_TIMEZONE(DATEADD({Date},2-(WEEKDAY({Date})),‘days’),‘America/Los_Angeles’) (It is suggested that the 2 in the formula relates to finding the nearest “Tuesday”, and can be replaced with any other day from Sunday (0) --> Saturday (6) ) I noticed this formula break when the given date is a Sunday and you are looking for the nearest Thursday (4), Friday (5) or Saturday (6) (which would occur before the given date). Since WEEKDAY({given date}) of a Sunday resolves to 0, The formula will never return a day in the past. More generally, if given date is Monday (1), this function will never return a day more than 1 day in the past (e.g. Saturday) if given date is

Hello, I receive time data in UNIX format in a text field on airtable. I would like to create a formula that allows me to have a date that I can understand, in the same basic format already on airtable. Could you help me? Thank you in advance I’m living in Switzerland, Zurich time

Hi AirTable friends, I have records that each have a long text field which is TOO long. That long text field is called “Excerpt.” What I’d like to do is create a formula field called “Trimmed Excerpt” that copies the content from “Excerpt” field but limits the word count to, say, 50. What’s the formula for that? I Googled for a solution but came away empty-handed. Thanks to those of you who take a crack at this one!

Can you do simple references within formulas? Current formula: IF({Frequent escalation?} = 1, “However, {he/she} frequently escalates, and so {he/she} needs a significant amount of support.”, “{C He/She} does well there with minimal supervision and supports by adults.”) The formula is working, except the pronouns are not. They are of course referencing other formulas, and there is a checkbox called “Frequent Escalation?” If not (siiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiigh) any suggestions on how to get around this? I am eventually wanting to get to more advanced texting formulas, but need to figure this out! Thank you so much!

Hi everyone, Now that I have Airtable all up and running with formulas etc. I have hit a snag. I understand we cannot link between bases but we can link within a base. I’m trying to create a formula that will decrease my stock column by 1 when a NEW appliance is installed into an apartment via the Maintenance tab under the Comments column. Am I able to do a IF formula in Inventory to read the IF Comment under Maintenance reads NEW - 1 from Stock? I don’t know if the formula will work because the Comment field is typed in.

Here’s the formula I’m using: IF( {Number Field 1}!=’’, ({Number Field 1} / {Weight (number field)} + {Number Field 2}), IF( {Number Field 2}!=’’, ({Number Field 1} / {Weight} + {Number Field 2}), ‘’) ) My goal with the formula: I’m trying to reach an inventory count as a result of this field. A) If the inventory count is low, we can manually enter the exact count of the part into “Number Field 2” B) If the inventory count is higher, we get an approximate count based on weight. So we enter the weight into “Number Field 1” of the total part. It calculates total weight vs the part weight to give us a part count. C) If for some reason during the process we weigh the inventory, but find more of that part, we sometimes add the exact count on after. So if there’s numbers in each of the field, we need the formula to account for both. The result of the formula “works”, as in the numbers are right. But it isn’t a number output. So for us to use the result of the field, or format the output, it

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.