Formulas

While I’ve seen plenty of posts on formulas based on whether a checkbox is checked or not in another field, I’m curious as to whether it is possible in the inverse? I want to create a formula that will say, “If condition exists in some field, mark checkbox in another field.” Can this be done?

Hi All, I’m looking for some help in building out a formula field (Transfer Tax) that is formatted as currency. I’m new to Airtable but have a lot of Salesforce.com experience. The Transfer Tax field provides the dollar amount that will be charged when the dollar amount in another field (Purchase Price) falls in a certain range. The rule is: the transfer tax is $3.30 for every $1,000 of the purchase price. Essentially, a Purchase Price anywhere from $0-$999.99, the Transfer Tax field should be showing $3.30. If the Purchase Price is anywhere between $1,000-1,999.99, the Transfer Tax field should be showing $6.60. If the Purchase Price field is anywhere between $2,000-$2,999.99, then the Transfer Tax field should show $9.90. This continues for infinity. Ideally, I only need it for a purchase price going to about $100,000. In Salesforce.com, I would l

Hey everyone. I’m using this formula to calculate how many days it took for a response. DATETIME_DIFF({Response date},({Submission date}), ‘days’) & " days" I’d like to add a part to the logic that says if {Response date} is blank, calculate days between Submission date and today’s date. Any ideas?

Hi, all! I’m facing a challenge to do the following: Column A with multiple choices (All, Sheep, Goats… up to 12 more) Column B with multiple choices (Universal, Black, White) Column C with single choice (F, SE) Column D with multiple choices (3 months, 6 months, 9 months) Because I have more than 500 records I need a formula to do the same as filtering manually. May be the topic should be divided in two parts: I want to have a formula that can tell me if a certain combination for a record is “true”. If it is true, we can name it (All-Sheep-Black-F-3-months), then the formula displays the name of the combination (All-Sheep-Black-F-3-months). I found that in excel this could be done with ISNUMBER and FIND but Airtable doesn’t support ISNUMBER. The problem for me here is that I cannot use AND and OR in columns with multiple choices because it doesn’t work or I just can’t make it work. For combination

I am trying to create a button that when clicked, opens a gmail link and populates the email address and body. My table has a lookup field called “PrimaryContactEmail” that simply pulls from the link record “Primary Contact” and brings in the email so that I can reference it in my formula. The formula I am using is: “https://mail.google.com/mail/u/0/?view=cm&to=” & ENCODE_URL_COMPONENT(PrimaryContactEmail) & “&su=” & ENCODE_URL_COMPONENT(“IPM Scoutek Demo Devices Shipped”) & “&body=” & ENCODE_URL_COMPONENT("…") The weirdest thing happens though. For rows that HAVE a value in PrimaryContactEmail, the button is disabled (greyed out) but if the PrimaryContactEmail is empty, the button is clickable! I thought MAYBE this is some kind of spam prevention thing, preventing me from emailing users outside of the base,

Hello, I as making a Base and there are 4 sheets. Is there something i can to for some of the columns of the sheets be linked? Example: We made a CRM about out clients. In one of the sheets are all the oportunities and who of our Hunter Team brought this oportunity. Then, we made a Sheet that tells us more about the client (website etc). I would like to link the Hunter in both sheets for that same name of client.

Hi everyone, I did my best with this using just the Help pages but I can’t seem to get this formula right. Basically, I have a spreadsheet where I have listed my credit card statements when they come in, including the date that the bill is due. Then I have a field where I state whether the bill is “Paid (in full),” “Paid (partially),” or “Unpaid.” Lastly, I have a formula field to determine whether the bill is overdue. This is the formula I have so far: IF(AND(DATETIME_DIFF(TODAY(),{Balance Due Date},‘days’>0),OR(Status=“Unpaid”,Status=“Paid (partially)”)),“Overdue”) What I’m trying say with this formula is: If today’s date is later than the balance due date, and the bill is only Unpaid or Paid (partially), then I want it to spit out “Overdue.” However, with this formula, it ignores the DATETIME_DIFF command and always spits out “Overdue” even if the balance due date hasn’t passed yet. Is there a way to fix this?

Trying to auto create a unique key in a table used for tracking grazing on a cattle farm. So my current formula is “(Pasture & DATETIME_FORMAT(Date, ‘M/D/YYYY’)” Where “Pasture” and “Date” are both fields in the table. Any help?

Hello guys, I have this string : DECO > OBJET DECO > AUTRE OBJET DECO and I would like only : AUTRE OBJET DECO so only what is after the second >. I tried with : RIGHT( {Catégorie}, FIND( “>”, {Catégorie} ) -1 ) RESULT was " DECO" (The last 5 characters) I really don’t understand why only the last 5 characters.

Hello Community, When checking the API Doc, I can see Airtable is providing size and thumbnails dimensions for an Attachment Is it possible with a formula (not script) to pull properties from the first attachment? I tried several things like: Attachment[0].thumbnails.full.width … but didn’t succeed :confused: Regards.

Hello! Newbie on Airtable, so I’m pretty sure this is a really daft question, but after looking through the help and this forum for about 2 hours and not finding anything, I’m now starting to wonder if my goal is even possible! I have 2 tables: An Authors table has name/contact details/author image fields. A Content table, which had a load of fields, including a ‘link to another table’ field to display the ‘author name’ field from the Author table. What I want to do is to have a field in the Content table with a tick (or yes/no or some sort of indicator - I kind of don’t care what it is!) which shows based on whether there is or isn’t an image uploaded in the ‘Author image’ field on the Author table. So kind of this (which I know isn’t actually a formula, just trying to lay out what I’m trying to achieve!): if field ‘author image’ in table ‘author’ is not blank, then show :white_check_mark: in field ‘got author image’

Hi Every one i want a formula like this: IF({TaskIcon} == BLANK(),“https://png.pngtree.com/png-clipart/20191120/original/pngtree-pen-icon-isolated-design-png-image_5045590.jpg","null”) TaskIcon is an Attachment field. i want that if {TaskIcon} field is blank (No attachment in the field) then my DefaultIcon field set to a url Please Help Someone

The following formula in the Airtable blog post here, under the heading “Find the next occurrence of a specific day :hourglass_flowing_sand: ” with the description: “if you just need to know the nearest Tuesday to a given date—even if that Tuesday has already passed—this simpler formula will do the trick:” is as follows: SET_TIMEZONE(DATEADD({Date},2-(WEEKDAY({Date})),‘days’),‘America/Los_Angeles’) (It is suggested that the 2 in the formula relates to finding the nearest “Tuesday”, and can be replaced with any other day from Sunday (0) --> Saturday (6) ) I noticed this formula break when the given date is a Sunday and you are looking for the nearest Thursday (4), Friday (5) or Saturday (6) (which would occur before the given date). Since

Hello, I receive time data in UNIX format in a text field on airtable. I would like to create a formula that allows me to have a date that I can understand, in the same basic format already on airtable. Could you help me? Thank you in advance I’m living in Switzerland, Zurich time

Hi AirTable friends, I have records that each have a long text field which is TOO long. That long text field is called “Excerpt.” What I’d like to do is create a formula field called “Trimmed Excerpt” that copies the content from “Excerpt” field but limits the word count to, say, 50. What’s the formula for that? I Googled for a solution but came away empty-handed. Thanks to those of you who take a crack at this one!

Can you do simple references within formulas? Current formula: IF({Frequent escalation?} = 1, “However, {he/she} frequently escalates, and so {he/she} needs a significant amount of support.”, “{C He/She} does well there with minimal supervision and supports by adults.”) The formula is working, except the pronouns are not. They are of course referencing other formulas, and there is a checkbox called “Frequent Escalation?” If not (siiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiigh) any suggestions on how to get around this? I am eventually wanting to get to more advanced texting formulas, but need to figure this out! Thank you so much!

Hi everyone, Now that I have Airtable all up and running with formulas etc. I have hit a snag. I understand we cannot link between bases but we can link within a base. I’m trying to create a formula that will decrease my stock column by 1 when a NEW appliance is installed into an apartment via the Maintenance tab under the Comments column. Am I able to do a IF formula in Inventory to read the IF Comment under Maintenance reads NEW - 1 from Stock? I don’t know if the formula will work because the Comment field is typed in.

Here’s the formula I’m using: IF( {Number Field 1}!=’’, ({Number Field 1} / {Weight (number field)} + {Number Field 2}), IF( {Number Field 2}!=’’, ({Number Field 1} / {Weight} + {Number Field 2}), ‘’) ) My goal with the formula: I’m trying to reach an inventory count as a result of this field. A) If the inventory count is low, we can manually enter the exact count of the part into “Number Field 2” B) If the inventory count is higher, we get an approximate count based on weight. So we enter the weight into “Number Field 1” of the total part. It calculates total weight vs the part weight to give us a part count. C) If for some reason during the process we weigh the inventory, but find more of that part, we sometimes add the exact count on after. So if there’s numbers in each of the field, we need the formula to account for both. The result of the formula “works”, as in the numbers are right. But it isn’t a nu

I want to provide a workaround solution given that Airtable natively cannot do much with the change log, and I want to create a column that would update another column (within the same table) every 1 or 2 days. For example, something like the below: Column B = IF( Last Modified Date < Today - 1, = Column A, IF FALSE DO NOTHING). Through this I am hoping that even though Column A is continually changed, I can see the previous value of Column A via Column B. Thank you in advance, and please let me know if I should explain further.

Hi! I’m going crazy trying to come up with an NPS formula. I am planning to have a formula return -100, 0, or 100 depending on the value so I can then average that column on dashboards. I can get my NPS just fine, but I can’t figure out how to exclude blank cells in an IF statement. In words, I want the following: If {Program NPS Value} is between 0 (inclusive) and 6 (inclusive), -100 If {Program NPS Value} is 7 or 8, 0 If {Program NPS Value} is 9 or 10, 100 If {Program NPS Value} is BLANK, “” There’s gotta be a way to factor out these blank cells. I’m getting -100s for all my blank cells with this formula: IF({NPS Value} < 7, -100, IF({NPS Value} > 8,100,0)) I appreciate your help!!

Hi there, Im looking for a bit of help with a formula. I’ll try and explain it as best I can… I have six fields as follows: 1st Payment Date (date type) (e.g 1st Jan 2020) 1st Payment Amount (Currency) (e.g £500) 2nd Payment Date (date type) (e.g 3rd Jan 2020) 2nd Payment Amount (Currency) (e.g £500) 3rd Payment Date (date type) (e.g 5th Jan 2020) 3rd Payment Amount (Currency) (e.g £500) What I would like to do is create another field with a formula that would basically sum all the payment dates which fit within a specific date range (an example date range relating to the fields above would be the 1st to the 31st Jan 2020) Would anyone be able to help with this? Thanks in advance!

I have a bunch of different strings of text and want to remove different phrases, but I’m unsure of the formula. Here is an example of what it may contain: 123.jpg 234.eps 345.ai 546%20(1).eps I want to remove every instance of “.jpg”, “.eps”, “.ai” and “%20(1)” Is there a way to do so?

I am trying to use an IF statement and I can’t get it to work either with an AND or an OR formula (or both)… Essentially I have a calculated price for my products and I want to create a formula field for a selection of the products IF ‘Sub-Category = X OR Y’ then take ‘Calculated Price +0.1’ to add 10p to the calculated price. I have tried this which returns a result for all products and not just those where the Sub-Category is “Coffee Retail Beans” or “Coffee Retail Ground”. IF(OR({Sub-Category}=“Coffee Retail Beans”, “Coffee Retail Ground”), {Calc Price}+0.1) Any other combos I try using AND statements where I have followed other similar examples on different threads, but they always come back with a formula error. Basically I have a list of wholesale and retail products each with a calculated price and for customers who only buy retail I want to add 10p to their price, but I want this to only apply to products with a retail ‘sub-category’. If th

Hi. I’m currently wondering if there is a way to extract a section of text from a URL. http://...Drink%2FSeafood%20%2D%20shutterstock_524277715%2Ejpg&...Images%2FFood%20%26%20Drink I want to Airtable to automate searching the link for “shutterstock_” and then display the following characters up to “%2E” The link will always have a different amount of characters before “shutterstock_” and after “%2E” and may contain a different length of characters between the two. Is there a formula for something like this?

Hi, I’ve been using Airtable for quite a bit since I started my new role and I need help with this particular thing: I have a table that has the following fields: Line items (Linked Record to Products - Multiple allowed) Customer Type (Single Select with options A, B, and C) Payment Type (Single Select with options CASH and CARD) Lookup Value (Formula) Charged Items (Linked Record to Pricelist - Multiple allowed) Products has the sample data: Product1 Product2 Product3 Pricelist contains prices for all permutations of Product-Customer Type-Payment Type and has the following fields: Transaction (Key, Formula - concatenation on Customer Type, Payment Type, and Product) Price (varies per permutation) For example, a transaction with Product1, Customer Type A, Payment Type CASH would yield “CASH A Product1” as the key for the record. Now back to the very first table - I’d like to customize the formula in Lookup Va