Formulas

Hi all !To explain my project : I want to make a task manager where tasks can be dependant to each other (for example a task will start when the previous one end, and so if I change the end date of this previous task, the start date of the task will be udpated too)So I created my table like that :The previous task of a task is linked from the "Previous task" field. The "Start Date (Not first step)" is a lookup of the "End date" field from the link to "Previous task".My goal is that the "End date" field have a formula that verify if "Previous Task" is empty or not, if its empty that mean the task dont have previous task and so it calculate the end date from the "start date (first step)" that is entered manually. If "Previous Task" is not empty that mean the task have a previous task and so its start date is the end date of the previous task (and so, t

My table is for students in a tutoring program.  One field is their current grade.  Another is the calculated formula of today's date.  Every August 15th (each year) I want each student's current grade to increase by 1.  Is there a way to do this? 

Hello,I am working on a property management system and there are contracts in two currencies. For all the data to make sense I need to convert the dllr contracts information to pesos with the current exchange rate. In the table where I have inflation data I have a column with the monthly rate when the contract was signed but I need to multiply for current exchange rate. I figured that manually making a column with current rate and coping the latest data to all rows gets the work done. I just want to know if there is a better way to do it?It would be much simpler to have a last() command where a formula finds last record in a table!!!!!Thanks,   

Hi there! This is a very newbie question, but is there a formula for cutting numbers out of a string? For example, if I have the value 01 Lorem ipsum and I want it to read Lorem ipsum what formula would I use?

I will preface with saying I have a hard time with functions. Not my brain thing. So, as much as I have searched for solutions I suspect my search terms weren't correct enough to find the answer. So I am hoping someone here can help.I need my email column in "table 1" to pull the email from "table 2" IF client is "who ever".Ex., in the client column of '"table 1" I enter Sally. I want the email column of 'table 1" to pull Sally's email from "table 2" and add it to the email column.  If it's Dave then pull Dave's email, etc... My client list is growing so more clients will be added. Can this be done without using "multiple select"?I hope I am clear in my explaining. Thanks in advance. 

hello, I'm trying to make a table where employees upload two dates through a form, when they go on vacation and when they return.So far so good, I was able to make a formula that counts the weekdays and these are subtracted from the number of available vacation days (vacations days left) . What I do not know is how to accumulate the days taken in the event if this person fills out the form againMaybe there is a simpler solution to this problem.Thank you

Hi,I'm trying to validate customer's mails, to send them automated mails.I created a column validating another Email column in my relevant table, but the formula shows me that the mail doesn't match the regex, even though I know it's a working mail.the formula is: IF((REGEX_MATCH({Email}, "[A-z0-9.+-_]+@[A-z0-9-]+\\.[A-z]+/g")),"Yes","No") I've basically took it from here:https://support.airtable.com/docs/guide-to-regex-functions  the example is this:besor23@gmail.com Can someone please assist?

Hi all, I figured out an easy workaround, but trying to understand why this is happening.I have three table that matter to this scenario - Employee, Certificate, and EmployeeCertificate (junction table)The Certificate table has a number field {Period of Validity} that records the period of validity of each certificate in months.The EmployeeCertificate table has the following fields:What is happening is that I have a lookup field in my EmployeeCertificate that looks up a number field in my Certificate table. The field it looks up is calledEmployeeCertificate is a junction table. It has one linked record field to my Employee table and another to the Certificate Employee (Linked Record)Certificate (Linked Record)Date of Certification (Date)Period of Validity (Lookup from Certificate table)Expiration Date (formula)The Expiration Date formula is DATEADD({Date of Certification}, {Period of

Hello,I am experimenting with Airtable and Make, but I have a table with a Primary key which is a Formula field.I am trying to create a record in Airtable with this primary key as a formula, but the primary key is populated with NaN value.Isn't supposed Airtable to create this primary key even when I create a new record via Make?edit: I added some screenshot to show that inserting a new record via Airtable API is NOT updating the formula primary key------My second problem is that Upsert from Make in Airtable is not working, it constantly replies with "NOT FOUND", but Upsert is supposed to create the record if not found.Thank youCamillo

Hello team! I am trying to calculate the average weekly attendance in airtable. I downloaded the data from airtable and was able to calculate it in excel using an "averageif" function but I am not sure how I can implement it in airtable. The last column is the average weekly attendance I want to populate in airtable and I was able to do it in excel with this function: =AVERAGEIF(D:D,D2,E:E)The number of days a week we are open varies which is why we need a dynamic denominator to count how many days in a week we are open. Any and all help is greatly appreciated! Thanks so much 🙂

My brain is broken (or my formula is)… We have 4 departments (Production, Creative, Marketing, BD). When a prospective client is in our radar we run their product through a review process to get a sense from a variety of perspectives, the quality of their work. We submit the materials into an interface and the head of each department assigns a member from their team to the review. Once all 4 heads of assigned and marked their department ‘ready’ an automation kicks in and sends out an email… Sometimes there is 1 person assigned from each department… sometimes there are 2 or more… So my sheet has (for each department) 1 field for the user assignment 1 field that looks up the emails (and formats it in the manner expected “graham@email.com, joe@email.com” finally though, I have a formula feild that attempts to combine all those emails into one string of comma

I have read a few discussions on Unique filter - lookup then roll up. But i really can't seem to figure it out. I have my calendar synced - then I have a column of attendees, which is linked to companies. So each row has the different company in the email, but obviously , it can have the same company more than once, given you can have several people from one company. i need to create a column to get the unique values here of this column, but unique filter does not work. Please need your help. 

I'm working with some data coming from another group and they use both text and dates in a fields that I need to pull apart and use the dates to estimate further due dates down the line. I'm trying to avoid the big #ERROR! when the field has text and not a date. Is there a way to pull out just dates into another field (or in the formula I'm already using) so it ignores the text? 

Hello everyone, Pretty new to Airtable but I am enjoying the platform and the features it offers. Currently I have a table with data for when we last saw specific people. The date is being entered as February 13, 2023 for example and I am converting it to a different format for reporting purposes. I am currently using the formula:DATETIME_FORMAT({Date of Entry},"YYYYMMDD") which outputs the date as 20230213 which is exactly what is needed. The problem is that there are other sections where specific people will not have dates entered so it is returning errors in the table "#ERROR!" How do I make the formula only activate when there is a date entered?

Trying to get dateadd formula to work with a linked record - does this work? In one table "Training Types (names as the fixed field called “Class Name”). One of the other fields in this table is a column called “Certification Good For” and it is formatted as an integer and sets the duration (in years) that each type of training is good for (IE crane safety good for 3 years). In another table, I have records of all the training classes we offered and the date they were offered at. The first field is a linked record back to “Training Types” so you can select what the training was, then the instructor that gave the course and the date the course was on. There is then a lookup field that references the “Class Name” and looks back to the “Certification Good For”. I am trying to add “Class Date” + “Certification good for”. “Class date” is a date field with 12hr time format, “Certification Good For” is the lookup field. Next field is “Certification Expiration”, so for crane safety w

Hello.The situation is as follow, I have two multiple select formatted fields in Airtable, namely "Placement(Planned)" where I'd like to insert placements to place my content and "Placemen (Used) are the placements where I already used the content. Please advise if it possible to have in the field "Placement (Stayed)" those places that first two fields are differ from each other with? (I tried formula REGEX_MATCH but it shows similarities between fields while I need differences).Attached a sample how it should look like. Will be grateful for the help!  

I have a long text field where I copy source material. I have seen mentiononed ways to extract URLs with REGEX when they are written out in a text field, but occationaly links are copied in hyperlink form. Is there a way to extract the URL from the hyperlink?Ideally I would need a formula that can extract both hyperlink URLs and written URLs. If there is a way to only extract links from one, specified, domain as well, then that would be the ultimate cherry on top. 

Can I use the switch formula to choose between the values of two fields based on whether a checkbox field is checked? I have 3 relevant fields in this table:{Contract Amount (distinct)} - a simple currency field showing the amount for this distinct record{Contract Amount (aggregate)} - a formula field that sums the first field + the remaining balance of this record's predecessor contract. {carry-forward?} - a checkbox field where Team 1 tells Teams 2 & 3 which of the first two fields they should use for their records.When Team 1 looks up a contract in their interface, they only ever want to see the first field as the "true" contract total, but when Teams 2 & 3 look up the same contract in their own interfaces, they want to see either the distinct or the aggregate depending on whether or not the box is checked.I tried the following switch formula in a 4th field and Airtable acc

Hello!I cannot figure this out.On one table, I have a table that lists the weights of a package on each row, and the cost to ship.For example, a 1# pkg costs $12.00 to ship, and on the next row, a 2# pkg costs $12.90 to ship.On a second table, I want to have a formula that takes an item's weight x qty, and display the cost to ship .So the formula would be[item wt] x [qty] = [total weight] and then display the cost to ship that TOTAL WEIGHT, and then lookup the cost to ship that weight in my first table.so if I had a 1 lb item and I have a qty of 2, the LOOKUP would lookup in my first table that it would cost [$12.90] to ship this 2# order, which I would then use in a formula to calculate the total cost of the entire order (item cost )x(qty)+(ship cost).I cannot figure out how to LOOKUP the SHIP COST ($12.90 in this example) and use it in a formula. Any help very much welcome!Thank you,  Lawre

Hi,Not sure if this is possible...I'm looking to create a formula that checks the condition of a single select cell called {Alt Shop}. IF empty use {Discount Code} if it contains "Shop Name" to use data from a particular cell in a particular row.For example {Alt Shop} contains "GAP", use data from cell named {Discount Code} on row named "GAP".Thank you for your help 🙂  

Hi, does anyone know how to make a formula that shows tomorrow's date?We send out an automated message with instructions to the team for tomorrow, and we want to be able to say "You appointment time for tomorrow, {tomorrow's date}"Thanks!

Hi, Sorry, i'm new. I'm trying Airtable to check if i can use for my project. I have a question, maybe simple, maybe not.I have 3 tables:Match:Team1Team2ScoreTeam1ScoreTeam2SeasonWinner (formulae: IF({Score Team 1} > {Score Team 2}, {Team 1}, {Team 2})) (string) TeamShortname as identifierFullNameSeason StartDateEndDateTeamsI have to create a new table SeasonScoreboard:TeamCountVictoryCountDefeatFor the CountVictory i've tried COUNTA( IF( OR( RECORD_ID() = {Match}, {Team.ShortName} = {Match.Wi

Hi team,I am looking for a solution to create the time slots from the existing date & time field. One of our clients wants a solution to this. They have created the appointments in the Google calendar which is synced with the Airtable. Here the synced record has only a single date and time and they want us to book 3 appointments in this slot by automating the bookings with 30 min intervals of actual time. For example, if an appointment is set for May 22, 2023 2 PM then the 3 bookings will be booked like  May 22, 2023 2 PM, May 22, 2023 2:30 PM  May 22, 2023 3 PM. Does someone have any idea how should we create a formula to create this kind of slot in the Airtable field?

Hello Airtable community!I have created a Jira integration with Airtable. There is a field for Sprint, however, if a ticket was included in more than one sprints, all sprints appears seperated by commas. I want to extract only the sprint that is the highest number. I tried doing this by taking the last one (with the RIGHT() function) but unfortunately the string is not always alpahbetically sorted within the cell. Please see below:Essentialy, I'm trying to find a way to extract the highest sprint. Any ideas how to either sort alphabetically within the cell or how to extract the highest using a formula? Many thanks!

Hi all! I was wondering if it's possible to use a formula to pull the last added item in a list of values in a Lookup field? The items are non-numerical so MAX(values) doesn't work. For context, there is a table for Members and a table for Posts. Posts can be either "topics" or "replies" and are all listed in the same "Posts" table. There can be multiple replies for a single topic and the replies are linked to the topic as a related record. But it's not possible for multiple topics per reply.The Lookup field is on the Posts table and pulls the member's rank from the Members Table (rank is essentially their role e.g. manager, expert etc), but that comes through as an array. I want to know the rank of the last member who replied so that I can use this value in a formula field (I have a separate formula that's applying a prioritisation level based on different factors and member rank is one of the factors).As there