Formulas

Hi! First time poster here. I have a ‘record creation’ trigger via Zapier that takes certain emails from gmail and enters key information into an Airtable. as a part of the migration I am collecting the names and emails of those cc’d on the email. there can be upwards of 8 or so individuals in the cc field which means i’ll need a unique column (or row) created for each email (will plan to do the same for the contact’s name as well). Haven’t been able to figure out the formula to make this possible but really just need something to create a new value for each comma separated value included in the cell. Thanks in advance for the help here!

Hello! I’m looking for a few hours to get the duration of a video file in an attachment field. Is it possible? Thank you! Quentin

hi all! Trying to create a logical formula but having some trouble: If Title is empty AND Copy Request is empty = “no copy needed” If Title is not empty AND Copy Request is not empty = “copy provided” If Title is not empty BUT Copy Request is empty = “copy provided” If Title is empty BUT Copy Request is not empty - “copy requested” Hoping this is possible but getting really stuck creating a formula past a simple AND formula :upside_down_face: Thank you in advance!

Hi! There is probably a very simple solution to this, but I’m trying to create a field that shows information from EITHER of two other fields - whichever one happens to be populated. My issue is that they are both calculated number fields, so I’m getting zeros instead of blanks. Example: of Jars (SOUP A) = 3 of Jars (SOUP B) = 0 Ideally Field C should just be “3” but instead I end up getting “30” because I don’t know the proper “IF” formula to wrap into it. Here is my current formula: CONCATENATE({# of Jars (A)},{# of Jars (B)}) Any assistance would be much appreciated! Thanks!

I have a field containing scores with each record a day. I’d like to have another field showing the average (or the total) of the last 7 days’ scores. I cannot find a way to do this having searched for hours. Thanks for your help

Hi, So I have a field called {First Name} and {Last Name}. I have a formula field called {Full Name}, which is simply the result of {First Name}&" "&{Last Name}. Pretty simple. However, there may be cases when the {Full Name} results in blank. For e.g. when I create a new row in the grid, but no First and Last Name have been entered yet. I assumed that the {Full Name} is equal to BLANK() in this case. I have a primary field {ID}, which I’m using as an ID with the intent that if the {Full Name} is not blank, then evaluate to Autonumber&" "&{Full Name}, so something like “101 John Smith”, “102 Rebecca Jones”, etc. However, I want ID to be a formula such that if the {Full Name} is blank, it evaluates to something like “103 No Name”, instead of just “103”, i.e. I want to see the ID that has no full name explicitly, so I thought a simple IF() formula for the ID field would work. I tried to write an IF() formula such that if {Full Name} is BLANK

Hey Community! I’m looking to create a formula to count the number of days since a specific date in another field. Image below. Please Advise.

Hi, I’m trying to formulate so I can split the text in one column (separated by a :winking_face: into multiple. Please can someone help!

Hi , I am trying to extract particular Strings followed by word. for e.g Date Wed, 31 Mar 2021 11:45:50 PDT Build Version 1.1.22User ID ABC Issue Reported* this is for testing Actions Tried -------------Networking----- I would need to Extract values followed by Date , USER ID and Issue Reported Fields. Length of User id value or the Issue reported may differ so I would like to take whole value followed by these keywords. MID will give me the value, starting value will be same always but as I dont know the end of String so dont know how to proceed with that. Any help on the same would be Highly Appreciated Note : Values might not be on the same line as shown above but there can be spaces between the field and Value

Hi. I’m a new Airtable user and this is my first post. I apologize if this repeats a solution provided elsewhere, but after a lot of searching I can’t find quite the advice I’m seeking. My Base: I have several projects. Each project has one or more milestones. Each milestone is associated with only one project. In a Projects table I have a field {Project Name} and other fields for entering info about the project not relevant to this post. In a Milestones table I have the fields {Milestone Name}, {Milestone Deadline}, and {Project Name} So the tables are linked through the {Project Name} field. My objective: On the Project table I’m trying add a column that automatically shows the name of the next milestone for each project. (I’d like to allow for the possibility that for any project the next day on while a milestone occurs might be a day when actually multiple milestones are scheduled for that project). Illustration: So, if I enter the fol

Hi there! I am trying to create a formula to do a lookup if one of a certain value is inside a job title. So for example the values are: VP, CEO, CIO, CTO, CFO, EVP, Director, Manager etc. If in the field {Job Title} one of these titles is in the name, I want to return the field "Interesting. Now I made this: IF(FIND(“IT”, {Job Title}), “Interesting”, “”) & IF(FIND(“Innovation”, {Job Title}), “Interesting”, “”) & IF(FIND(“AI”, {Job Title}), “Interesting”, “”) & IF(FIND(“digital”, {Job Title}), “Interesting”, “”) & IF(FIND(“data”, {Job Title}), “Interesting”, “”) & IF(FIND(“transformation”, {Job Title}), “Interesting”, “”) & IF(FIND(“information”, {Job Title}), “Interesting”, “”) & IF(FIND(“CIO”, {Job Title}), “Interesting”, “”) But this returns: InterestingInterestingInteresting for a few records. Any idea what would be the best formula for this? We can also make it a

I have a table with a multiselect field for stages: Submitted, Reviewed, Approved, Completed. I would like to track the date of each stage change in separate fields. So Date Submitted, Date Reviewed, Date Approved, Date Completed. I am using this formula to populate those fields… IF({Stage}=“Reviewed”, LAST_MODIFIED_TIME({Stage})) The problem I am now running into is keeping those date entries even as the stages changes. So when the multiselect is changed from Reviewed to Approved, I would like both the Reviewed Dates and Approved Dates to remain. With the above formula, only the date of the current stage remains. Please help! Thank you!\ -Vicki

Hello, I am trying to substitute all the occurrences of ", " (comma, space) in a Lookup field with “%20” and place that into the formula field.

Hello everyone ! I have to calculate the cost of a product. It can be made of Tissu, Wool or both. The problem comes from the “total” columns with a Formula field. When I have some empty fields, the “total” field shows “NaN” because it divides by 0. I can’t write the formula to avoid this. Does anyone have a solution? Thank you in advance, My formula : IF({Prix Tissu}=0, {Prix Tissu}/{Nb par tissu}=0){Prix Tissu}/{Nb par tissu}

Hey all completely new to Airtables, merely hours in! I was wondering how do I move on to the next cell if the current cell has a value? I’m trying to calculate deadlines given an estimated number of days a project will take from a production date, however occassionally I’ll have a predetermined deadline but no start date so want the value to remain and move onto the next row. My formula is: IF({Build Start}, DATEADD({Build Start}, {Build Days}, ‘days’)) This works but if a value already exists it replaces it with a blank space? Ideally I’d just use an else and continue.

Hi, I am experiencing unexpected behavior. Is this expected behavior that MIN function in formulas doesn’t work with lookup fields that contain numbers (in the same format)? Would be great if I someone can check & maybe I am getting this wrong? Thanks in advance

Trying to create several calcs for implementation dates. Need an example of what will give me days for Connectivity that show Project kickoff date -30 so for example if Kickoff happened 60 days ago, Feb 2, then Project Kickoff Date (Feb 2) minus 30 would equal 30. I need a whole number as an output. I know the excel Days formula gave me that but I’m not sure how to convert that into an Airtable formula that works. Date Diff does not seem to work either. So I need something like formula = (whatever function (Project Kickoff Date – 30)) = X Days

Hi there, I would like a formula whereby if a particular field (Subscription ID) is empty then the formula returns “Y”, otherwise it doesn’t return anything (formula is left blank). How would I go about this? Thanks, Blake

Hi, I am creating an Airtable form with a drop-down option. Now the options in the dropdown are the entries of a different table. It looks like this: When I open the form, it shows the IDs, not the names of the Hotels: Is there a way to control which fields of the other table are being displayed (i.e. not just the IDs) ? Thanks, Christoph

Hello All! I am attempting to get the Record ID of a different Record. I’m using a basic numbered column and then a formula that just adds 1 to that numbers field to get its next logical Record (Successor). I’ve used Record_ID() to get the id of the parent record. Does a way exist to lookup the id of another record based on that successor?

Hello, I have seen in this video My Workflow with Merch Wizard and Orbitkit Integration - YouTube 2:20 to 2:47 you have some functions you can make with air table, but I haven’t been able to figure that out. Basically, I want to create a parent child function. And if there Is a parent, I want all the keywords to sum up in an extra column. At 3:48 you can then select one parent or child and it will pull the keywords from one table to another. Way above my head. I appreciate all help.

Hi, I’m trying to get familiar with the SWITCH formula by taking a shot at using it instead of a nested IF but can’t seem to figure out the following. I have a field called “Expiry Date” (type Date) and another field called “Expired?”, which is a formula field where I’m trying to use the SWITCH formula. My goal is that the “Expired?” field formula should output “Expired” if the “Expiry Date” is lesser than TODAY(); it should output “Expires today” if the “Expiry Date” equal to TODAY(); and should output “Still Active” if the "Expiry Date is greater than TODAY(). The formula I’m using is SWITCH({Expiry Date},<TODAY(),"Expired",=TODAY(),"Expires Today", >TODAY()"Still Active","N/A") I’m definitely doing something wrong because I’m getting a formula error. Can somebody please help me with this. Thanks, Sean

Hello, I have a trouble to get mp4 URL’s working, when they are generated by Formula for Attachment field. Situation: Attachment {Attachment} field has some mp4 files in it. Formula field refers into Attachemnt field. {Attachment} Result is an URL for mp4 file. Clicking the URL opens a new window with video file, but video file won’t get started. However, Video file works, when clicked directly in Attachment field, but not via Formula. I need to open it via formula. Is there a work around or way to fix it ? Windows 10, Browser chrome. thank you, Lassi

Hi all, I’m looking to display the result of a formula as a read-only check box. The result of the formula is always a 0 or 1. Here is the formula: IF(AND(OR({Meal Plan Shopping List Recipe}, {Non Meal Plan Shopping List}), NOT({In Pantry})), 1, 0) Adding a screenshot for reference. I’d love it if the 1/0 column could show a checkbox if 1 and nothing if 0, like the other columns. Thanks in advance!

Hello, Airtable newbie here. I have been trying to work out this formula for hours now and I can’t figure out what is wrong. I want to create a custom SKU based on when it was set up. This is my formula that is erroring: “EY” & RIGHT( {LIST DATE}, 1) & ROUNDUP( MONTH({LIST DATE}) / 3, 0) & ”-“ & {SKU IDENTIFIER} The SKU should start with ‘EY’ and then a two digit number where the first digit is the year (1 for 2021, 2 for 2022, etc.) and the second digit is the quarter. I have plugged each subsection in separately and each work by themselves but when I string them with & it errors out. I have tried both Concatenate and &. I’m sure it’s something very simple I"m doing wrong but I’ve read the documentation many times over and can’t figure it out.