Formulas

Hi, all. I have a table that has a “Planting Date” for a vegetable field. This record is linked to another table that contains the number of growing days (ex: 90 days). I’d like to use a formula that would take the planting date plus the number of growing days (90) and give me an anticipated harvest date. What’s the right formula? By the way, each week has a different “grow days” associated with it so hard coding 90 days into the formula won’t work. Thanks, Paul

Hello AT Community, I am in need to create a view that shows records only if the actual time is earlier than a specific time (7.00 pm) everyday. E.g.:if it is April 19th, 4.30 PM the records should be showed but as soon as It is 7.01 PM, April 19th’s data shouldn’t be showed anymore. As soon as the day changes to the day after (April 20th in our example) the new records should be showed until 7.00 pm and so on. Does it make sense? I have used the formula IS_BEFORE(NOW(),TODAY(‘D MMM YYYY 19:00’)) so I could add a filter to the view based on the formula outcome but I am not getting the correct result even if I am not getting an error. Does anyone have any suggestion, please?

Hey guys, I wanted to export data into csv file but image url was exporting with the name of the file and the URL between brackets… So I did some digging to figure out a way to extract th URL and found this small formula and thought to save you guys some time researching: REGEX_EXTRACT({Image Field Name}, ‘https?://.+[^)]’) Cheers!

Hello All, I have something that is absolutely stopping me in my tracks. Here is the situation: I have a workflow process where we are going to use an Airtable form to add a new row to a sheet - this is a new job for us. That in turn, using Zapier, makes an email that sends to my scheduling software called Service M8. Service M8 (SM8) converts this email into a job, and automatically converts the name of the customer from first name last name to last name, first name. We have to use a set template for the email to be able to be ready by SM8 and therefore be able to convert it to a job with one click. And the tables for name are as the below example: First Name: John Last Name: Smith When a job is automatically made from the email: Smith, John Once the record is in Airtable, and the job is in SM8 we run workflow that constantly updates fields in Airtable. They look for the column we have called “Job Name” as that’s how we track everything. The workflow driven off of the SM8 is all drive

Hey everyone, I have a Deadline column (date) and a “Urgency” column in a sheet. I want any row/item due in less than or equal to two days to be marked in other column as Urgent, and I want anything longer than that to be marked as Not Urgent. I’m guessing there’s a good formula for this, but I’ve poked around for a bit and haven’t been able to find one/do it myself. Any help is much appreciated!!!

Good morning!! Today I’m trying to build a dashboard for my hiring process (for multiple companies) and I’m facing a problem I’ve already had. Currently candidates are in a table ‘Candidates’ with one column for name / companies / status… and hundreds of rows. My ultimate goal would be to summarize all these infos in another one table e.g. Number of candidates in Specific positions per Specific companies. Can’t get over it with the look into another record / rollup formulas and I was hoping you could help me! My guess is that it would eventually look like this: Thank you very much in advance, Best

I’m borrowing a formula from the ‘Ultimate Meal Planner’ by Kamile Parks… it works, except for the issue where Airtable goes by the American format of Sunday being the start of the week - I’m in Aus and go by Monday being the start. IF(DATETIME_FORMAT(Date,‘gg ww’) = DATETIME_FORMAT(TODAY(),‘gg ww’),‘This Week’,IF(IS_BEFORE(Date, TODAY()),‘Elapsed’,‘Upcoming’)) Is there a way to make it count the Sunday as the last day of the week?

I feel like this is an easy solution but I’ve hit a wall. I’m trying to get a %age. In my formula field I have {Actual Hours Worked}/{Max Capacity}. I have set the format to Percent. Somehow I’m still getting #Error! Any suggestions what I’m doing incorrect?

The TRIM function doesn’t seem to work when referencing a Lookup field in a Linked Record. Every time I try to trim the data that’s passed from a lookup in a linked record, my formulas return #Error. Here’s the formula at fault: TRIM({AMID (from link)})&"-"&TRIM({AMID})

Hello, I am trying to create a formula to return the category of my campaign based on the campaign name. For example, if my campaign is “Carlie Bookcase-Manual-Walmart” I want it to return “Bookcases”. I looked into nested IF formulas and FIND formulas and tried to combine them. I got it to work for 1 category using this: IF((FIND(“Desk”,{Campaign Name})),“Desk - Vanity”) But if I try to add more criteria for other categories my formula is invalid. Here is the invalid formula: IF( (FIND(“Desk”,{Campaign Name})), "Desk - Vanity”, IF( (FIND("Dining”,{Campaign Name})), "Dining Tables”, ) )

I’ve been trying to figure out if I can create a formula around two date fields… The situation: Create a field with the possible outcomes as SOLD, PENDING, FIRM, CANCELLED depending on two date fields. SOLD if Today is before Signing Date PENDING if Today is currently equal to or after Signing Date and before Firm Date. FIRM if Today is equal to or after Firm Date CANCELLED (I have no clue how to incorporate this. Basically, I still need a status for deals that have been cancelled, but I don’t know how this would tie in with dates…) Nevermind, I think I might have figured out how to do this… I added an extra checkmark column for the CANCELLED records IF({FIRM DATE FX}=BLANK(),"",IF({CANCELLED?}=1,“CANCELLED”, IF( IS_AFTER({SIGNING DATE copy},TODAY()), “SOLD”, IF( AND({SIGNING DATE copy}<=TODAY(),{FIRM DATE FX}>TODAY()), “PENDING”, IF({FIRM DATE FX}<=TODAY(),“FIRM”,"") ) ) )) Any help would be greatly appreciated. I had this formula working in Excel, but the functionalities o

Hi, can anyone tell me how to compare data in same column on airtable? I don’t know if they are numbered like excel? For example : = IF (AND (A3 = A2, D3 = D2), 1.0) , pls help me :pleading_face:

I’m using Airtable to schedule event times and am trying to tie a few fields together with a formula but am having trouble getting the date to play along. Right now I have a project name field and then multiple events within a project over a few dates/times. So for example, I’d like to have: Project 1 - 4/10 2:00pm Project 1 - 4/10 4:30pm Project 1 - 4/10 7:00pm I have a relevant date/time field, but then when I use a formula to include that field in a name, it formats it in a way I do not want. When I use datetime_format, it’s in the wrong time zone (I think) and I can’t change it in the formula formatting. This is what I’m using: {Project}&" - "&DATETIME_FORMAT({Start Date Time},‘M-DD-YY h-mm’)

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(), then formula result should

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 following into the Milestone Table: {Milestone Nam

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 lookup? Or an array? Happy to hear your thoughts!

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