Formulas

What would be the correct way to have the duration / age of a ticket in Airtable being any status that isn’t “Complete”? In the “Days Open” field I currently have got to this formula : IF(LAST_MODIFIED_TIME(Status), WORKDAY_DIFF( TODAY(), LAST_MODIFIED_TIME({Status}) ) ) What’s the best way to stop it counting once the ticket is completed?

Hello! I am having trouble creating a formula to calculate my service fee. Our fee is 10% of the order total (min fee $10, max $25.) We charge and extra $20 IF the order is for delivery. Please Help!!

I am trying to manage a data dictionary and generate XSD (XML schema definition) as part of managing API definitions in our project. However, I keep getting blocked by this error. Here is an example base (I stripped down all columns that are not relevant to the XSD generation): Airtable

Hi, I have issues with a checkbox that doesn’t work proper with my IF AND formula. Or more correctly, when checkbox =“1” formula works fine, but when checkbox =“0” it doesn’t. As far as I can tell, an empty checkbox is supposed to be =“0”, right? Formula looks like this: IF(AND({Win}=‘Yes’, {EW}=“1”), (({Stake} / 2) * {Odds}) + (({Stake} / 2) * {EW Odds}), IF(AND({Win}=‘Yes’, {EW}=“0”), {Stake} * {Odds}, IF(AND({Win}=‘EP’, {EW}=“1”), {Stake} / 2 * {EW Odds}, {Stake} * 0))) Works like a charm as long as the checkbox is marked, but when it’s not the 2nd Row of the formula is not working and instead the ELSE thing at the end of Row 3 kicks in. I’m a total beginner at this so my apologies for novice language or stupid question. Many thanks! Best regards, Marcus Sjöberg

Is it possible to get the last 2 digits of the creation date (year) e.g. 20 for 2020 19 for 2019. This is to use in a unique reference field (first 2 letters of name + “-” + last two letters of creation year + “-” + Auto ref number) I currently have: UPPER( LEFT(Created By,2)) & “-” & ( YEAR(CREATED_TIME()) & “-” & {Project Ref} Which gives me: E.g. AA-2020-101 Where do I insert the RIGHT(XX,2)? I’ve tried the following but with a returned #error!: UPPER( LEFT(Created By,2)) & “-” & RIGHT( ( YEAR(CREATED_TIME()),2) & “-” & {Project Ref}

I have the following formula which returns the correct amounts, except when engineer report is selected. In this case it comes up blank. All the other selections produce the expected numbers. IF(Type=“Inspection”,“300”,IF(Type=“Estimate”,“30”,IF(Type=“Engineer Report”,50,IF(Type=“Photo Report”,“20”)))) Thanks

Hello and thank you in advance for taking a look at my question! I would like to have a formula that analyzes two columns of data to return a specific amount, or 0. Column 1: # Nights Column 2: Cabin Name If # Nights is greater than or equal to 7, AND Cabin Name is “299” then $1800, if not then $0 If # Nights is greater than or equal to 7, AND Cabin Name is “514” then $1850, if not then $0 I am struggling with the logic of whether the IF statement for cabin name comes first or the # of nights comes first. Also struggling with the IF(AND…) formula - or perhaps I should be using a different formula altogether? Please help! Heather

I’m trying to grab a variable amount of text from between two consistent characters, > and <, to format HTML. I’m using Zapier to convert rich text to HTML and then using an Airtable formula to modify the URLs with UTM codes. Example below. Starting Text Cooperative Currency Desired Output Cooperative Currency From this ">Cooperative Currency< To this Cooperative%20Currency">Cooperative Currency< Thank you! ~Brian

Hello, I am trying to do a simple DATEADD function. Add X number of days to Y date. I have a date formatted column and an integer number column. The formula column has this formula in it: DATEADD({Last PM},{Interval},’days’). I’ve tried every variation of the DATEADD function that I could think of, and nothing works. I’m getting this error: “Sorry, there was a problem saving this field. Invalid formula. Please check your formula text.” I can use the WORKDAY() function, without an issue. But this isn’t what I need. Not sure what I’m doing wrong. Thoughts?

Hello airtablers, I have a field {RESPONSE} with 5 possible choices A,B,C,D,E I need to be able to give a score based on each response. So if response is A OR B score is 4 if response is C OR D score is 1 if response is E score is 3 This would be easy to solve with a switch function but the cell with responses returns all the responses concatenated in a single array (A;B;C;D;E). Is there a way to score the responses? Thanks a lot

Hi Airtable Friends, I want to combine four things in my primary field: (1) Story ID#, (2) Date, (3) Storyteller, and (4) Story Title – all separated by a " - ", of course. I was able to get the first two things with this formula: ({Story ID#} & " - " & DATETIME_FORMAT(Received, ‘DD/MM’)) & " - " But when I follow this up with {Storyteller} & " - " & {Story Title}, I get a message that says: “Sorry, there was a problem saving this field. Can’t save field because it causes a circular reference.” To be clear, here’s the full formula that keeps getting rejected: ({Story ID#} & " - " & DATETIME_FORMAT(Received, ‘DD/MM’)) & " - " {Storyteller} & " - " & {Story Title} I’ve tried a number of variations using parenthesis and curly brackets, but always get the “sorry” message. Grateful to anyone who can help me get past this irksome obstacle!

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,""

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 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 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