Formulas

Hello communityi am using a regex extract formulato fill formula fields from a lookup field. the formula works well. But I have 5 formula fields and when the character string separated by commas only has 3 elements, the other two fields refer to the last letter of the last word.REGEX_EXTRACT(ARRAYJOIN({Participant name (from Imported table)}),"([^,]+,?){1}")ExLookup containsALLIE Pierre, BELUFFI Maeva, MUD AdrienFormula 1 field 1 =ALLIE PierreFormula field 2 = BELUFFI MaevaFormula field 3 = MUD AdrianFormula field 4 = nFormula field 5 = nhttps://airtable.com/shroD4pRoA52pXcg6Thank you for your feedbackBest regardsFrank

I'm building a formula to roll up a list of people's names only if they are filled. I would like the formula to result in:[Person 1], [person 2], [person 3], and [person 4]Sometimes there is no person 2, 3, or 4 listed. Right now i have the formula listing all of the people and skipping where it is blank. I'd like it to add an "and" between the last two people listed. This is what I have so far: REGEX_REPLACE(CONCATENATE(IF({Staff Name 1}, {Staff Name 1} & ", "),IF({Staff Name 2}, {Staff Name 2} & ", "),IF({Staff Name 3}, {Staff Name 3} & ", "

Hello Airtable world, We’re trying to extract customer’s names from a lookup field of a synced table where the names sometimes include a second name after the comma. Our data usually looks like this: John Smith Jane Doe, Terry Doe James Bond We’d like to be able to have a column that displays the following: John Smith Jane Doe James Bond So far we’ve tried the functions below, both returning and error. IF({Customer's Name}, REGEX_EXTRACT({Customer's Name}, "[^,]*")) LEFT({Customer's Name}, FIND(",", {Customer's Name}) -1) The number of characters is unpredictable both, before and after the comma. I suspect the biggest issue is that our primary field from which we’re hoping to pull information is a lookup field and not just simple text. Is there a solution to achieving this?

I'm new to coding so I'm a bit out of my depth.I have a "Multiple Select" field and I'm trying to create a new field which counts how many selections were made for each record.The items are given a rating depending on how many selections they can tick.I have the below but I'm certain I'm making lots of very basic errors.Any suggestions would be greatly appreciated.{Sustainability Rating} = "n",(("n" = 0)IF({Sustainability Checklist}, "Is the item plastic free?", "n"+1),IF({Sustainability Checklist}, "Is the item made from recycled material?", "n"+1),IF({Sustainability Checklist}, "Is the item fully recyclable?", "n"+1))

Hey guys,I've got a base that tracks books in a small library.  I need to create a field that extracts the last 3 digits of the author's last name and it has become very complicated, haha.Example author names:John BunyanC.S. LewisAlister E. McGrathC. Everett KoopDr. William Lindner Jr.Authors very often have prefixes like Dr. or Rev. and they like including initials and suffixes, too - lol!So how do I build a formula that will pull the last 3 letters of the last name like this:John Bunyan = BUNC.S. Lewis = LEWAlister E. McGrath = MCGC. Everett Koop = KOODr. William Lindner Jr. = LINThis gets me very close:   UPPER(LEFT(REPLACE({Author(s)},1,FIND("~",SUBSTITUTE({Author(s)}," ","~",LEN({Author(s)}) - LEN(SUBSTITUTE({Author(s)}," ","")))),""), 3))  ...but it

Hi allI've been using Glide (www.glideapps.com) to create a front end, and their native DB has a lovely "Relative date" field. ie - post a comment, and that formula can be used to show "Posted just now" or "Posted 2 weeks ago". Before I write a huge Airtable formula so I can do it in my base - does anyone have any suggestions of the best way to do this?  ie If difference between now and {date} is :< 2 mins - return "just now"<59 mins - return "8 mins ago" (or however minutes>59 mins and <1440 - return "3 hours ago">1440 and <10,080 - return "x days ago"etc etcMust be a better way than a huge IF statement?ThanksAndrew

Hello!I automatically retrieve a list of sales directly in Airtable via Zapier, problematic, the price is sent "raw" from zapier with a lot of "0", like that : 16000 for 160So I created a formula to remove them :LEFT(raw_price, LEN(raw_price) - 2) And it works 🙂Now, I am looking to create a new formula, in a new field to get the currency and be able to add the sales.Any idea ? 🙂

I want to write a formula to automatically note "Active" in a column when the position start date is before today and the position end date is greater than today OR when the position start date is before today and the position end date is blank. Here is how I'm trying to write it. The first part of the formula worked- it populates the cells in the column with the word "Active" for those whose position start date is prior to today, and whose end date is greater than today. When I try to add in the OR part- where position start date is less than today, and position end date is blank, I get an error. This works: IF(AND({Position start date} <= TODAY() , {Position End Date} >= TODAY()) 

Hi all, I'm stuck as to what to do here. Hoping someone can help. I THINK a formula should be able to do this? Essentially I want to create a view that shows me only the records that have a zip code that matches a zip code of another record. I was playing around with the DeDupe app, but that merges records, which isn't what I want. I want to keep the records, but I want a view that lets me easily view where the duplicates are. Screenshot attached to help communicate what I mean. Thanks! 

I am attempting to have a field that will either show that a task is Scheduled or display a [Mark as Scheduled] URL Hyperlink.  I have fields for Scheduled (checkbox) and custom URL field.

To explain my project, I have two tables :- A "Movies" table that stores a list of movies, one of the fields in this table is an Attachment field that contains the movie poster image.- A "Catalogs" table that has a link to the "Movies" table.The goal of my project is to create catalogs by selecting the movies I want to include using the link to field, and then, by clicking a button, launch a Make scenario that retrieves the selected movie list and generates a catalog in PDF format using Eledo. The data insertion into Eledo is done in JSON format (example: {"title":"Movie1","description":"Description1","img":"URL1"},{"title":"Movie2","description":"Description2","img":"URL2"}).Before the Airtable changes, this project was easily working because I could simply retrieve the movie image URL using a formula for the "URL" value. However, since the changes, this is no longer possible.I do have a solution, which involves manually replacing the JSON for eac

I have a sort of complicated question about how date formulas treat the concept of a 'week.' This formula also considers the week to be Sunday thru Saturday:DATETIME_FORMAT(Date, 'w') For a record dated Sunday June 18, the value returned here is 25. (The same value as today, Wed June 21.) – However, consider this formula: IF(AND(VALUE(DATETIME_FORMAT({Date},"w"))=VALUE(DATETIME_FORMAT(NOW(),"w")),YEAR(

I have a single select field for "horoscope." Before allowing the user to select a horoscope for a record, I want to check to see if a birthdate field exists and if I can infer the horoscope. How could I make the single select field be conditional while maintaining its functionality if a birthday doesn't exist.

I have data that essentially looks like this:I want to use a formula to populate the last column with the type (in my example, food) based on whether or not other cells in the row are greater than 0. So IF (column A, B or C is >0, return "Fruit") AND IF (Column D>0, return "Fungus" and so on). I can get it to return one of the types but not all. Ideally they would be separated by a comma.I also sometimes have a blank cell and sometimes I have a 0. I know I can do a simple IF statement and have columns for each type, but is there a way to do this in one column?  Thank you for your patience, I am new to airtable and airtable formulas!

I created an automation to log the date/time for every status change in a given project.There are 4 status options:* Todo* In Progress* On Hold (waiting for information from external source)* DoneMy goal is to calculate the hours spent actually doing the project, which would always have "In progress" as the start date. See screenshot below.The tricky part is that the projects always have a back and forth between status: (...In progress -> On Hold -> In progress -> Done...)Would it be possible to get the difference between "In Progress" and the next status?I found this 5 year-old thread that tackles a similar problem, but it's not quite the same thing I'm trying to achieve here.

I have no idea where to start on a formula for this. :stuck_out_tongue: What I would like to accomplish is for Airtable to compare two start times and two end times and tell me which of them matches up. One start/end time would be for an event or concert, and the second would be for employee availability. I want to know if an employee has a start/end time that overlaps an events start/end time. If the two times match I would like Airtable to tell me which employees are available during that event or concert. I have two tables, one table is for the event and its information then other table is for keeping track of employees available time. Table 1: is for events and projects. It has a column that has a start time and end time. Table 2: is for employee availability. It also has a column for start time and end time as well. What formula should I start building out. Should I combine the tables together to make it easier? Any suggestions

Hello!I'm trying to create a field to display the month name. My start column is EU dates like that : 10/6/2023Precision, sometines the number of the month is unique "6, 7, 8" but from october, it switch to a double number "10, 11 or 12".Any idea ? 🙂

Hi guys! Thanks in advance for your support.Right now I have several tables in a database, one of the tables contains the events that occur, and another table contains the types of events that exist (in order to count them). The question is, how can I count only the events that have occurred in a specific month or have been involved in that month? For example, if I want to know the number of events that have occurred in May, but an event started in April and ended in June, it is not going to count if I use a conditional which searchs for event started or ended in May.Do you have any ideas of how to implement it? 

This is driving me nuts. I have a long text field (rich text turned off) that seems to have multiple line breaks. Formula I used was Substitute(Field,“\n”,“”) and while it seems to work it also doesn’t. I tried with putting in a value instead of “” so I could see it working and it is working but also airtable seems to be keeping paragraphs anyway. Any help would be greatly appreciated.

I want a percentage bar where if desktop, tablet and mobile is ticked then it will show full 100% filled percentage bar Here’s my table link: Airtable

Hello dear AirTabler, we are working in Influencer Marketing with AirTable and want to create a form that allows Creators to automatically upload their content.In the AirTable we map the complete campaign handling, so here are already the names of the influencers.The form should be neutral, i.e. no linked field can be created so that the creators can select their name and then upload content.How can we make it so that the creators enter their @handle in the form and the content is then automatically entered into the existing column of their name?

Dear Airtable Hive Mind,I have an Actors table and a Movies table. Each Movies record refers to the Actors table via multiple Link fields, i.e. Movies.Actor1, Movies.Actor2, Movies.Actor3,..., Movies.Actor10 in order to capture the full cast, each of which is a Link to the Actors.Name field.Q1 Each Actor is a member of a particular Acting Association (Actor.ActingAssociation field). How can I pull the Acting Association for each Actor ideally into the Movies table, i.e. Movies.Actor1Assoc, Movies.Actor2Assoc etc. Or do I need a new table, and if so, how to do it there?Q2 How can I gather a distinct (unique) list of Actors into a new Movies.AllActors field that lists each actor noted across that Movies record (10 Cast Member Actor fields as listed above), ideally as objects rather than text, that will automatically update when a Movie.ActorX field is edited. Plus a related column called Movies.AllAssociations showing the distinct list of Associations for the

Hello guys, i have a table where i calculate some values which can also have a Zero.That means that i sometimes get 3/0 which leads to a Nan error.I already tried a couple of formulas but i do not get it. So my question right now is how can i put the cells which would have Nan as a value to 0? I would be happy for a solution. Thank you.

Hello you!I'm looking to remove 2 characters of fields, from the right of the cell. I have a list of prices, like that, no currency :1570197401549I'm looking to get this :157 Є97 Є15 ЄThe deleted numbers are the cents 🙂Cheers! 

HelloI've managed to create a formula that adds x number of days to a start date and pushes the result to a work day if the end date is Saturday or Sunday:IF(AND({Direction start date},{Number of days},"days"),WORKDAY({Direction start date},{Number of days}),"days"),{Number of days})The result seems to work fine, however the formating isn't great as the answer is is like this 2023-06-02T00:00:00.000Z .  I've clicked the format option but that doesn't let me do anything.  Any suggestions would be gratefully received!