Formulas

Hi. Im brand new to AT. Im pretty good with Excel formulas but need to learn AT. I need a formula in “Prep Start” that will duplicate “Job Start” without the time. Thank you!

Hi team! I have a table A with a column called “Offer Amount”. I want to create a table B which will have a cell containing the max(tableA’s OfferAmount), that gets updated each time a new record is created in table A. Is this possible on airtable? The background is that I want to send the current highest Offer Amount (out of all the records on table A) to a webpage on builder.io via zapier (hopefully) each time a new record is submitted to table A. The records are inserted into Airtable table A from a form on builder.io via zapier.

I have a list of tasks in my table and a date range for each task. The date ranges are entered into two different columns, Start Date and End Date. Each of those are formatted as dates. I’d like another column to list all the months that are included in that ranges of dates. So for example, if Start Date is Jan 1, 2020 and End Date is April 30, 2020, I’d like the Months column to list Jan 20, Feb 20, March 20, April 20. Is there a formula for this?

Hi there, I need to calculate the difference between the {Date} and today in days, and display “todo” or nothing if the number of days is >1. I tried this but I keep having errors : IF(DATETIME_DIFF({Date}, TODAY(), ‘days’)>1),“To do”, " ") Any ideas?

I’m trying to workout a formula to identify a text field that starts with “The”, or “A”, and move the “The” or “A” to the end of the text string with a comma. So if the field said The Blue Book, I want it to say Blue Book, The Or at the very least I want to delete “The” or “A” from the start of the text string, but not from Elsewhere in the string. Can anyone point me in the right direction?

Hello- I am looking to extract a specific file saved art image as “FINAL” when we have 10 plus revisions in one field/cell. Meaning the designers have revisions of art file attachments and I want to create a button based on the file type URL that only contains the word FINAL in the saved attachment. RIGHT(LEFT({ART FILES}, LEN({ART FILES}) - 1), LEN(LEFT({ART FILES}, LEN({ART FILES}) - 1)) - SEARCH(“https://”, {ART FILES}) + 1) How do I have it only find the word FINAL and create only the button URL link to that file only. Thanks! Scott

Hello! I have an automation that populates numbers separated by commas in the same entry. Example: 12, 12, 12, 8 I want to create a field that will tell me the sum of these numbers, essentially replacing the commas with +. When I do this using the SUM function, it returns 0 likely because of the commas. Is there an easy way of doing this?

Basically, if a field has an extra space at the beginning or end from human error, I want to automatically remove it. Also, is there a way to flag a field that has extra spaces? Some kind of data validation when adding to the field?

Hi there, I am creating a participation tracker that I can use for an upcoming project. The concept: There are 10 different groups, and each group can have 3-10 members. One “leadership” group contains at least one member from each of the other groups, so theoretically each member can belong to up to 2 groups. I have used a many-to-many relationship to link members to groups. Here is what I have: A table that lists all members with their group(s)(& other pertinent info) A table that lists all groups with their members (& other pertinent info) A table that lists all meetings with the members that attended and their group(s) as a lookup field A table that lists all emails and phone calls with the member they’re with and their group(s) as a lookup field What I need: identify how many times a group has participated in some wa

Hey there i am pretty new to AirTable and its formulas - and I am not a programming genius. Is have a long list of people, with all kind of data. This includes the date of birth. I am trying to generate a daily number of people who are above or below 12 years of age. I played around with sorting lists a bit and managed to do 2 views - one with adults (born 4380 days ago or before that) and minors (born within the last 4380 days). Is there a more clever way? I would actually prefer to do a new table telling me how many of which I got in my list. Any help would be great. Thx.

Hello! I am trying to set up a timesheet in Airtable and I want a formula or automation to assign a new pay period every two weeks. I don’t care if it is just a number (pay period= 1,2,3,4…etc.) or a single select field or something else! I just need to indicate a change in the default entry every two weeks. My first thought was to set up an automation and set the trigger as a scheduled time (every two weeks). But, I keep hitting a roadblock when trying to set up the action. I do not want to FIND, UPDATE, or even CREATE records… I just want the default field entry for new records to change every two weeks. In my mind, the sentence would be “Every two weeks, add 1 to the pay period number.” Any thoughts on how to get this done?? Thanks to anyone in advance!

I have a field called: Branch Name I need to extract text from it using a formula field. I’m just not sure how. Examples of data in the field: #2828 - Clarksville Branch - Clarksville, TN #4109 - Aiken Branch - Aiken, SC What I need to extract: Clarksville Branch Aiken Branch I tried this formula, but it doesn’t work (returns #ERROR!) and I don’t know enough about REGEX to troubleshoot: REGEX_MATCH({Branch Name}, '/(?<=-).*(?=-)/g')

Hello people, I’m really new to all this, so this might be a stupid question. I want to retrieve AirTable data from my table, filtered by two variables (problem, severity). Based on these two values, the code should give back individual help offerings. But it seems that I can only filter by one variable using “filterbyformula”. Using two it gives back that the request is invalid. Is my syntax just wrong or is it even possible to filter by two variables? data = { "filterByFormula": [f"problem = '{mood}'", f"severity = '{severity}'"], "fields": ["help"] } thank you so much!

Hello Airtable Team, I have made conditional formula with emoji icons. But I have got one issue in the old OS/Browser. some old OS in the chrome/edge old browser icon color is not appearing. Can you please provide a list of supported browsers lists and OS? OR Any alternative solution for that? Thanks

I would like the formula to be able to identify the client by field {Rodzaj usługi}. If the client would be (in the {Rodzaj usługi}) “Fotografia ślubna” then I would like {Status} to show “ :heavy_check_mark: ” only if the {Odbitki}, {Album}, {Galeria internetowa}, {Selekcja zdjęć}, {Zwiastun}, {Obróbka zdjęć} fields will be filled with the word “Oddane”. Otherwise, the {Status} field should show “ :x: ". When the client would be (in the {Rodzaj usługi}) “Film ślubny” then I would like {Status} to show “ :heavy_check_mark: ” only if the {Film wrzucony na dysk}, {Teledysk ślubny}, fields will be filled with the word “Oddane”. Otherwise, the {Status} field should show “ :x: ”. This formula works for two {Rodzaj usługi}. I would like to add one more “Service Type” on the same terms as the previous ones. Namely, “Ślub cywilny” If the client would be (in the {Rodzaj usługi}) “Ślub cywilny” then I would like {Status} to show “ :heavy_check_mark: ” only if the {Galeria

Hi I’m creating a tournament ranking system. I have a table for each tournament played and within each table, I list all the players and their individual scores and then their ranking position and ranking points for that tournament. On a separate “rankings” table, I list all the players and lookup their score, ranking points from each of the tournament table. I can then add the total ranking points across all tournaments and sort them by male/female/adult/senior categories. However, the end of season rankings after 8 tournaments will be based on a player’s best 5 results/points scores out of the eight events. Therefore, I need to take the 5 largest (not necessarily unique) points from the 8 fields. I’ve looked at various formulas to try and find the answer (similar to LARGE function in Excel), but I’m stumped. Does anyone know how to do this? Is the answer to structure my tables differently? I feel database design is always my downfall :frowning:

Is there an easy way to ‘calculate’ or combine Tags and Score, so I get row 1: “A7, B6, C5, D4, E3, F2, G1” and row 2: “C7, G6, F5, A4 E3, D2, B1”

Hey All, I’m struggling to build a formula in airtable for the following. I have a list of free text data in my column Source, where customers have put in on where they have found us, ranging from ‘Search engine’ to ‘Google’, 'Bing, ‘My friend James’, ‘A friend’, etc… I want to create a column that groups several of these free text entries together, so for example for ‘Search engine’ and ‘Google’ we return ‘search engine’ for all entries that contain the text ‘search’ or ‘google’ in column Marketing channel. (I know I won’t be able to capture all, but I can continue to build on this formula to include all) I tried a Switch formula, but that requires the text to be exact, and not ‘contains’. I tried an If Find formula, but i’m struggling to get the format right and I get a lot of errors. Could anyone help?

I’m creating a vehicle fuel log. I’d like to calculate the miles driven between fill ups (trip). After that, then I will be able to easily calculate the miles-per-gallon (MPG). I know I could easily accomplish this by adding a column for “start odometer” and “end odometer” and then simply subtract the two numbers. But that is extra work each time I create a new entry. I’d rather just add the odometer reading at the time of the fill up and have it auto-calc based on the previous (above) entry’s odometer field. I know that since Airtable is not just a spreadsheet you can’t do what you’d do in Excel with something like “=sum(b2-b1)”. Also since the sort order could change, etc. So I was thinking, what if I setup an auto-increment ID, that way I can reference to the previous ID’s odometer field. Since the previous ID would be one less than the current, that would work, unless I delete an entry. So that may not be the best option. Is this possible or am I just stuck

Hello everybody, I have been using a formula to generate invoice numbers, but is has been producing some overly similar looking numbers which confuses clients. Current formula is: “INV” & DATETIME_FORMAT(CREATED_TIME(),‘msw’) What I’d like to change it to is: “INV” & DATETIME_FORMAT(CREATED_TIME(),‘wms’) The only problem is that if I change it now, all of my previously issued invoice numbers change too. Is there a way to perform the below? If date is on or before 18.04.2022 then “INV” & DATETIME_FORMAT(CREATED_TIME(),‘msw’) else “INV” & DATETIME_FORMAT(CREATED_TIME(),‘wms’) Your advice would be very helpful! Thank you.

Getting error while adding formula field as “Formula fields should be configured in the field menu dropdown”. Please find attached video link for the same - drive.google.com formula-field-error.webm Google Drive file.

I have a multiple Select field that takes entries from a form. Often times it comes in with one of the options being empty or null in value. See Screen shot. I’m trying to create a formula that removes this empty Value and the “,” for it. I’ve used all of the ARRAY formulas and they aren’t working. Example: Multiple select Has: ,17,18,19 I want the Formula Field to be: 17,18,19 (removing the first null value and the one between 18/19) Screen shot shows both fields and that the empty field remains.

Hi There!!! I am trying to figure out how to solve an issue that it doesnt seem very difficult but i am not able to. I organize groups to go to the theatre. At the moment someone fills up the form to attend an event, I would like to check if there is still tickets available and if not, send him / her to the wiating list. For that i woukd need the summary of tickets sold at that moment and this would be te trigger of an automation. Is that possible? Just want to have a field called tickets sold that summarizes the number of tickets sold until the moment of a new record is created.

Hi All, I’m parsing JSON files into Airtable Long Text fields, and then setting up Formula Fields to relay the contents of that JSON. For example, the JSON data looks similar to this; {"NAME":"Colour","VAL":"Red","ID":"1"}, {"NAME":"Shape","VAL":"Square","ID":"2"}, {"NAME":"Size","VAL":"500","ID":"3"}, {"NAME":"Scale","VAL":"2","ID":"4"} At the moment, I’m using a fairly hacky method to display the data in a formula field, for example; ABS(SUBSTITUTE( RIGHT(MID({Log Content},FIND("Size", {Log Content}),17),4) , '"','')) But this is buggy as. Obviously if Size is more or less than 3 digits, the output fails or works simply by chance. What would be the correct way to write a formula that retrieves the entire value between the inverted commas, regardless of the length?

I apologize ahead of time if I’ve made this way too hard; I’m a newb. I tried to search for this question already being asked, but I don’t think my search terms are right. Feel free to put this under another topic if it fits there. I’m trying to create a database that automatically ranks the nutritional quality of commercial pet food. I have 4 columns indicating the nutrient levels in each product (protein, carb, fat, & phos), with a number hand-entered as a percentage of calories (doesn’t need to display the percent sign). I created 4 IF function columns to categorize various number ranges into “Good”, “Bad”, and “Borderline”. So now each row contains 4 values in any combination. (eg: Good, Bad, Good, Borderline) So here’s where I’m lost; now I’d like to evaluate something like the following: If there are 3-4 “Good” returns, and 0-1 “Borderline” returns, I want it to display “EXCELLENT” 3 “Good” & 1 “Bad” = “GOOD” 2 “Good” & 2 “Bad” = “FAIR