Formulas

I'm really struggling with a formula which is the final step of my solution. I need a way to sum up only the top 4 values within a list of rolled up values (there may be between 0 and 5 values in the rolled up values array). Is this possible?The context is I'm calculating winners for a series of 5 rowing races, and we determine a winner by combining their 4 highest point values. My base is structured with the following tables:Races (e.g. "March 19, 2023 Race")Entries (e.g. "Boat #5"). These roll up to the relevant race.Competitors (e.g. "John Smith"). These roll up to the relevant races and entries.For each entry, there is a points value that is calculated based on the final time, boat type, and average age of competitors. Competitors in the same entry (i.e. boat) all get the same number of points for any given race. At the end of the season, we give awards to the individual competitors with the most points for their

Hi everyone,I’m looking to incorporate the tracking of SLA’s into our base to measure performance against timestamps. Currently I have a table of all our SLA’s with descriptions and numeric values for the SLA which will be used in the calculation. We have other tables where we’d be linking the SLA against a record to pull through the numeric value to then compare against a timestamp in a formula.However, we have some records where more than one SLA would apply to them for which we’d have multiple records linked. In this scenario I then get multiple numeric values for different SLA’s all in the same lookup column. The knock on is that I cant now use the numeric value of the SLA in the formula to calculate weather the SLA was met or not.Current Ideas:I could just set up columns for each of the SLA’s that apply to the records in the table I'm pulling them through to and hardcode the values in. However, if the SLA is changed then I’d be keen to avoid goi

Hello I currently use a UTM tagging template that was created by the team at linktosheets. But I would like to be able to transfer it over to AirTable. It should be a relatively simple project for someone who knows how to replicate the formula in Column 6 of this Google Sheet. I haven’t mastered AirTable formulas yet, so I’m looking for help with this. Please let me know if you’re willing / able to help with re-creating this sheet for me. Cheers, Alyssa

Hey all! Not sure what I did, but I'm trying to create a simple field combining the values of 2 other fields.Here is my formula:And here is the results I am getting:And I am trying to get it to retain Friendly date. Where are the extra values coming from, and why won't it keep friendly date?* Second try *My husband (who I have also converted to an AirTable lover) updated to this formula to remove those extra bits, but now it is adding a day to the displayed date? lolThis shouldn't be causing me this much trouble! lol

Hey guys im really dumb in Excel, I need some help. First column is the item, then next one I have records for each, that is the stock. I’m struggling for an hour to find a formula which COUNTS how many rows are there, therefore I have the exact numbers of the rows, which is the stock for each item. Thanks!

Hi! How do I individually concatenate fields if one of the fields is comma-separated? For example, I want to take “red, blue” and concatenate this with “shoe” to come up with ““red shoe”,“blue shoe”” with the quotation marks instead of “red, blue shoe”. Any ideas? Thank you in advance!

Objective Zapier to search Airtable records: Description, Amount, Transaction ID exists, if Not, create a record. Question Can Zapier search across three columns (see screenshot)? Screenshot https://dsh.re/65cc0

Hello,I would like a formula to convert date fild  into quarter and  add display it in full text.Exemple:Dates between 01/01/23 to 31/03/23  will display " first quarter"Dates between 01/04/23 to 31/07/23  will display " second quarter"and so on...Thanks for your precious help. Remy     

Hey folks,I have a table called Tasks and it has linked record called Owning Team I want to filter it based on Team RecordID something like FIND("recZyPUXXXXX", {Owning Team} & '') this does not work for RecordID although when getting the records via API Owning Team values are RecordIDs.

Hi,I am looking to build  a formula to retrieve dates between two dates. The information I am trying to retrieve is instances where a service user has attended sessions.The formula I am using is:IF(AND( DATETIME_FORMAT({Date Field}, 'DDMMYYYY') >= 'DDMMYYYY', DATETIME_FORMAT({Date Field}, 'DDMMYYYY') <= 'DDMMYYYY' ), {Data Field}, "" )The information retrieved is only examples where a service user has attended ONE TIME between those dates. If a service

Hi all,I've ran this code and don't have an error message coming up but when I click ok the final part of the 'If' formula is deleted.I'd like it to run so that all parts of the if formula work (three options): IF({Always On/Miniseries} = "Always On" , {Total Budget (USD)}*((13-{Number (from Start Date)})/12) ,IF({Always On/Miniseries} = “Existing Always-On" ,  {Additional Budget 2023 (USD)}) ,IF

I don't know if this is possible .... if anyone tells me to restructure my data, I'm quitting lol I have Orders, Products, and Warehouses table. Using linked records, I assigned various warehouses to products. And then when orders are placed, it's looking up the warehouse based on the product.  I want to show open open orders per warehouse in a rollup, but it's rolling up the products .... not the count of linked orders. I cannot some come up with how to write this formula .... it feels like it should be simple!  The linked records field is set to only lookup orders that are not yet shipped. This is what I currently have set up as the formula - it's counting the number of products assigned to the warehouse though .... not the number of orders. 

I am new to Airtable and Formulas I saw a post by Florian_Kleinicke Who said he had managed something similar, could someone please explain how I use the formula below. DATETIME_DIFF(DATETIME_FORMAT({Birthday},‘MM-DD’),DATETIME_FORMAT(TODAY(),‘MM-DD’),‘days’) My problem (which is likely is also a problem here) is that TODAY() won’t update on its own. It stays the same for a while. Is there a solution to that problem? I’m using automations to send an email, once the computed difference hits 0. Ah I just came um with a solution, that might circumvent that problem. I can compute the birthday DATETIME_PARSE(DATETIME_FORMAT({Birthday},‘MM-DD’),‘MM-DD’)

Hi guys, When I’m using Blank() in a formula (to flag empty fields) it also counts the number ‘0’ as a blank, and the other way around. So when I have a formula that says {MyNumberField} = 0 it also registers the empty fields as zeroes. Has this always been the case? At the very least I’d expect the more specific {MyNumberField} = Blank() to neglect ‘0’ fields. Arthur.

Hi,I'm working with the NPM package to get data from tables which have lookup fields and I'm wondering if there's a way to ignore these lookup fields in the initial API call either by using some parameter or `filterByFormula`. I've checked the API documentation for my tables, checked the documentation for the Formula field and nothing seems to be able to filter by field type.EDIT: I guess the question can also become, is there a way to ignore fields when using the `base().select()` method? I have a list of the fields I want to ignore from the Base schema but can't find a way to ignore those fields. I can just drop them from the returned data afterwards but that is going to be fairly expensive as we're processing potentially tens of thousands and eventually millions of records. Thanks in advance,Thomas

I'm not sure if what I'm trying to do is possible, but here goes:I'm working off of an existing base template, and am trying to tweak one of the formulas that's in there. The existing formula is as follows:I would like to add one additional condition: if Equipment Status is "Unknown" the result is"❌ Not Available". Since this is pulling from a different field than the existing SWITCH formula, I can't add it in there; I've tried nesting another "If" within this formula, but that also doesn't seem to work. It may be that I simply can't do this, but if anyone has any ideas or suggestions, I'd appreciate it--I'm pretty new to Airtable formulas, and trying to figure out how best to leverage these formulas. Thanks in advance!

I'm trying to create a date formula with the next/upcoming birthdate from a staff profile with Date of Birth. I need this to be formatted as a date so I can use it in a calendar, which has proven to be more difficult than I anticipated. Here are 2 ways I have gone about it. Any suggestions?Attempt #1:DOB is Staff member's Date of Birth:IF(DOB = BLANK(),BLANK(), DATETIME_PARSE((((MONTH(DOB) & "/") & DAY(DOB)) & "/") & YEAR(TODAY())))-the result is shy by 1 day. Birthdate of

Hello Formula Gurus- I am trying to convert some excel formulas to airtable and struggling a bit.  This is the formula in excel:=IF(J2>=8.5,"A",IF(J2>=7.5,"A-",IF(J2>=6.5,"B+",IF(J2>=5.5,"B",IF(J2>=4.5,"B-",IF(J2>=3.5,"C+",IF(J2>=2.5,"C",IF(J2>=1.5,"D",IF(J2>=1,"F")))))))))Any help is appreciated!  thank you 

I'm hoping someone can help me with a date formula and how to implement it.I've got a project plan (with dependencies) and I've got three fields, "start date" "end date" and duration.The project has a kick-off date and all start and end dates cascade from there based either on the duration of the task or a a dependency.All dates to be calculated on a M-F work week.When a task is marked complete, the end date needs to be updated to the current date (thus re-calculating the tasks dependent on that end date).Bonus points for the ability to integrate holidays into the calculation.I'm completely lost on how to achieve this. Any help you can offer is much appreciated.Thanks.

Hi TeamI need to know what formula I can use to subtitute the culumn at the left, this is a multi select culumn to the culumn a specific name. It seems possible to have severall subtitute fonction in a row but my formula doesnt work.Any suggestions?Here what I tried to test SUBSTITUTE(SUBSTITUTE(SUBSTITUTE({Annonce}"Lovely Clichy Home - Mairie de Clichy / Levallois","Xavier Clichy"), "Studio au pied du Sacré-Coeur","Feutrier"),"Magnificent duplex in Montrouge near Metro Barbara","Barbara

Let's say I have 3 date columns namely Attempt #1, Attempt #2, and Attempt #3. I want to send Attempt #1 on May 1, 2023 --- then I want Airtable to automatically compute the next follow-up date (which is after 3 days). But if it falls on a weekend, I want it to automatically add the nearest weekday date available. The same goes for attempt #3, I want Airtable to compute the next follow-up date based on the date indicated in attempt #2. Is this possible? 

Good day all.I have a formula that currently works:IF({Course Status}='Retired',"RET-"&CONCATENATE(LEFT({Course Level},1),{Course Type Coding (from Course Type)},".",{Sequence Number})&"-"&{Course Name},CONCATENATE(LEFT({Course Level},1),{Course Type Coding (from Course Type)},".",{Sequence Number})&"-"&{Course Name})&" "&{Engine Version}However, I now need to add an additional IF statement to check for Archived:IF({Course Status}='Retired',"RET-"&CONCATENATE(LEFT({Course Level},1),IF({Course Status}='Archived(DO NOT DELETE)', “ARC-”&CONCATENATE(LEFT({Course Level},1),

I extracted unique names of participants in my table by using the ARRAYUNIQUE formula in a field called {Progress Made BY}. This produced a list of unique names separated by commas in the {Progress Made BY} field, which is what I was looking for. I used this one instead of CONCATENATE because I had a couple of duplicate text strings. I then created an IF LEN SUBSTITUTE formula field called {# Participants Progress Made BY} to count the names separated by commas produced in the {Progress Made BY} formula field. IF({Progress Made BY},LEN({Progress Made BY})-LEN(SUBSTITUTE({Progress Made BY},",",""))+1) *Note that I had used this formula successfully in other tables to do the same thing, however the reference field in the other formula fields was a CONCATENATE formula field instead of an ARRAYUNIQUE formula field. I get an #ERROR message in the {# Participants Progress Made BY} field where ther

Hello!My table is the first step to generate documents with personalized names. Sometime there is 1 name on the document, sometime 2.List of my columns :Name 1Name 2Name 2 + and (formula)I'm trying to find the good way to write the formula of my name 2 + and column : Option 1 : there is a second name and I need « and name 2 »Option 2 : there is no second name and I need a blank field At the moment, if I have no « name 2 » I still see « and » in all my fields 🙂 There is my work/try :CONCATENATE({And}, " ",

I'm trying to get this formula to calculate the difference between the date 'today' and the date in the field 'End date', then stay blank if it it's more than 10 days but return 'Yes' if it's 10 days or less. I can't get it to return the right values though. Any suggestions for how I can fix this to get the right result?  IF(DATETIME_DIFF(TODAY(),{End date},'days') >10, " ", "Yes")