Formulas

I frequently use the formula IF((FIND)) formula to create indicator columns with 1s and 0s or emojis. In my latest project, however, it has not been working out as I want: The above is a screenshot of a Grid View of form results. The field “BACKEND_Expertise Category Prefix” is a lookup field that shows the subject area codes of subject areas that our respondents selected from a Linked Record question. For example in the fifth row, you see “SB, SB, SB, SS, SH”; that’s because the respondent selected the options for “Early Childhood education;” “Out-of school learning;” and “Parent Teacher Involvement;” which are all coded “SB” (which in our taxonomy system, means they fall under Education). I then created dummy variables/fields using the formula field type to spit out 1 or 0. IF( FIND(“SB”, {BACKEND_Expertise Category Prefix (from Subject Area Expertise)}), 1, 0 ) = so that all records that have “SB” in the lookup field will get a “1” in the column “Education”, and then I can sum up

Hello everybody! Inside my base we have 10-digit codes for each of our users for passwords and such. I was wondering if I could somehow use a formula field to only show a select few digits of that code. Similar to a phone number when they send you a confirmation text you can only see the last four. Thanks!

Hello community! I have the below if statement that needs revising. The issue is that the formula assumes zero when DeStaging Date is blank, when in fact, I want to see the result from the rest of the formula to popup on my Google Calendar. What should I add? IF({DeStaging Date}<{Monthly Rental Fee Renewal Date},“No renewal charge”,CONCATENATE(DATETIME_FORMAT({Monthly Rental Fee Renewal Date},‘MM-DD-YY’)," | “,{Client Name},” | ",“Rental Fee Renewal Date”))

Hiii So I am trying to track checks and employees. I have an “Employees” table and I have a separate “Checks” table. They are linked. I was able to do a rollup for each employee to get the total amount they received no problem. My issue is I want my rollup to only show me the checks they received in 2021. I see a lot of the forums talking about SWITCH and IF stuff. But what I want is different. I want the formula to look at my dates, see which ones contain the year 2021, and then spit out the check amount so I can then do a rollup on each individual year. This may be way too complicated and I would love a better answer. But ideally, I want to be able to look at how much each employee made by year in the employee table and not just in the checks table. But as each check is individually added, it would need to add up all the checks of that year from the checks table.

I have created a base for my companies therapists. I want our front desk to be able to filter the data to match clients up with therapists. I’m stumped on how to add the age ranges, as they vary greatly. Some therapists see ages 3-10, others 18+, or 25-40, etc. What I’m wanting is to be able to enter the clients age, 32 for example, and be able to filter out therapists that don’t work with that age. plz help

Dear beloved friend, Could any one help telling the formula to count unique value or text among the duplicate in airtable? Thanks in advance and regard,

Hello there! I am brand new to Airtable formulas and was hoping to get some help. I’m attempting to calculate NPS just for this calendar year of 2022. I was able to calculate the NPS for all of my results but when I try to modify it to add in just for the year 2022 as an If/Then statement it doesn’t work. Here is what I have been attempting to use. Thank you so much for your help! IF( Date}=2022 ) THEN( {NPS Score}=BLANK(),BLANK(), IF({NPS Score} < 7,-100, IF({NPS Score} > 8,100, 0)))

I am storing MAC addresses and we scan a bar code and in inputs the MAC address. The only thing with this is they don’t have the ‘-’. Is it possible to format it to have the - between every 2 characters? Also do I need to have 2 fields for this? The first being the scanned MAC address and the second being a formula? Or can I have a formula and input text then it formats it within the same field? Either way will work.

Currently I have a field that contains multiple products separated by commas. Is there a way to “find” all commas and “replace” them with the “/n” line break? I would like the product field which currently looks like this “product1, product2, product3, product4” to appear in the field as product1 product2 product3 product4 Is there a way to do this?

I wanted to assign a delivery timeslot to an order based on the order created time. Example, if a customer places and order after 8pm and before 12midnight, the schedule delivery will be between 8am to 11am the next day. I tried using IS_AFTER and IS_BEFORE in an IF-AND formula but it is showing a blank result. NEED HELP BADLY. Thanks

Hello! I am trying to create a formula to help track tenure and extension dates for personnel. Each person has a “start date” – I want to have formula fields that calculate quarterly extensions +12 months; +15 months; +18 months Here is the formula I entered: DATEADD({Start Date},+12,‘months’) but I am getting an error

Hello, I have two fields with two different formulas: Field 1:DATEADD({Date morning Start},{Number of days},‘d’) Field 2: DATEADD({Date am Start},‘4’,‘hours’) I would like to have only one field grouping these two formulas Also, I have a third formula field but the formula doesn’t work on either the date or the time: If({field date}, 09:00, ‘true’, ‘false’) Thank you for your answer

From my screen shot below, you can see I have the column Company. In this column it has duplicated values, so I was wondering if there was a way for it to only display once using a formula in a new column? e.g. in the count column to only show The good company once. Also would be great if this process is automated, as I am using integromat to keep insert new records.

I’m currently working on a base to track our inbound containers. We’ve had a simple one for a while where we will update the movement of our shipping containers, and then manually update the ETA, based mostly off guesswork. I’m looking to automate the ETA updates, based off when a container was last seen and where it was last seen, using the average number of days it takes to get from place to place. For some context: we have 5 different shipping routes spread across 3 different warehouses. These all have a different number of steps to get through before they reach our warehouse. The way I’ve designed it thus far (with the help of someone in a forum, thank you!), is to have the following tabs: Containers ETA Calculation Paths (Routes) The container tab has generic information about the containers we need to track. I have it linked to the Path tab to show which Port it will need to go through. I also have a “Last Activity” as a date field and “Last Known Movement” as a single field drop

This is not actually the context of what I’ll be using this for but rather than explaining what we do, I figure it’d be better to compare it to the old math question which is… If I have someone that has 5 shirts, 5 pairs of pants, and 5 pairs of shoes we need to cleanly display all the options and print them into different fields. I’m wondering if this is doable, if so, what would my setup need to look like assuming the columns I’d start with in this example would be shirts, pants, and shoes? Thanks in advance!

I have a table which has a Payout Month Column, which is a formula: DATETIME_FORMAT({Check-in date}, 'MMMM') How can I add the month number in front of the Month word (ie: 1 Jan)?

Hello all, I’m trying to figure it out for weeks to solve this problem. Base A : I want to get address data (zip code) + buying rate from this base Base B : I want to get address data (zip code) + selling rate from this base Base C: When I write that specific address/zip code and it matches with the address in Base A and Base B, I want to see buying rate and selling rate in separate columns automatically. So it is like; zip code base A + zip code base B = buying rate base A + selling rate base B in base C That would be great if you could help me to solve this issue because I’m about to lose my mind :slightly_smiling_face: What is the right way to do it? Is it by a formula? Is it by something else? Please help me. Thank you.

Hey there I still don’t get AirTable to the fullest and would appreciate some help. I got a database with Birth Dates and managed to find out peoples ages with the TONOW formula. I would want a second row telling me wether the Date filed is more then 4380 days ago or not. I tried something like IF(Date > 4380 days ago , “True”, “Not True”) As this does not work - what can I do instead? Thx for any hint.

Hi! I am working on a contract template, where one of the dynamic fields pulled from AT is the duration of the contract, expressed in the following manner: The initial term of this Agreement shall be for a period of 2 months and 5 days, commencing on… I have a basic formula that allows me to extract an amount of time as a single term (1 month), but it’s not smart enough to say something like 1 month and 5 days. Basically, I don’t know how to add the remaining days. Here is my current formula: IF( {End Date & Time}="", "", IF( DATETIME_DIFF( {End Date & Time}, {Start Date & Time}, 'months' )=1, DATETIME_DIFF( {End Date & Time}, {Start Date & Time}, 'months' ) & " month", IF( DATETIME_DIFF( {End Date & Time}, {Start Date & Time}, 'months' )=0, IF( DATETIME_DIFF( {End Date &amp

Hello, I’m trying to work out a formula that When number of ID’s in ‘ID’ colum matches number in ‘how many in household’ change status of ‘Household’ to complete. I then plan on triggering a webhook which I think I understand how to do. Picture for reference included Many thanks, Lou

dear airtable community, i would like to create a formula that sums up the size of the identical item. in the screenshot you will see, that every record has only one size, even though its sometimes the identical sneaker. i want it to be: “dunk low disrupt 2 pale ivory black” : 39 EU - 8W US [case 1: only one record with the title - nothing should be changed, just display the available size] “dunk low light violet (venice)” : 38 EU - 7W US [case 2: multiple records with the same title, but with the identical size - only one record should include the size they all share and the others should be empty] “dunk low two tone grey” : 38 EU - 5.5Y US / 44 EU - 10 US [case 3: multiple records with the same title, but with different sizes - only one record should include all the sizes which are seperated with a " / " and the others should be empty. if anyone of you could help me with this challenge i would highly appreciate it, i hope i described the requirements understandable, if not feel

Hi. Im brand new to AT. Im pretty good with Excel formulas but need to learn AT. I need a formula in “Prep Start” that will duplicate “Job Start” without the time. Thank you!

Hi team! I have a table A with a column called “Offer Amount”. I want to create a table B which will have a cell containing the max(tableA’s OfferAmount), that gets updated each time a new record is created in table A. Is this possible on airtable? The background is that I want to send the current highest Offer Amount (out of all the records on table A) to a webpage on builder.io via zapier (hopefully) each time a new record is submitted to table A. The records are inserted into Airtable table A from a form on builder.io via zapier.

I have a list of tasks in my table and a date range for each task. The date ranges are entered into two different columns, Start Date and End Date. Each of those are formatted as dates. I’d like another column to list all the months that are included in that ranges of dates. So for example, if Start Date is Jan 1, 2020 and End Date is April 30, 2020, I’d like the Months column to list Jan 20, Feb 20, March 20, April 20. Is there a formula for this?

Hi there, I need to calculate the difference between the {Date} and today in days, and display “todo” or nothing if the number of days is >1. I tried this but I keep having errors : IF(DATETIME_DIFF({Date}, TODAY(), ‘days’)>1),“To do”, " ") Any ideas?