Formulas

Hello Airtable community! I am struggling with a formula and thought I would put it here since you all never let me down :slightly_smiling_face: I want the formula to produce the result overdue IF the contact’s sum of (“level of connection” and “Level of importance”) is greater than 5 and “Last Contact” is more than 180 Days The contact’s sum of (“level of connection” and “Level of importance”) is greater than 7 and “Last Contact” is more than 90 Days -The contact’s sum of (“level of connection” and “Level of importance”) is greater than 8.9 and “Last Contact” is more than 30 Days For reference here is what I currently have (Very far off) IF(DATETIME_DIFF(TODAY(),{Last contact},‘days’)>90,“Overdue”) THANK YOU !!!

Hey guys, Which field type should I use to enter page numbers for each of my table listings? I want to be able to do some calculations on them later e.g. average page number for 10 rows of book listings. I tried using the number (#) field type but it adds an unnecessary decimal place. So for example a page number entered as 350 changes to 350.0. I was going to use the single line text field but wondered if that would prevent me running calculations later on. Thanks in advance if anyone can help.

Hello, Here is a screen shot: What I am trying to do is create a formula for In Stock so that when Condition is New, Quantity decreases by 1 (and if the Condition is not new, it stays with what is in stock). However, as you can tell with Row 3, that is not the case. My formula thus far is = Quantity & IF({Condition (from Maintenance)} = ‘New’) - 1 What am I missing? Furthermore, if Condition has ‘New’ twice in the column (or however many is in the Quantity), such as Row 1, in stock should be blank or 0, or if there is nothing in Condition, In stock should equal Quantity. I have it so when stock gets to 1, the status changes to Order More, and would like it to change to Out of Stock when it is 0 IF({In Stock} = 1, ‘Order More’) & IF({In Stock} = BLANK(), ‘Out of Stock’) Is this doable or do I need many more columns and

Hi, How can I change time format 8:00 into 0800? Thanks Erwin

I have a table with a lot of webshop orders in it. how can I make a view with all orders made to-date a year ago? So I can compare Christmas sale from last year with the sale this year to date. is there a function that checks for order date one year ago and back for example

Hello - I’m trying to combine and format data in order to use in a report…would like to separate with tabs if possible to get the spacing and format to look nice and consistent. (Spaces are not working out correctly if record data doesn’t have the exact number of characters.) I’m trying to doing something like the following, but can’t seem to find anything that produces a tab. I’ve tried “\t” and “ALT+09”. Any suggestions? {Data 1} + TAB + {Data2}

I feel like this question has been asked before, but I cannot find an answer or post that really applies. I have a form where users can input/sort options from one table (a hacked ranking system). Once they the form is submitted, I then want to figure out an automattion using basic text fields, or have multiple formula fields pull the records 1 by 1 into the separate fields in the order they’ve been input by the user’s form submission. A, B, C record order Field 1 = A Field 2 = B Field 3 = C I’ve looked at arrays, converting to strings, tried the string lengths by using the shorthand info, this seems like something I used to do all the time in others, but it’s very clear arrays of data are not well managed in this?

I’m trying to further customize a formula (that’s already working) with additional functionality to detect when text is missing a certain element and, in such cases, to then add that element to the text in a different column, but can’t seem to get anything to work. Specifically, I have a formula that, among other things, takes a URL from, say, Column A, and then customizes/edits that URL in Column B based on criteria from data in other columns. In this instance, Column A is a URL field and Column B is the formula field, and Column B displays updated versions of URLs from Column A. So far the formula is working fine except I also need it to detect when a URL in Column A is missing its trailing slash ("/") and to subsequently add in that slash where it would otherwise go when the URL appears in Column B. I also don’t think it will be as simple as merely finding/replacing instances of <.com> with <.com/>, <.org> with <.org/>, <.co> wit

Building a payroll system and need formula help with splitting up total hours between Daily Hrs, time and a half pay, and doubletime. Here’s my setup: My “Total Hrs/day” is the actual total of all hours worked, calculated with: (OUT-IN)-BREAK Then, I want to limit the amount of hours from the “Total Hrs/Day” that can go into “Daily Hrs” to 8 hours of regular pay. I do this with: IF((OUT-IN)-BREAK<=8, (OUT-IN)-BREAK, 8) This means that if 10 hours were worked, “Daily Hrs” is capped at 8. I want the remaining two hours to populate into the “time and a half” to be multiplied by that 1.5x rate. The caveat is that I need this to be capped at 4 hours of overtime, before spilling any remaining hours from the “Total Hrs/day” into “doubletime”. IF( (OUT-IN)-BREAK>8, ((OUT-IN)-BREAK)-8, BLANK() ) I can

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!