Formulas

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

I'm really struggling with a formula which is the final step of my solution. I need a way to sum up only the top 4 values within a list of rolled up values (there may be between 0 and 5 values in the rolled up values array). Is this possible?The context is I'm calculating winners for a series of 5 rowing races, and we determine a winner by combining their 4 highest point values. My base is structured with the following tables:Races (e.g. "March 19, 2023 Race")Entries (e.g. "Boat #5"). These roll up to the relevant race.Competitors (e.g. "John Smith"). These roll up to the relevant races and entries.For each entry, there is a points value that is calculated based on the final time, boat type, and average age of competitors. Competitors in the same entry (i.e. boat) all get the same number of points for any given race. At the end of the season, we give awards to the individual competitors with the most points for their

Hi everyone,I’m looking to incorporate the tracking of SLA’s into our base to measure performance against timestamps. Currently I have a table of all our SLA’s with descriptions and numeric values for the SLA which will be used in the calculation. We have other tables where we’d be linking the SLA against a record to pull through the numeric value to then compare against a timestamp in a formula.However, we have some records where more than one SLA would apply to them for which we’d have multiple records linked. In this scenario I then get multiple numeric values for different SLA’s all in the same lookup column. The knock on is that I cant now use the numeric value of the SLA in the formula to calculate weather the SLA was met or not.Current Ideas:I could just set up columns for each of the SLA’s that apply to the records in the table I'm pulling them through to and hardcode the values in. However, if the SLA is changed then I’d be keen to avoid goi

Hello I currently use a UTM tagging template that was created by the team at linktosheets. But I would like to be able to transfer it over to AirTable. It should be a relatively simple project for someone who knows how to replicate the formula in Column 6 of this Google Sheet. I haven’t mastered AirTable formulas yet, so I’m looking for help with this. Please let me know if you’re willing / able to help with re-creating this sheet for me. Cheers, Alyssa

Hey all! Not sure what I did, but I'm trying to create a simple field combining the values of 2 other fields.Here is my formula:And here is the results I am getting:And I am trying to get it to retain Friendly date. Where are the extra values coming from, and why won't it keep friendly date?* Second try *My husband (who I have also converted to an AirTable lover) updated to this formula to remove those extra bits, but now it is adding a day to the displayed date? lolThis shouldn't be causing me this much trouble! lol

Hey guys im really dumb in Excel, I need some help. First column is the item, then next one I have records for each, that is the stock. I’m struggling for an hour to find a formula which COUNTS how many rows are there, therefore I have the exact numbers of the rows, which is the stock for each item. Thanks!

Hi! How do I individually concatenate fields if one of the fields is comma-separated? For example, I want to take “red, blue” and concatenate this with “shoe” to come up with ““red shoe”,“blue shoe”” with the quotation marks instead of “red, blue shoe”. Any ideas? Thank you in advance!

Objective Zapier to search Airtable records: Description, Amount, Transaction ID exists, if Not, create a record. Question Can Zapier search across three columns (see screenshot)? Screenshot https://dsh.re/65cc0

Hello,I would like a formula to convert date fild  into quarter and  add display it in full text.Exemple:Dates between 01/01/23 to 31/03/23  will display " first quarter"Dates between 01/04/23 to 31/07/23  will display " second quarter"and so on...Thanks for your precious help. Remy     

Hey folks,I have a table called Tasks and it has linked record called Owning Team I want to filter it based on Team RecordID something like FIND("recZyPUXXXXX", {Owning Team} & '') this does not work for RecordID although when getting the records via API Owning Team values are RecordIDs.

Hi,I am looking to build  a formula to retrieve dates between two dates. The information I am trying to retrieve is instances where a service user has attended sessions.The formula I am using is:IF(AND( DATETIME_FORMAT({Date Field}, 'DDMMYYYY') >= 'DDMMYYYY', DATETIME_FORMAT({Date Field}, 'DDMMYYYY') <= 'DDMMYYYY' ), {Data Field}, "" )The information retrieved is only examples where a service user has attended ONE TIME between those dates. If a service

Hi all,I've ran this code and don't have an error message coming up but when I click ok the final part of the 'If' formula is deleted.I'd like it to run so that all parts of the if formula work (three options): IF({Always On/Miniseries} = "Always On" , {Total Budget (USD)}*((13-{Number (from Start Date)})/12) ,IF({Always On/Miniseries} = “Existing Always-On" ,  {Additional Budget 2023 (USD)}) ,IF

I don't know if this is possible .... if anyone tells me to restructure my data, I'm quitting lol I have Orders, Products, and Warehouses table. Using linked records, I assigned various warehouses to products. And then when orders are placed, it's looking up the warehouse based on the product.  I want to show open open orders per warehouse in a rollup, but it's rolling up the products .... not the count of linked orders. I cannot some come up with how to write this formula .... it feels like it should be simple!  The linked records field is set to only lookup orders that are not yet shipped. This is what I currently have set up as the formula - it's counting the number of products assigned to the warehouse though .... not the number of orders.