Formulas

Hello all! I don't know how else to submit a feature request, but the formulas' support of multi-select fields is counterintuitive, so I thought I would share my experience.I want to have a computed field named {Is Executive?} where the value is TRUE if the multi-select field "Role" contains any of "Manager", "Label A&R", "Agent", "Distribution" and some others.There are multiple ways I tried to approach this:OR({Role} = "Manager", {Role} = "Label A&R", {Role} = "Publishing A&R", {Role} = "Agent", {Role} = "Distribution", {Role} = "Music Tech", {Role} = "Executive")Obvious issue: Multi-select is not properly handled (the formula only works if the record has exactly one Role).FIND statements chained with OR (like https://community.airtable.com/t5/formulas/screen-a-multiple-select-field-for-a-certain-value-to-fill/m-p/66522#M5986)Issue: If any options in the multi-select have common substrings, false matches will result.It's really important that we get this right, becau

I'm trying to figure out if there's a way to update a number field annually based on date. I've got a Position Start Date field, a Year of Experience On Entry field which is a number field that reflects the year(s) of experience the person had as of their position start date, and I want to create a field that adds +1 to the number in the Year of Experience On Entry field annually on their Position Start Date, and that field will be called to Current Year of Experience. Is there a way to write a formula that will calculate the Current Year of Experience?POSITION START DATEYEAR OF EXPERIENCE ON ENTRYCURRENT YEAR OF EXPERIENCE1/15/2022016/12/23004/13/201816Thank you for any thoughts you might have on this!

Hi Everyone, I am struggling to develop a formula to determine which position in an array another field is located. Here is my example: The Array of Trans# is 2239, 4539, 4792, 8912, 10394 I am trying to find a formula that says Trans# 4792 is in position 3, Trans# is in position 1, Trans# 8912 is in position 4, etc. I can use FIND to tell me the starting position of the string, but cannot figure out to determine which position Trans# is. Thanks so much in advance.

Have spent the day trying to figure out what I'm doing wrong and I'm totally stumped. Trying to write a formula to populate a status. If a person's start date is today or after, and their end date is today or after OR end date is blank, return "Onboarding." If a person's start date is before today, and their end date is after today, return "Active." If a person's start date and end date are in the past, return "Alumni." Those are working fine. BUTI also want to return "error" when there is something in the start date and the end date is empty. In my table there are two people with a start date, missing an end date, and it's returning "Alumni." Formula pasted below...Help! I'm new to writing formulas and I'm going totally cross-eyed trying to figure this out. IF(    AND(      {Position start date} < TODAY(),      {Position End Date} < TODAY() 

I use airtable for marketing project management of creative requests. I would like to count the timelines that my team is given. I want to count the number of days from the moment a record status is changed to "Ready for design" to said record's due date. I have a DATETIMEDIFF from date created to date due, but that is no longer working for our team. TIA

I’m using the following formula to take a record from one field to update a record in another field. IF({production Pipeline} = "Media Delivery", "Media Coordinator") & IF({production Pipeline} = "Ingest", "Media Coordinator") & IF({production Pipeline} = "Transcripts", {Assignee})Now I’m writing a similar formula but the record has multiple selections and there are too many combinations to use the same formula, so rather than use “=” I would like to use “contain” but I can’t find a way to express contains.I’m trying to get the formula to say  IF({production Pipeline} CONTAINS "Transcripts", Assignee) Is there a work around for thisI tried “!=” and “>=” and “<=” that does not return the desired results

In our scheduling table for projects (table is called "Deliverables"), we have 6 sequential dates that tend to move frequently:Kickoff DateCreative ConnectRound 1 to ClientRound 2 to ClientRound X to ClientDue DateI've been trying to write a formula (fairly unsuccessfully after researching much of the forum) that will look at all six of these dates and return the closest date that is TODAY or later. All 6 dates are in a single table and apply to all records. We're putting them in a column named "Next Due Date"What we currently have:  IF( OR({Kickoff Date}, {Creative Connect}, {Round 1 to Client}, {Round 2 to Client}, {Round X to Client}, {Due Date}), DATETIME_PARSE( MIN( IF({Kickoff Date}, (VALUE(DATETIME_FORMAT({Kickoff Date}, 'YYYYMMDD'))-VALUE(DATETIME_FORMAT(TODAY(), 'YYYYMMDD')))), IF({Creative Connect}, (VALUE(DATETIME_FORMAT({Creative Connect}, 'YYYYMMDD'))-VALUE(DATETIME_FORMAT(TODAY(), 'YYYYMMDD')))), IF({Round 1 to Client}, (VALUE(DATETIME_FORMAT({Round 1 to Client}

Is there a formula I can use to convert a date (6/28/2023) to a text (June 2023)? It'll make it easy for me to group by that month.

Good afternoon! How do I display the number of days in a month in the table, excluding Sunday. There can be as many as four non-working Sundays in a month, as well as five.

Hi all,As a new AirTable user, I am seeking assistance in creating a base to support my father's nonprofit organization in managing data. Currently, I have two relevant tables: 1) "Foundations Records" which contains information on grant agreements and applications with various foundations, and 2) "Foundations" which holds specific details about each foundation (e.g., related records, total funded amount, contact information). I would like to add a field that displays the most recent award granted by each foundation. While attempting this, I may have complicated the process, but I will explain my approach.Firstly, I created a lookup field in the "Foundations" table to gather all records associated with that foundation from the "Foundations Records" table. Additionally, I created a lookup for the year corresponding to each record related to the foundation using the same linked table relationship. My next step was to create an IF formula field that would use the highest numbered year and

Hi, I'm having a little trouble with a project.I have a table called "Report" with the report names, submitted by, email address, and attachments and another table (Contact list) with a contact list with just names and emails.My customers have to fill in a form, and this fill in the main table (report table). My question is, how can I check if the contact that submitted the form (submitted by) is already on the contact list? I would like to do like a checkbox, so I can link an automation to this column and send me an email with the contact information of people that have not been on the contact list.I can't automatically add new people to the contact list, because that table is synchronized to another base.How can I solve this problem? I thought about the function lookup, but I wasn't able to apply it. Thank you very much!

I have this working formula to return 'Reaction Hours'...ROUND(DATETIME_DIFF({Reacted Date & Time},{Request Date & Time},'minutes')/60,2) If the request and reaction times are equal, 0.00 will be returned (correctly).  But then I embed it in an IF statement (simply to prevent #ERROR outputs if 1 of the input fields hasn't been populated yet)... IF(AND({Reacted Date & Time},{Request Date & Time}), ROUND(DATETIME_DIFF({Reacted Date & Time},{Request Date & Time},'minutes')/60,2),"") Now it works fine apart from the situation where a zero is returned, in which case the result is empty/blank.  Both date fields are present, so the first part of the IF statement is triggering, but for some reason the 0.00 value is replaced by empty/blank.  Any ideas why?  

I'm having a hard time cleaning a long text value so that it can be passed to an API as a JSON value via a Post Request.  After running lots of different cleaning formulas, which all yield 'bad character' errors, I think I've narrowed the problem down to some strange behavior re: line breaks - as demonstrated in the table below, where the same formula yields oddly different outputs for different text blocks.  REGEX_REPLACE formula is used to replace ''\n" characters w/ XsThe formula output for record A works as expected.  In Record B only some line breaks appear to have been replaced.   Record C is a duplicate of Record B that has been modified with one character "$." This modification seems to trigger the formula to find the second set of '\n' characters.  In some cases, while testing this, it seems like the list view and detail view yield different results when I copy and paste them into a text editor.  One view yields collapsed text and a

Hi,I have an event list that contains a Start Day column including time (needed in some instances, but often left as 0:00). As I would like a simplified view in some interfaces, I created a formula column to display the date without the time using the formula:DATETIME_FORMAT({Start Day}, 'DD MMM YYYY')However, the formula returns the wrong day, in fact Day -1. I imagine this is due to the 0:00 time being read wrong? Any suggestions to circumvent? Adding a formula to just +1 the day would probably not work for the events in which time is indicated.Thanks! 

hello community What would be the formula for to extract from the rollup field "participant name (from imported table)" the names entered in this field and to report them individually in the formula fields, do you have an idea of the formula to use thank you Franck https://airtable.com/shroD4pRoA52pXcg6

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} & ", "),IF(ARRAYUNIQUE({Advisor Name}, {Advisor Name} & ", "))),", $","") 

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 breaks with suffixes 😞Can anyone help me tweak it to work with suffixes???Thanks so much for any help!

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()) , "active" ) But this does not:IF(AND({Position start date} <= TODAY() , {Position End Date} >= TODAY()) OR(AND({Position start date} <= TO

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.