Formulas

Dear All, I am quite new user of Airtable and I am trying to create a database for my geology laboratory. In short, we make microscope slides named Thin Sections (hereafter I will call them “TS”) from geology samples (hereafter “samples”). From one sample, one can obtain more than 1 TS at the end of the manufacturing process. However, the user knows since the beginning if he/she wants one, two, or three (and so on) TS from his/her own samples. I have thus created a table with the following fields (see attached image): “Sample name”, “Site name” (the name of the site where the sample has been collected), “total TS” (that refers to the number of TS on aims to obtain), “TS names” (since each TS has its specific name at the end of the making process). In the case more than one TS will be produced, I would fill automatically the “TS name” field as it follows: e.g. → (Sample name)_1, (Sample name)2, (Sample name)

Hi Everyone, I’m trying to figure out a formula and am wondering if anyone here might know the answer: I’m trying to calculate the days between a Start Date and End Date, but if the Start and End Date is the same, I want that to = 1. I’m using the DATETIME_DIFF formula, but when the Start/End is the same, it returns a 0 when I want a 1. Thank you!

I made a short video to explain my challenge: https://vimeo.com/575868703/4099a1186a Basically, I’d like to create a formula, so I get only the coming date within a list of dates. Hope someone can help me with this. Thank you guys!

I have two fields - a rollup field with 3 integer values and a currency field with a single value. I want to divide the currency field by each value in the array to, hopefully, get a new array that includes the output of each calculation. This seems like an obvious problem to do on an array but I cannot find the formula to perform the task. Can someone point me in the right direction? I’m brute forcing this and it sucks.

Hi old Airtable friends, It’s been a while. I’ve been using a formula that’s worked up until now: IF([Book Number - Old}, {Book Number - Old}, “BN” & {Autonumber}) But now I see that when a book record is duplicated, the same old book number is duplicated right along with it and we need each and every book in our inventory to have a unique inventory number. Any ideas? Please and TIA. :slightly_smiling_face:

I want to create a table to calculate my cost of goods, for products that I make. I have a table with the list of products and their RRP. Another table with a list of expenses, ie labour cost, packaging cost, materials cost etc I want to have a third table that takes (product RRP) - (total costs) = profit For example (candle $59) - (wax $2, vessel $5, packaging $3, labour $10) = $39 I’m very new to tables and formulas, so it may be easy and obvious. Any help would be appreciated!

Hello! So I’ve found a formula before for pulling the most recent record added to a Linked Record Field, but I now find myself needing to pull out the FIRST record added. Basically, I have a Market Research base with a Table of Titles. There is a field there called “Seen at” that links to second Table of Events, showing every time we’ve seen that title exhibited. I want to add a rollup or formula that takes the linked record that was the first added to that field and puts it in a new field called “Discoverd at.” Anyone have any ideas on how to do this?

Hello! I’m looking for a way to restrict a lookup to only the record with the max date in a date field. Context: Managing a vehicle fleet Bases: Registration VIN Desired outcome Only display the license plate that is associated with the registration record that has the max “license plate exp date” value. I’m not sure how to go about this. Seems like there may be a way to do so by creating a view in the registration base that only shows the records with the most recent restricting the lookup to that view but can’t figure out the first step there. Any help appreciated.

Hi, I have a rollup field (called “Lessons Included”) that won’t work in the following formula: IF(AND(FIND(“Math”, {Lessons Included}), {Submit Date}=BLANK()), “Math Pending”, IF({Submit Date} = BLANK(), “Math Not Included”, DATETIME_DIFF( {Materials Due}, {Submit Date}, ‘days’))) The FIND doesn’t work, and all fields where Submit Date is blank return Math Not Included, even if Math is part of the rollup field. Any ideas on a workaround for this? UPDATE: If I create a helper lookup column that includes ONLY “Math”, I can get the formula to work. However, with all the lesson types included, that would make my sheet much larger than it currently is.

Hello, I’ve integrated Airtable with Pandadoc. Each time a client signs a quote, the information lands in airtable so that we can prepare the order to be shipped to the client. However, the order information we receive is split in 2 columns : On the left are the product references and on the right the order quantities for each reference. Is there a way to use ROLLUP or FORMULAS in order to have something that is more readable. For example : V-1C * 1 CS-1C-24M * 1 CS-6C-24M *1 Even better would be to be able to display only the references we want. For example, if I want to display only the V-1C orders, I’d have only V-1C * 1 in the resulting field. All we need is to easily understand the order in order to prepare it and ship it to the client. Thanks a lot for your help !

Hello! There is a column (category) with 3 categories (A, B, C) And a column (total) You need to create a formula: If {category} = “A”, {total}*0.5, If {category} = “B”, {total}*0.1, If {category} = “C”, {total}*1) But it doesn’t work for me. thanks

Hi everyone! I don’t think this topic has been covered but if it has, I apologise! I’m trying to create an ‘If’ stated formula that connects two multi-reference fields. The aim is to automate the following: If I select a specific field on one row, I have to add the connecting, multiple fields to that in another column (which are a lot). The logic would read like this: If ‘anxiety’ is selected on the ‘feel statement’ column, then ‘I feel nervous/worried about most of the things I do’ and ‘I feel that something bad is about to happen’ and ‘I feel a lot of uncertainty in my daily life’ are selected on the ‘feel statement_softr’ column. A field on one column (feel statement) is the diagnosis for the statements on another column (feel statement_softr). So for example: Anxiety = I feel nervous/worried about most of the things I do I feel that something bad is about to happen I feel a lot of uncertain

Hello, I have a form response that pulls data in this format (bucket number, bucket weight;) ##, ##.#; ##, ##.# for example, 15, 32.5; 18, 33.4; 6, 35.1 I need to split the data so that I have the second string numbers added up to a total weight. I understand I need to split (in excel) the text at the semi colons, and then extract the last 3 characters to compound. Any ideas how to structure or accomplish this in airtable? thanks so much. Rose

Hello, I am looking for a method/formula to calculate the total time allocated for products (for manufacturing) which have different components with pre-defined times. For instance, A product called “GS60” can be manufactured from one or mix of “RCBO+GPO”, “SA Meter”, “Modem” and so on. I have pre-defined time for each component such as “RCBO+GPO = 1h”, "SA Meter = 1:5h etc. Once I select the components from multiselect field, it should be calculated the total time for each product in “Total Time” field. Thanks

I have a rollup field that is pulling the sum of multiple numbers, and in the next field, I want to use that result in a formula, but am getting an error. Before it was a rollup and I just manually entered the number, the formula field worked fine, so I’m assuming it has something to do with changing it to a rollup. This is my formula, total COG is the result of the rollup field: IF(COG>0,({Profit}/{Total COG})) Can rollups not be used in formulas or do I just need to change something about my wording for it to work?

Hi I need to extract the last record from a rollup field where multiple records exist. Help!

Hi all! I’m looking for a solution in a switch formula, with nested if functions. I want to calculate a percentage & addition based fee that changes based on the category input. Also with possible caveats on how that fee is calculated based on the price of the item. I’m hoping this is possible. Example: If Category=A, multiply by 12% If Category=B, multiply by 9%, then add .50 If Category=C, AND the price is <$15, ADD $2, OR if Category=C, AND the price is >$14.99, multiply by 20% Right now I have a separate table linked to include all this info, but I would love to set up a formula to calculate all of this within the main table. Thanks for the help!

Hi Airtable community ! I’m new to formulas, loving it but struggling a bit as well… I’m trying to automate a change in my records, from a phase of the process to the next, at a specific date using IF/AND. I created a fromnow field called “Calculation” and when it gets to 0 days, I would like to change my Phase en cours to the next phase. It looks like this : IF( AND( {Calculation} = “0 days”, {Phase en cours}= “Envol 2” ), “Accélération 3”, IF( AND( {Calculation} = “0 days”, {Phase en cours}= “Accélération 3” ), "Accélération 4”, IF( AND( {Calculation} = “0 days”, {Phase en cours}= “Accélération 4” ), “Propulsion 5”, IF( AND( {Calculation} = “0 days”, {Phase en cours}= “Propulsion 5” ), “Propulsion 6”, IF( AND( {Calculation} = “0 days”, {Phase en cours}= “Propulsion 6” ), “Coaching terminé”, ) ) ) ) )

Hi, Begginer here with formulas! I imported a lot of data into my database. In a column box, for example, I have up to 10 different data, all separated by semicolons. My goal is to multiselect them, but I was told that I had to substitute the semicolons for something else for it to work. I have tried the basic substitute formula, but clearly I am missing some information or understanding. Thank you very much for your help!

Objective: I want to return a number if a cell has a certain text in it and then multiply that number by another number to return a total value For example: If Content Type Column says TikTok then return number 30. However when I use the Switch function like this: SWITCH(({Select}), “UGC - TikTok”, “30”, “UGC - Review”, “25”, “UGC - How To”, “20” ) It shows a number but isn’t recognised as a number, therefore I am unable to multiply it against another column/variable to measure total cost. E.g. 30 * 5 (no of videos) = $150 [Content type cost] * [No of videos] = #ERROR! How to solve?

Hello guys, I’m new to airtable. Just want to know how to customize workday ? because my office is working from monday to saturday and the WORKDAY formulas does not work for me, so please let me know if anyone have a solution, thanks

Hi Everyone, I have a field called “GL value” that is a number between 0 and about 100. I’d like to have a separate field that does the following: If GL value is equal to or over 20 = :x: High GL If GL value is between 11 and 19 = :orange_circle: Moderate GL If GL value is 10 or less.= :white_check_mark: Low GL Can someone help me write this? This is where I’ve got to: IF(OR({GL Value}>=20), " :x: High GL”), IF(AND({GL Value}>=11, {GL Value}<=19), " :orange_circle: Moderate GL”), IF({GL Value}<=10, " :white_check_mark: Low GL”) Thanks so much!

Dear ones, I need help. I’m using a filter on another system that searches the result based on a certain date. If I inform the filter as follows. Date = “2021-06-25”, The expectation is that I would return all the rows that appear on this date, however filtering this way I have no return. If I use the Date filter >=“2021-06-25”. it works, but it brings me back not only the lines corresponding to that date but all the other dates ahead.

Hello yall! Got a quick quesiton here! My goal is to connect airtable with hubspot through zapier but when there import of info occurs there is a weird inflow of info due to internal problems between connecting the two that spits out code instead of text. My goal is to translate such code as lets say “100011010” into blue for example where each value of x that gets imported automatically has another column that can spit out y values. so similar to vlook in excel. Please let me know how it can be done

Hello everyone, Tinkering with my garden automations and I have seasons on my mind. There’s a couple of items I’m working on and thinking about, and figured I may as well post my questions here to help advance my Airtable skills. Firstly, spending time with a Switch statement, is it possible to stack cases? For example; SWITCH(Month{Date} ,1,"Summer" ,2,"Autumn" ,3,'Autumn' ,4,'Autumn' ,5,'Winter' ,6,'Winter' ,7,'Winter' ,8,'Spring' ,9,'Spring' ,10,'Spring' ,11,'Summer' ,12,'Summer') SWITCH(Month{Date} ,[1,2,12],"Summer" ,[3,4,5],"Autumn" ,[6,7,8],'Winter' ,[9,10,11],'Spring') And what are peoples thoughts on having rolling seasons within a recurring calendar? Not too sure about this one though, trying to map it out still, so perhaps it’s more of a base design question. :thinking: DATETIME_PARSE(DATETIME_FORMAT({Recurring Date},'MM/DD')&"/