Formulas

I am attempting to calculate asset depreciation using Airtable. I am calculating using the following Fields: Purchase Date: Date field Unrecovered Basis 1: A formula which is returning a currency value 1 Year (from Depreciation Conventions): This is a lookup to another table which has the 1 year depreciation amount. This is a numerical value (decimal) I have reduced the formula I am attempting to use down to it’s simplest form. There will be more switch states, but it doesn’t even work with the 1, so I didn’t want to confuse matters by adding more. The formula returns a result just fine, but I am unable to format it as a number (currency) and thus, am unable to use the value in future formulas. The Formula is this: SWITCH(DATETIME_FORMAT({Purchase Date}, ‘YYYY’) - 2015,1,{Unrecovered Basis 1}*{1 Year (from Depreciation Conventions)},BLANK()) When attempting to format the field, it state

I have a field that is bringing in names using the web clipper block. The problem I am having is the names from the site come in with additional header, and extra space between them. Is there a way to clean this up in a formula or in the web clipper on import? Here is the example: Clients: Bob Jenkins Sarah Hunter I need it to look like this: Bob Hunter, Sarah Hunter Thanks as always!

I have a table with 200 records and two fields: cost and sold. Im trying to link to another table using the link to records to calculate the average sold price but I have not managed to make it work. Does it have to bring all the new records in the second table? ITs like i have to select 1-by-1 for all of them Thanks

Hi, Airtable newbie here. We are trying to setup a table that calculates farm type based on size of farm. The result we are looking for is this: Acres…Farm Type Empty…Unknown 0.0…Landless 0.1-5.0…Small 5.1-20.0…Medium 20.1+…Large Trying to use this formula: IF(Acres = "", "Unknown", IF(Acres >= 20.1, "Large", IF(Acres >= 5.1, "Medium", IF(Acres >= 0.1, "Small", IF(Acres >= 0.0, "Landless", "Unknown" ))))) This is causing a conflict between Unknown and Landless. Any idea why and how to fix this? Please help. Thanks in advance.

I am exporting orders from Woocommerce using Zapier into Airtable. The Order from the customer is received into two separate cells like this; [Apple,Orange,Pear] and [1,3,5] I need Airtable to recognise that order is for; 1 x Apple 3 x Orange 5 x Pear I also need to know how many I require for a certain City. For example if I receive the data like this; [London][Apple,Orange,Pear][1,3,5] [Manchester][Orange,Banana][2,5] [Manchester][Apple][10] [Birmingham][Orange,Pear][3,4] [London][Pear,Orange][3,7] I need to know; London - Apple x 1 Orange x 10 Pear x 8 Manchester - Orange x 2 Banana x 5 Apple x 10 Birmingham - Orange x 3 Pear x 4 - Total - Apple x 11 Orange x 15 Pear x 12 Banana x 5 Please note that I will not be able to input the list of Cities and it would be Difficult to list all o

Hi, I have a table with several categories and they all have a month assigned to them (essentially types of activities being undertaken in that month). I want to create a view that shows one category across specific months. For example “Real Estate” in March, April and May. I can’t do it with filters, because I would need to combine AND OR filters. I saw a post about combining formulas: https://support.airtable.com/hc/en-us/articles/221561507-Combining-AND-and-OR-filters but can’t seem to write the correct formula for this. I would really appreciate any help! Thank you so much!

Can I create a formula for calculating Interest like the one I use in Excel: UPB*{Interest Rate}*(({To Date}-{Bill of Sale Date}))/365 In Airtable, this formula generates a NaN error message: I got it to work by breaking it out into two cells and using the formula recommended for differences in dates from Airtable’s support page on NaN errors: Cell labeled “test:” IF( AND( {Bill of Sale Date}, {To Date} ), DATETIME_DIFF({To Date},{Bill of Sale Date},‘days’), BLANK() ) And in another cell, this formula: UPB*{Interest Rate Total}*(test/365)

I have a field named Postcode Area which is type formula and has nested IF statements. Is there any way to add a second action to an IF statement such as: IF (A=B, (action 1 & action 2), action 3) I would like to take the value that is returned from the IF statement and paste it into another field within the same table so that I can use it in another lookup. If there’s no possible way to do that in the IF statement, is there another way to duplicate the value of Postcode Area into another field? I have done this successfully with a script but I don’t know of a way to trigger the script when a form is submitted.

Hello Airtable Community! My table has a single select field with 3 options. The field is called SVC Broad Category: There is another single select field with 20 options called SVC Area: When we add tasks to the table, we assign them to a SVC Area and to a SVC Broad Category. I am hoping for assistance with an IF Statement for SVC Broad Category, so that whatever is selected in SVC Area (e.g. Alcohol) is automatically assigned to the right SVC Broad Category (e.g. Ops/Logistics). The image below shows the Areas and Categories. As always, any assistance is greatly appreciated.

hi, I am new to airtable, and been trying with no results to update a heavy equipment fleet location when in a different table I create a record that moves a piece of the fleet from jobsite A to jobsite B, could anyone point me in the right direction…thanks.

nOt sure if I’ll ever get the hang of this formula thing. How do I write a formula to calculate the minutes based on hours times days using these types of options the columns are and both field types are a single select resulting in these kind of combinations? |4 day workshop| |Full Day 8 hours 8-5| |4 day workshop| |Full Day 6 hours 9-4| |3 day Workshop| |Full Day 8 hours 8-5| |3 day Workshop| |Full Day 6 hours 9-4| |2 day workshop| |Full Day 8 hours 8-5| |2 day workshop| |Full Day 6 hours 9-4| |1 day Workshop| |Full Day 8 hours 8-5| |1 day Workshop| |Full Day 6 hours 9-4|

Greetings, I’m trying to do a simple multiplication formula. I want to multiply two columns. (Pic 1 of 3) I am using the formula as described in the directions, Price * Quantity. (See Pic 2 of 3) I only get an error message! (See Pic 3 of 3) I can’t figure out what I am doing wrong. Thank you.

I am using the Time Tracker block, and the Pivot Table block to track hours and total hours. Now, I need to calculate earnings based on the total hours. I’ve played around with a few formulas and field types and can’t get it QUITE right. I have my clients listed in a multiple select column. I need the total earnings to be calculated by client and I’m having trouble wrapping my head around how to set up the base to get to this calculation.

I am having a hard time with the IF statement. I have a Status category I would like to make student status change based on Anticipated graduation date, however, I don’t have that for all students. So this is the formula I am trying and failing at: IF( {Anticipated Graduation Date} <= TODAY(), “Graduated”, IF({Anticipated Graduation Date} > TODAY(), "Active Student”, “Unknown” ) I’ve done a couple variations and can’t seem to do it. Any thoughts out there?? Thank you, R

I am attempting to create a formula that finds the latest lastModified Date from a group of records. The group is a single select column. = greatest lastModified date of “group1” Thanks

I have a formula where if condition 1 and condition 2 are true then show data from another column. What I want to do is have it show data from two different columns if the conditions are true. Here’s the formula: IF(AND({Contract Ended?}= “No”,({Exclusive?}=“Exclusive”)),Rights, BLANK()) What I want to do is add another column - Holdback Rights - to the formula so the data in both shows up in the new column. It seems like it should be simple but no matter what I do it won’t show the data in both. Thanks in advance! Jennifer & Mary

Hello AT community! I have a question regarding creating formulas. In my base I have 2 tables: a recipe table with each ingredient in its own field, linked to my ingredient table. In the ingredients table I have several single select fields for each allergen that a particular ingredient contains. So let’s say I want to summarize the allergens in my recipe for lemon meringue pie. I’m using a frozen pie crust that contains dairy and wheat, allergens which are indicated as “yes” in their respective fields in my ingredient table, plus eggs (another allergen). Back in the recipe table, I would like to summarize all the allergens present in my recipe for lemon meringue pie, so I could easily look at the recipe and see that it contains eggs, dairy and wheat without having to go back to the ingredient table and look at each ingredient individually. Is it possible to create a formula to summarize all the allergens present in my recipe, within the recipe table?

Hello, can I join three fields un one? I want to mix three fields in one automatically. Is that possible? I dont know wich rule I have to follow. In A I have different categories (A) which belong to another column (B); and I want to mix in a third field the categories which are the same. Is that possible? A: blue B=house; C= Carla=blue+ 13yearsold D: I want to mix in other names in C, the words that are the same. thank you Cristina

Hi all, this is my first post on the Airtable community. :slightly_smiling_face: I have a base that I use to track my FB groups activity. So I have a column with a checkbox to mark when I’ve completed something in that group. What I’d like to do is have those checkmarks automatically clear off at the beginning of each new week. Is that possible with a formula? If so, can someone point me in the right direction of where to get started? Thanks so much for any help! Valerie

Hi! I’m new to Airtable, so what I want to do must be something very easy, but I can’t figure it out. Table A Employee Name | Current Company | *Other Companies Mark | Facebook | Amazon, Google Sergey | Google | Amazon, Facebook Jeff | Amazon | Facebook, Google The above is Table A, where it has a field Current Company. This field is linked to Table B: Table B Company Name | Employee Name Google | Sergey Facebook | Mark Amazon | Jeff Can I make *Other Company field in Table A automatically populated by Formula or something else?

Hi all, I’m quite new to Airtable and I’m trying to transition my logical thinking to relational databases from Excel but i’m struggling with something… I’ve recently imported an Excel worksheet into Airtable that contained form data and formulas on the same worksheet. In Airtable, I have been creating the tables I need and linking them to the main Data table where possible. In the Excel file, there is a form that I have also replicated in Airtable. The use case is that the form is filled out by a user and once submitted, a new line is entered into the Data table. The first 20 fields/columns within the Data table are for the inputted data from the form. The next 10 fields/columns are supposed to be calculations based on some of the data from the form and some data from other tables (vlookup from Excel). I am now trying to replicate the formulas that the Excel worksheet had (Vlookup) in Airtable but

I run a pet relocation company and we have many clients who hold their pet files with us in case of a future move. We need a formula that red flags when a vaccination expiry date is imminent (e.g. in 14 days, in 28 days, etc). Really struggling with how we can incorporate this into airtable.

I run a pet relocation company and when preparing pet travel paperwork we need to ensure that there are a certain number of days between the dob and a vaccination, or travel date, etc. We need a formula that red flags when one date is not within/above a specific range of another date (or the current date). Thank you in advance

The formulas are calculating in real time, while I’m typing values in another field. Is there a way to set it to stop behaving like that and calculate only after I type Return?

Hi guys! I need help figuring this one out. I was approaching the problem using MAX and MIN formulas, but that doesn’t help me sorting the 5 possible dates I have (just 2). Problem goes like this: I’m working a logistic solution. The operator works by areas, visiting a zone one day of the week. But during peak seasons, he may decide to visit the area every day of the week (hence 5 possible days). My table calculates the next possible visit day from the autodate it assigns when a new request of service is submitted. So, if the order is posted on a monday and the operator visits the client’s area on tuesday, is the next day. But if it’s on mondays, is 7 days from the request date. I hope you’re following me. Calculating which date it will be for that area is easy, with: IF({retiro 1}-WEEKDAY(Autofecha)<=0,DATEADD(Autofecha,{retiro 1}-(WEEKDAY(Autofecha))+7,‘days’),DATEADD(Autofecha,{retiro 1}-(WEEKDAY(Autofecha)),‘days’)). where ‘Autofecha’ is req