Formulas

I have a long text field that takes the food and the preference of the user.Sample Input:Burgers (Enjoy), Pasta (Ok), Seafood (Nope), Pork (Ok), Beef (Ok), Vegies (Enjoy) I want to get all the texts that are "Enjoy"-ed by the user.Expected Output:Burgers, Vegies What formula can I use to get this output?

Hello all,I'm formulating a formula to get the total number of days requested for PTO. The current formula I have is:DATETIME_DIFF({Event End}, {Event Start},'days')The output of this displays the days, but also accounts for the weekend days as well which I don't want.I have a separate table labelled: "Holidays" with a column for holidays showcasing the federal holidays with a format of "1/2/1991". I understand I'd need to use the WORKDAY_DIFF, but don't know how to incorporate that onto: DATETIME_DIFF({Event End}, {Event Start},'days').Any help would be appreciated. 

I an Airtable newbie. I work at a public library and use Airtable to manage and track our booklists. Since there are no sales or orders per se, we calculate usage using physical counts. Every few weeks I count them and record in Airtable the current qty of each booklist and the number added (if any) for each booklist. It's easy to determine all time usage: ([starting qty]+[number added])-[current qty]. But I am stuck on how to derive annual usage via formulas.There are two tables involved: Booklists and Inventory. Booklists uses rollup fields to grab numbers from Inventory. Inventory has fields both for qty added and for physical count.What I want I a view grouped by year, sub-grouped by booklist, showing annual usage. I can brute force it by adding a new field every year for the ending qty, but I'm hoping there's a more elegant way to do it via formulaThe formula would be something like this for each booklist: For a particular year, find the earliest record dat

PLEASE HELP  ME I need to a formula like this. =IF($G4<=200;5;EĞER($G4<=600;7;EĞER($G4<=1000;9))) How is write on air table thank you in advance 

Apologies in advance if this is elsewhere or common knowledge or the query is posted in the wrong location (Formulas). I did look all around to find a similar Q&A elsewhere (found some close but none exactly what I was looking for), so...TWO sample use case needs for the following:1) Gardening: calculating when to sow or transplant a crop indoors or outdoors in relation to estimated frost dates.2) Annual Events (film festivals, awards shows) that occur at about the same time every year.Q1: How can I have a date withOUT the year? (reworded: How can I set up ballpark seasonal dates to faciliate copying over every year?)A-ish? I know I can do three columns (month, day, year) - but then I can't set up an annual calendar subscription, right?Q2: Is there a way to do a formula to calculate X# of weeks before a key date (i.e.: to add or subtract from the earliest frost date in a column).?Thank you!

Hey all, I’m wondering if there is a way to perform element-wise multiplication of two arrays of numbers. For example, if I have two fields with arrays [1, 2, 3] and [4, 5, 6] respectively, can I write a formula that produces [4, 10, 18]? As an example use case, I could calculate a weighted average of an array of numbers [10, 20, 30] with weights [.2, .5, .3]. After element-wise multiplication, I could take the sum with SUM([2, 10, 9]) = 21 (In this example, I’m just taking the dot product of these two arrays).

Hey guys, I'm currently stuck on how to formulate a profit based off the total value of the invoice. This is what I currently have:I have the formulas needed to determine the selling price of each product, however via my "Invoice" tab, I have this: Where I'm stuck at is, how to formulate the formula to determine the gross profit of each invoice in their respective columns.Any help is appreciated! 

Hello,  I am trying to use the WORKDAY function to add X amount of days to my start date, however it is populating an extra day but only if I include the time. I don't need to include the time but its the only way I can get the project "duration" to show up on the calendar or timeline view. Can anyone help please? I've tried to add -1 days to remove a day from the start day but it is telling me there is an error. Current Formula: WORKDAY({Start Date}, {# of Days}) Thank you, 

I have a formula that is achieving 2/3 of my goals, but I need some help figuring out that last 1/3! I am trying to add an "SOS" flag to any to-do that meets one or more of the following criteria:Has been manually flagged as "Behind" in the Alerts column.Has not been given a Due Date (so the Due Date field is blank).Has a Due Date that has passed AND it has NOT yet been marked as "Done."How would I structure that final part of the formula? Currently, I have: IF(     OR(          Alerts = "Behind",          {Due In} < 1     ),          "🆘",

Seeking to identify if two numbers match exactly in both fields, such as 22 matching 22, and not partial matches like 2.  

I would like to separate a look up field (values separated by commas) into separate cells. I have information from Table 1 linked to Table 2 (Junction Table) that is linked to Table 3. Table 3 has a look up field (puling information form Table 1 to Table 2 into Table 3). The values in the look up field are separated by commas. (I am having a problems applying formulas to the look up field). Is it possible to separate these values into separate fields?

I just found out that both SET_LOCALE and SET_TIMEZONE functions returns an incorrect hour, at least for Europe/Lisbon and en-gb as per the below examples:SET_TIMEZONE(NOW(), 'Europe/Lisbon')SET_LOCALE(NOW(), 'en-gb')Both these functions as returning 1 hour less than the correct one.

Hi, I need to have a formulated field where if the time in the field "start date" is 6 am it returns "ET" and if it is 12 pm returns "PT"

Hi all, my first post here. I did some searching on the forum and couldn't find the answer to my question so here it is.I'm building a client portal where users can add their own records, and share them with others. The records that they have created and shared are shown in their personal overview. This overview is created by adding a condition to all records made ('record creator' is Logged in User's Client Name).Currently, the users need to add their own name to the record they add in order for the filter to work, but this is quite weird and clunky.Therefore, how do I automatically add the user name to a created record? I tried using "Created by", but I found that this doesn't work.Thanks!

I would like to recreate a transaction sheet and P&L sheet from Google Sheets into Airtable. In Sheets, there is a "Transactions" sheet with all of the company's expenses. The relevant columns for the P&L sheet are data, amount, category 1, subcategory 1, subcategory  2, and subcategory 3. On the "P&L" sheet, which tracks the transactions by categories and month, I am using SUMIFS equations to get the weekly and monthly totals for each category, as well as the total expenses for each month. For example, this is a snippet of the table. It is continuous for the whole year. I would like to recreate this in Airtable. Right now, I have a "Transactions" table in Airtable. The first field in the date, then a field for the category (Employee, SAAS, contract, office), then a field for the first subcategory (USA salary, PH Salary, Intern,

Hi all,I am a Campaign Coordinator and I use Airtable to help with my reporting. I currently have every campaign uploaded within Airtable for the entire fiscal year. However, I am looking to filter a bar chart extension to only represent the campaigns for quarter 1.For example, I have a table that represents the campaign goal for each campaign. I created a formula: {Campaign Count}/{Campaign Count Rollup (from Goal) (from Label)} *1However, this represents the percentage of total campaigns for each goal. I am looking to filter the bar chart to represent only the campaigns for Q1. Does anyone have a solution to this?Thank you in advance!

We have a base that has start date and end date columns. Need a formula that counts how many days worked in the month on that project. Anyone done this before? 

I think this is a formula question .... Maybe extensions or something. I have an interface with two key components: attachments and comments.  Since there will be many attachments at any given time, I want to add a data field that looks up/displays only the last uploaded attachment. How can I accomplish this? 

I have two tables, one with a list of payroll dates(vertically) for a company and another that is a payroll download that includes payroll dates(vertically), gross pay and employee information. In excel I used the sumif formula to say in sheet 2 sum gross pay for all the rows that equal the payroll start and end date in sheet 1.Example: There are 10 employees so there are 10 rows with different employees names and gross pay amounts in sheet 2. In sheet 1 I have a single row with the start date 1/1/2023 and end date 1/15/2023. I want to be able to to say sum the gross pay for all the rows in sheet 2 that have a start date of 1/1/2023 and end date of 1/15/2023. I need these dates to be referencing something though so that it will sum different date ranges based on the row it is in. So if the next row down in sheet 1 is 1/16/2023 and 1/31/2023 then it will sum all the gross pay amounts in sheet 2 for that date range. I tried the rollup but it is not dynam

I am trying to do a formula where I have a row with numbers (=calculation field/column) and depending on the number a certain text value (Tier 0 / Tier 1 / Tier 2 / Tier 3) should be the result in my formula fieldTier 0 : 500,000 - UnlimitedTier 1: 100,000 - 500,000Tier 2: 40,000 - 100,000Tier 3: 500 - 40,000I tried several approaches but none of them worked, I always get an error message.IF( {Calculation} < 40000, "Tier 3", IF( {Calculation} < 100000, "Tier 2", IF( {Calculation} < 500000, "Tier 1" , IF( {Calculation} < 100000000, "Tier 0"))))ORIF({Calculation} < 40000, “Tier 3”, IF({Calculation} < 100000, “Tier 2”, IF({Calculation} < 500000, “Tier 1”, “Tier 0”)))ORIF({Calculation} < 40

Hi, i have a formula field named "Jenis Parts Regex" where its value is "15.9mm Karet stabil FR RH/LH ES1|05".I want to extract anything that matches "end_of_name" field, in this case "ES1|05", and the result shall be in "regex replace" field.But what i got there is "15.9mm Karet stabil FR RH/LH |". Why am i getting "|" ?Here is my formula in "regex replace" field:REGEX_REPLACE({Jenis Parts Regex},end_of_name,"")Please helpThanks 

Hi there!I am trying to create a formula that looks at an entry (single select) and if that entry is = A, B or C say "Blabla" and if it says anything else say "Haha".I have hence tried to nest OR() into IF() as followed:IF(entry=OR("A", "B","C"), "Blabla", "Haha")I am not shown any error but the formula result is wrong (It says "Haha" when it should say "Blabla').Any thoughts?

I am trying to build an automation or formula for this scenario:Student #1 has been enrolled in school from the dates 7/1/2018-6/30/2023. I have a field in this table (Students table) for Enrollment date (7/1/2018) and graduation date (6/30/2023). I need the field " School Years" to populate with the respective school years this student was in school for. Ex: SY 2018-2019, 2019-2020, 2020-2021, 2021-2022, 2022-2023 - this is a multiselect field. This will be unique for every student, as their enrollment and graduation dates are varying...I have build a table named School Years and have the start and end date for each school year as a record. I would love this to be automated so that every time we have a new student and their graduation date changes, it will update what school years they were here for.This seemed so easy to do in my head and I have tried every way to get this to work, but I cannot see

Hey all - I'm running a formula column with a lot of IFs in there. Only 1 out of the 6 possible responses returns a text field, the rest are date fields.The dates come out in a very ugly way, this is how it looks: 2023-07-12T23:06:37.000ZIt basically makes it impossible for me to use / property view.After some playing around with it, I've understood that the issue here is with the fact that one of the values returned is a text field.However, the way I have the formula and the intended use. Unfortunately, I can't do anything else but have a text in one of the IF's (since it's the if true, not false, that I could just leave empty).I've tried using the time parse formula like this: DATETIME_PARSE(the if formula), but the result is an error.How do I make this work?

Hi I want to extract a portion of a text from a google drive share link.The format of the drive link is like this:https://drive.google.com/file/d/1IklI1OAW8XLtTKDfwW75BNcve8z8xQ57/view I want to extract the text after "d/" and will end at before "/view". So the intended result should be "1IklI1OAW8XLtTKDfwW75BNcve8z8xQ57 "Any ideas how?Thanks a lot!