Formulas

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.

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 each movie in the Make scenario and setti

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({Date})=YEAR(NOW())),"Y", "N") This formula reports whether a given record's date falls within the current week. So, a record dated Sunday June 18 would get a "N" if the week is Sun thru Sat. But! A "Y" is returned, because this formula is considering a week as Mon thru Sun. Does anybody know why this inconsistency would occur? Thank you for your help!

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.https://community.airtable.com/t5/other-questions/subtracting-last-two-created-linked-record/td-p/51592