Formulas

Hi, I have landline and mobile numbers in my data base. If one of them is missing, i want to show the other, if both are missing, i want to keep it blank, and if both are full i want to concantenate them. This is my formula syntax but its not working. Can you please help correct it? IF( AND( {Mobile Phone 1} = BLANK(), Landline = BLANK() ), “Both Blank”, IF( AND( {Mobile Phone 1} = BLANK(), Landline = NOT(BLANK()) ), “Landline”, IF( AND( {Mobile Phone 1} = NOT(BLANK()), Landline = BLANK() ), “Mobile”, “Both” ) ) )

Hi, Airtable seems to only offer a lookup for a specific string. I am trying to do a lookup based on another cell’s value. Example: Company receives a request for one table. In turn, it needs to make a request for quotation from 3 companies for wood. So you want to a lookup that says "If (Table Request) + IF (Vendors Chosen), Lookup {Contacts: Table Request : Vendor). As in, show me the applicable contacts that relate to this particular vendor and this particular request. Thank you!

Hi Everyone, I’m trying to nest a long string of “IF” statements to populate a field and keep getting errors back (Sorry, there was a problem saving this field). I think it might be because I’m targeting different cells with my “IF” parameters… Can someone help me design the correct formula to handle the following: IF({CELL1}, FALSE(), “INPUT1”), IF ({CELL1}, TRUE(), “INPUT2”), IF ({CELL2}, TRUE(), “INPUT3”) IF ({CELL3}, TRUE(), “INPUT4”) and so on… needing about 8 of these in total. Any help would be greatly appreciated!

Hello, I’m trying to create a formula field that will return a value of “Overdue” or “In Progress” based on the values of 2 other fields: Writer Due Date and Status. Here’s what I’m trying to achieve: IF the Status is any of the following: In Progress, Awaiting Information (Outreach 1), Awaiting Information (Outreach 2), Awaiting Information (Outreach 3), AND the Writer Due Date is BEFORE the provided deadline, then the field returns “In Progress”. IF the Status is any of the following: In Progress, Awaiting Information (Outreach 1), Awaiting Information (Outreach 2), Awaiting Information (Outreach 3), AND the Writer Due Date is AFTER the provided deadline, then the field returns “Overdue”. How would I write this as a formula? Thanks in advance

Hi guys, I’m trying to come up with a formula that will calculate the number of days that has passed and based on that number, change a label in a field. Here are the specifics: I have an airtable for keeping track of job applications that I’ve submitted. I want to automatically update a label field based on the day that I applied. Example: I apply for a job and mark the date (date field), then I manually set a field called Follow up to “recently applied”. Then after 7 days, I manually change that label to “Send 1st follow up”. So how do I create a formula that would basically do this: if(date applied < 7 days){ set the label to “Recently Applied” } if( date applied is between 8 and 16 days){ set the label to “send 1st follow up” } if( date applied is between 17 and 24 days){ set the label to “send 2nd follow up” } else{ set the label to “maybe you should send your application” }; Thanks in

I am looking for a way to extract the first value from a field that has a text result like this £38,331 - £41,216 per year It is to simplify the output of a data extraction of salaries Your help greatly appreciated

how can i use a roll up field to dynamically populate a formula? Is it possible? Thanks to everyone

Hi folks, I have a problem with the Round() function. I have a formula to display two percentages as text; ROUND({Australian Shares % of Portfolio},4)*100&"% vs target “&ROUND({Australian Shares Target},3)*100&”%" The output is fine in about 80% of cases but occasionally I get this; 28.999999999999996% vs target 35% I have tried formatting the original formulas with Round and formatting options but have not been able to stop this happening. Any thoughts on what I could do? Thanks, Jonathan

Hello everyone! I’m trying to migrate from Google Sheets to Airtable, but still having issues creating a budget template for my projects… As you can see in the screenshot below, my “total” and “tax” fields are working fine when I add numbers to the fields “QTY” and “COST”, however, my “SUBTOTAL” formula (also in the screenshot) always stays at zero, regardless if I do or do not add numbers to the fields “freight” or “Discount”. I also would like my subtotal field to calculate the formulas even if “discounts” and “freight” are numbered as zero as I don’t always get a discount or have to pay for freight. Can someone please help me understand what I’m doing wrong and how to solve it? It would be very much appreciated!

Hello, I have a problem I can find a solution for in Excel but can’t seem to figure out in AirTable. Any advice much appreciated! Here’s an example that is analogous (not exactly our case but in the most simple terms I could think of): We have a table of Customers and a table of Products, with the junction being a Sales table with one customer and one product only for each record (customers can buy many products but in the Sales table we create a record for each Customer/Product combination) and also a date of the sale. For each year, I need to report the number of NEW customers per product. So if Customer X bought apples last year, I do not count them again this year (Customer X/Apples). But if the same Customer X didn’t buy oranges last year and did this year, we would count them this year (Customer X/Oranges). (Reporting ALL customers per product per year is easy enough, but capturing just the new ones is eluding me!)

Hi everyone! I’m using the Dateadd formula to add a certain number of days to a starting date to calculate a new date. For some reason, I have to include an additional day (32 instead of 31) to get the result to display ‘correctly’. Is there something I need to change in my starting date?

Hi all, I want to use the DATEADD() formula on only the oldest created value (date) in a Lookup column that stores multiple values (a 1-to-many relationship is needed). I can’t seem to figure out a way to design the base or write a formula that does this. Any ideas? Thanks, Julie

Hi, I need to convert letters to their position in the alphabet. So A becomes 1, B → 2, C → 3 etc… Does Airtable have a function to do that? I’d like to avoid the multiple IF statements :upside_down_face: Thank you

Ok, I’ve scraped the forums and can’t figure this out. I have a catalog of courses, and I’m trying to show the related courses (only within a particular discipline) but also, only at a particular campus. I can create a lookup that shows all of the courses tagged within a specific discipline. I just can’t get it to filter that ALSO by campus. I can lookup all courses by campus, but then not also by discipline. I can only do one or the other. I’ve thought about setting up an automation in another table, or creating campus-specific Views. But I can’t see how I can do this without manual entry. Is there a Formula or Rollup formula to filter the Lookup results by another Linked Record field?

Hello, this is an example of a value in my NAME field: 1290 Name 3 (Some Words) 1255 Another Name 12 (more words) I’m looking for a formula that will get rid of the numbers and space before Name, and anything in parentheses) so would result in: Name Another Name Thanks for any help!

Hello, Is it possible to perform a lookup with multiple criteria on Airtable? Similar to this: Sheetaki – 24 Apr 21

I’m building a base to track my workouts in the gym. I have a table where each record represents a set of an exercise performed. It has fields for the weight used, the number of repetitions, the name of the exercise (dropdown), and the muscle group worked by the exercise (IF formula based off the value in the exercise field). I know from a base design perspective that the exercises and associated muscle groups should probably be in a separate table and then linked to the table for sets. However since I will be adding records during my workouts on my phone, I found that the usability was better when keeping everything on a single table. With a linked record when selecting the value, there would be a lag time, the values would not be alphabetized, and I would have to scroll through. Whereas with a field on the table, there would be no lag time, and the values would display in a single alphabetized block. What I am looking to do is create a table with one record for each

This is our current working formula. IF(AND({Number of Items}=5, {VA Recipient?}),57.39, IF({Number of Items}=5, 55, IF(AND({Number of Items}=7, {VA Recipient?}),73.18, IF({Number of Items}=7, 70, IF(AND({Number of Items}=10, {VA Recipient?}),88.98, IF({Number of Items}=10, 85)))))) We want to specify dates for this. So, if before October 1, 2020, then the above formula is correct. BUT between October 1, 2020 and October 1, 2021, the formula should be: IF(AND({Number of Items}=5, {VA Recipient?}),57.70, IF({Number of Items}=5, 55, IF(AND({Number of Items}=7, {VA Recipient?}),73.60, IF({Number of Items}=7, 70, IF(AND({Number of Items}=10, {VA Recipient?}),89.50, IF({Number of Items}=10, 85)))))) And then finally, starting October 1, 2021, the formula should be: IF(AND({Number of Items}=5, {VA Recipient?}),63, IF({Number of Items}=5, 60, IF(AND({Number of Items}=7, {VA Recipient?}),78.90, IF({Number of Items}=7, 75, IF(AND({Number of Items}=10

Hi all, I’ve been spending around 2 hours trying to understand what I was missing in a simple “if X superior to Y, then show X, otherwise show Y”… Here’s a screencast of what’s happening, can’t believe my eyes :Screencastify I have Y = 11.8 If I change X to 9, the formula says X is higher than 9… Very weird behaviour here as shown above. Any hint on this amazing bug??

I have about 45 (and growing) different Airtable calendars, all with their own unique “Share View” Link. Is it possible to have a lookup or linked record field in a different table that displays all of these links? I feel like there must be a formula for this. I would also ideally want this to update automatically if the links are changed. Thanks!

Hi everyone, I currently have a base where there is a column for DIMENSIONS in the form of manual entry formated as such: 12" x 10" x 8" I would like to SPLIT the numerical values into 3 different columns (column 1 = 12, column 2 = 10, column 3 = 8) From my research I have not been able to find an adequate split formula (I know that airtable, unfortunately, doesn’t have an easy way to SPLIT) How best would I go about splitting this information by a split-like formula?

I want to clean up a base for old, deprecated fields. The problem is, I want to make sure they’re not being referenced in a formula field somewhere. (Or that their Look-Up or Roll-Up copy on another table isn’t in a formula.) Is there an easy way to check dependencies? Or an easy way to search all formulas at once?

Hello ! I am is looking for a simple solution for joining the values of two “Multiselect field” into third “Multiselect field” and keeping the list updated if values in initial fields change. And can you please advise how to manage the list of values of “Multiselect field”, - for example create conditional lists etc. Thanks in advance ! Alex.

I have a table with a status column. I created a Kanban view for these status and I would like to set the current date when a certain status is set. For example when status is STARTED set the started_date column to NOW() So I use the following formula : IF(status = 'STARTED', NOW()) It works to set the date but the issue is when I switch the item to the ENDED status the started_date is cleared. I would like to know if there is a way to set the column value when the condition is true and do nothing when it’s not. Thnaks

Hi there, I am using a formula field to pull the time stamp (and t-zone) from a Date and Time field. I am using this formula currently: DATETIME_FORMAT(SET_TIMEZONE({Day 1 - Start}, ‘America/Los_Angeles’), ‘h:mm a’). This works wonderfully, however, if the time and date field is blank (which, in my case, does happen) my time stamp field shows #ERROR. How can I add to this formula so it knows to leave the field blank if the date and time field is blank? Would appreciate all and any help on this. Daniel