Formulas

I think the formula posted to the Airtable blog here, under the heading " Figure out which date comes first :soon: " is way too complicated. The formula on the blog is posted as: IF(IF(XOR(BLANK(),{Expiration Date},{Date})=1,"",IF(OR({Date},Expiration Date)=1,IS_BEFORE({Expiration Date},{Date}),"")),“ :warning: Expired”,“Ok”) Mistake #1 is obviously that the second Expiration Date needs {}. Simple enough. But couldn’t it be written more simply as: IF(IS_BEFORE({Expiration Date},{Date}),“ :warning: Expired”,“Ok”) ? I get the same results. Even if we stick to the same original formula, I can delete all of the bolded bits and return the same results: IF(IF(XOR(BLANK(),{Expiration Date},{Date})=1,"",IF(OR({Date},Expiration Date)=1,IS_BEFORE({Expiration Date},{Date}),"")),“ :warning: Expired”,“Ok”) Am I missing something? Just trying to understand. Thanks for

Hi all, pretty new here! I just want to set up a field to capture grams and kilograms (weight). Do I set cutomize the field to number? I’d like to to display KG or g like it would if I added a currency $/£/€ etc. Is this possible? Thanks for any help you can provide!

Hi, I’m writing an item order table and have been successful in pulling prices from a matrix based on qty input using the Tiered system, but I find I now need to include the ability to override the price. The Tier Formula is below and works - it populates the name of the tier based on the quantity input IF(Quantity > 1, IF(Quantity < 5, “Tier 2”, IF(Quantity >= 5, IF(Quantity < 10, “Tier 3”, IF(Quantity >= 10, IF(Quantity < 15, “Tier 4”, IF(Quantity >= 15, IF(Quantity < 25, “Tier 5”, IF(Quantity >= 25, “Bulk”)))))))), “single”) Once the tier is populated, I’m using a SWITCH formula to pull in the correct price based on the price tier SWITCH((Tier), “Single”,{Single Unit Cost}, “Tier 2”,{2-4 Units Cost}, “Tier 3”,{5-9 Units Cost}, “Tier 4”,{10-14 Units Cost}, “Tier 5”,{15-24 Units Cost}, “Bulk”, {Bulk 25+ Plus}) I find that I need to be able to override a price if their is a special one off price for a given line item. But since I’m using a formula, there doe

I have a formula I can’t figure out. My column that I want as a formula is called “Forecasted SCIP Received” To calculate this date, I need to use another column called “Actual Site Walk Date” plus a “Duration” column. However, if “Actual Site Walk Date” is empty, I need it to use “Forecasted Site Walk Date” instead, so: If the “Actual Site Walk Date” is not Empty, then “Actual Site Walk Date” + “Duration”. But if Actual Site Walk Date is empty, then “Forecasted Site Walk Date” + “Duration” How do I write this?

Hey, im trying to get the end result to change based on the input from the other inputs, I’ve currently got a simple version of this where it ticks if everything is signed off and if not then X but what I want is some form of hierarchy. e.g if everyone says they are signed off but one is blocked then the main status is blocked… similar if all are signed off but one in progress and one back burner then the status is back burner. Is this possible :slightly_smiling_face: ?

Hi everybody, I am new to using Airtable. I am sure that there is a formula for this but I can’t seem to find it or I don’t write it in a correct way. With a API service of a third party software I pull data from my Airtable. This data is dynamically filtered by using filterByFormula. The data I need is in the column called “groupcode” and has numeric values varying from 100 and 900. These numbers stand for product categories. Normally if I need products from the category with the number 212 I use the following formula. {groupcode"} =“212” But now I need products from category 212 and 268. Is there a way I can display only the records of two different specifc values ? Any suggestions or tips are appreciated! Thank you in advance!

I’m searching for the occurrence of a specific character in a text field with SEARCH but it doesn’t return any answer, can you help?

Hi everyone! I have a problem to solve =/. I need to find matched items between BUCKET and PERSONA tasks, but I don’t know how to do this.

I am having trouble with a formula. I need to bring back an exact minute time but cannot figure out how to make it read the minutes in this equation. Can someone help?

Hey all, I’ve been trying to work this out on my own but it’s providing super difficult. I have a birthdate field and I want to find out the age. I am using DATETIME_DIFF(NOW(), Birthday, ‘years’) & ’ years ’ and I see the age however those that haven’t filled in the birthday field say Nan. I would love some help. Thank you

Hi! So I have a main table which has a series of streams of work. There is a second table with ALL the activities related to those streams. The tables are linked such as on the main table it brings in ALL the activities in a string. I want to bring forward just the next activity for each stream. The roll up function works for the date but I can’t seem to get the text description in. I basically need to say. Of all the activities related to this record, give me the next one. But because they are in different tables, I’m stuck. Any tips would be very much appreciated

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 states “Your result type is not a number or a date. Formatting options are currently only available if

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 of the fruit. I would need the application to read the Items (Fruit) and Cities only by selecting it to read the contents of a cell. Here I have provided 5 records per day but there could be up t

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