Formulas

Hello everyone. I am new to the Airtable world and I have a question that I would like your help with. I have 17 number fields. They are called A, B, C, D, E ...I want to have 5 fields called max1, max2, max3, max4 and max5. Max1 field is going to be the highest value of those 17 fields. Max2 is going to be second highest value of 17 fields and so on till max5. (They don't have to be unique.)I could find the highest value with the formula MAX(A,B,C,D,...) but couldn't find any info on how to find the other max values.Example:ABCDEFGMAX1MAX2MAX3

Hi all, I'm trying to extract the first set of data in this single select field using LEFT() and then converting that into a date field. The data extracted will be in Chicago time. When I run this formula, it assumed that the date is in GMT,  and doesn't respect the SET_TIMEZONE. (note last entry where the time should be 3pm but the formula returns 10 pm central)Any ideas what I'm doing wrong here?Thanks! 

I'm trying to use either a SEARCH() or FIND() function to locate a comma within a string. Unfortunately, these seem to be broken on Airtable right now. Am I missing something? This should be pretty straightforward, right?

Hello altogether, this is driving me crazy. I am entering some formulas in my field and while I am adding fields, they disappear. This behaviour is consistent for all the fields. What is going on? Is it because there are too many referenced fields?  

I have a base that has client names in a table and appointment date on another table that is linked to the specific client. I am trying to create a formula in the client table that will display the last appointment for that that client that was scheduled and another formula field that will display the next appointment date for that client. When I try to create a formula to look at the linked appointment date table from clients, it says it can't be calculated because it's not a date. Is there any way to make this work, which I am missing? Thanks in advance.

Hey I'm quite new but couldn't find an answer : I'm looking to show the average of a column in the interface. To be specific, I have a set of data from one table and I'm selecting certain data from another table, and I want to calculate the average of specific values from this other table in the interface. (It's for managing events, so I have a list of events in one table and another table with all our members and for example their age, and I select the participants in the specific activity and want to be able to calculate the average for example of the age of the participants from the list of members).Hope it's kinda clear, thanks!!

I have an Airtable database set up where we track entrepreneurs we work with in Sprints (Jan-Apr, May-Aug, Sept-Dec). One feature I need to implement is a way for our team members to submit revenue updates as often as they need based on entrepreneurs they work with that earn revenue.The way I currently have it set up is the following:I have a form created called revenue updates for the team to submit when they need revenue updates for their entrepreneursIn the form and in the airtable database, I field called "Guarantee Period Revenue" where they enter their revenue information and they select the entrepreneurs name from a drop-down field that connects back to the main database recordThis has been working; however, there are 2 issues that the team faces:When they enter the revenue into the field, they must remember what was previously entered and add the current revenue + new revenue before submitting the form.Entrepreneu

I see there is a log formula but I have some formulas using the natural log and that doesn't appear to be an option ive got even though I do have it in excel. Maybe I'm not looking the in the right place. Im wondering if there is a way that I can express LN() in airtable. Thanks for reading

I used the DATETIME_DIFF({Date Due}, TODAY(), ‘days’) formula to give me the number of days until the due date, however, how do I put an IF statement in if there isn’t a start / due date?

Hi Just wondering if anybody can help with a formula?I'm trying to set up a task (meeting date) that can occur on a Tuesday of say the 1st week, 2nd week, 3rd week or 4th week of the month. So lets say the due day was  Tuesday 02/10/2024 then i'd like to set up a recurring meeting date for the 1st Tuesday of the following month. If the due date was Tuesday 08/10/2024 then it would recur on the 2nd Tuesday the following month and so on.My head is baffled so any help would be greatly appreciated.

Hello,I'm moving into the List View Interface, and I am loving the "Prefix" field which allows me to use visual indicators to quickly see the status of records. My current roadblock is this, I haven't been able to figure out:How can I have a Single Select Field automatically assigned based on a formula?As shown in the screenshots below I have:1) Created a Single Select field that has a "Stoplight Anaylsis" of a record's status as Red, Yellow, or Green.2) I added this Single Select field as the "Prefix" in my List View Interface3) Now I want to know how to automatically assign the status based on other fields. Specifically:GREEN if {Leader} and {Client} are filledYELLOW if {Leader} AND/OR {Client} are emptyRED if {Leader} AND/OR {Client} are empty AND the date is in the Past.It's not a difficult concept, just trying to see how to implement it in Airtable.Quick

I have master blocks of code in my code table.in another table - main table -I wish to get data from a field in the code table by specifying it’s rec ID. I will then combine this code with other data from the main table. I can do this with a linked table but it requires that the user must remember to select a record every time a new record is added in the main table and since there is no default for linked record, I cannot find a way of using linked records.i was hoping to create a formula that did this get(field.codeTable(recID))I could put the actual field data from the record in code table directly into a formula but then every time my code is updated rather than update the code table code I have to remember to do this elsewhere.any help much appreciated, perhaps a script?

I am trying to create a project tracker with a task list and start dates and end dates for each task that are calculated automatically based on a dependent (linked) task and its associated dates. My problem is how to create the first row because no matter what formulas I build for the various date columns, I end up with a circular reference error. I'm expect this is possible in Airtable but I haven't figured out how to do it.I have tried adding a Manual Date column and a Calculated Date Column and then using this function for the start date of each taskIF({Previous Task},{Calculated Start Date},{Manual Start Date})But it still gives me a circular error reference.

Hi,I'm in the unfortunate situation that I seem to have an automation bug that creates unwanted changes to some records, but not all.Since I cannot on my own find out which automation causes this (mailed support regarding this), I wanted to at least identify all instances. The symptom is I end up having two different contact information for the record. Unfortunately there are no identical fields to make an easy IF (!=) between, only parts of the fields are the same (basically a name, with different stuff before and after).In Sheets/Excel this would be a VLOOKUP case, and in the automations there is 'CONTAINS' that could be used. But how could I build a formula that takes one column (in full, I was able to SUBSTITUTE it down to the exact string) and find records where the other column contains the search string?TIA,Björn

New to all of this. No coding background; small business owner. I am trying to create a rudimentary inventory system with Airtable. I can't figure out how to get a formula to "count" the number of array entries in a linked record field, and return back a simple integer value. For example, in the photo attached, the "Reserved" field should return the value of "2," since there are two entries in the linked record field. Any ideas? Thanks. 

I have been reading for hours through the help forum and documentation, but ARRAYSLICE doesn't want to work for me.I want to achieve the following: Show me the first value of an array. My formula is: ARRAYSLICE(verticals_from_contact, 1, 1) but it doesn't show me the first value of the array. Instead, the field shows me the whole array. If I change it to: ARRAYSLICE(verticals_from_contact, 2, 2) nothing is shown anymore.  I tried the formula on lookup fields, linked records, and multi-select fields, and nothing worked (see image). Does anyone have an idea what's wrong?

I have four (4) columns that are number data fields titled {Trip 1 Miles}, {Trip 2 Miles}, {Trip 3 Miles}, and {Trip 4 Miles}. The settings to the number data fields include decimal places and a screenshot of the settings are attached.Column five (5) is titled {Total Miles Traveled} which is a formula data field that SUMS the total of the number data fields listed above. THE PROBLEM TO SOLVE: I am unable to get the formula in {Total Miles Traveled} to include decimal points in the SUM total. A screenshot is attached. The formula is totaling [26.70 + 26.70 + 9.10 + 9.10] and is rounding the answer to [72] instead of the desired answer with decimal points as [71.60].

I working on a formula that will provide me with 4 different outcomes depending on a products Variance from our target cost of product/FOB. Any products that have a variance less than -5% need to output "Red -5% or More Variance", products with a variance between -1% and -4.99% need to output "Orange -1% to -4.99% Variance", products with a variance greater than -0.99% need to output "Green - Meeting or Exceeding Target", and any products that have an empty value need to return an empty value. I have figured out how to return most of these responses, but I'm hung up on how to return the empty value. Any help will be greatly appreciated! IF({P1 FOB % Variance From Target}<= -.05, "Red -5% or More Variance",IF(AND({P1 FOB % Variance From Target}>= -.0499

I've searched on the community for solutions to combining one field that contains a date and another field with a duration to create a datetime field, but haven't come across any solutions to solve it. I'm using the DATEADD() formula but Airtable is not returning a correct results. What am I missing?

Hi everyone!I created a base for employees in our company with the table where are all information about every employee. I also created a form to update those information if necessary. I prefill all the fields and create a button field so that i can quickly open the form. But somehow that button doesn't work with some of the employee. they were added after I created this button. I attached some photos for you so that you can see what I'm talking about.Does anyone know what is the problem here?

Assume you have a total of 100 items and a multi-select field that allows you to select from a set of values. You want to find out what percentage of the total is represented by the selected values.

Hello, I am trying to calculate the difference between two dates, excluding weekends. Datediff. However, weekend is Friday and Saturday at my company. Workday doesn’t help much since it assumes weekends are Saturdays and Sundays. Your help is highly appreciated.

Hi, I'm simply trying to subtract hours in one field from hours in another. I have a column with 'hours recorded' and one with 'hours billed'. They are both formatted as time units (hours). I just want to subtract one from the other in a new field. As an additional question is there a way to set the hour units to just hours, with no minutes, at the moment it's h:mm and I need to enter a 0 for minutes each time Thanks   

Hi all - I'm having a very strange issue with a Formula field. I've got a Status field (single select) that informs a Formula field. i.e. if the Status field says ABC, then make the Formula field say XYZ. It's working perfectly except for two outputs. Now here's where it gets weird: after extensive testing, the issue seems to be with the output string, not the input. If the Status is Booking Received, the Formula field should return "Booking received, waiting for pickup confirmation" but it returns a blank field. I also can't get it to return any results when I change the formula to return my name, the word Test, or gibberish lettering. But when I type one of the other results that is working for another status, it works! Meaning if I type "Decline" in to the formula in place of "Booking received, waiting for pickup" it returns Declined. So weird, right?? Why will it only return certain other existing outputs?

Hello! I want to generate some HTML code using a Lookup function and I've been running into a weird issue. Each HTML button is defined in a table, then is combined like such in a Lookup function:<a href="/water-coaster/192/" style="text-decoration: none;" title="Go to this water coaster"> <span style="background-color: beige; color: black; padding: 10px 20px; border-radius: 50px; font-size: 16px; font-weight: bold;">Montanha Russa Aquática - Thermas dos Laranjais</span></a> , <a href="/water-coaster/281/" style="text-decoration: none;" title="Go to this water coaster"> <span style="background-color: beige; color: black; padding: 10px 20px; border-radius: 50px; font-size: 16px; font-weight: bold;">Acqua Coaster - Thermas Acqualinda</span></a>Unfortunately, there is a pesky comma that Airtable adds in between the two blocks. I tried using a second column with the formula  SUBST