Formulas

Hi folks, Formulas aren’t my strong suit, but I’m hoping there’s a way I can do this. Essentially, I’m helping my school manage teacher assignments, so I’m building a simple database to help me organize and visualize this data. I have two tables: 1 for Courses and 1 for Teachers. I have them linked together such that if in the Courses Table I type in a teacher’s name, then that course will show up on the Teacher’s tab (and vice versa). In the Course tab each course has a numerical value assigned to it based on the number of times per week they meet. What I would like is a column that tabulates the total number of periods assigned to a teacher that is calculated based on the courses assigned to that teacher. Does anyone have any formulas that can do it? I was hoping a lookup field would be a sneaky shortcut, but no luck. See my attached screenshots (I’ve cut off the teacher names field for privacy’s sake):

I’m using IF Statement with return numbers for two conditions: If it’s a car, one value, if it’s a motorcycle, another value. Everything is ok about that, however, when trying to multiply the presented result, it shows #ERROR!, how to solve it?

I have tried multiple Nested If statements to achieve this result: This is my ultimate goal: If “Date of Last Contact” is blank then return blank. If it is not, then if “Days Since Contact” is <3 then return “ :heavy_check_mark: Less Than 3 Days :heavy_check_mark: ” If it is not, then if “Days Since Contact” is <5 then return “ :warning: Almost More than 5 Days :warning: ” If it is not, then if “Days Since Contact” is >5 and Status Does Not = Revisit Later then “ :x: Greater Than 5 Days :x: ” If Status = “Revisit Later” and “Days Since Contact” is >30 then :x: Time To Revisit :x: If not then leave blank. IF({Date of Last Contact} = BLANK(),BLANK(),IF({Days Since Contact}<=3,“ :heavy_check_mark: Less Than 3 Days :heavy_check_mark: ”,IF({Days Since Contact}<=5,“ :warning: Almost More than 5 Days :

Is it possible to create a formula that tells you how many of a specific day of the week are in a month? Such as how many Wednesdays are in this month? I have a specific deliverable that needs to be done every Wednesday. I’d like a formula that would help me see how many of these are left in the month. Thanks!

I have three columns in my table. Lat/Long Combined - Example: 18.194608875237545, -63.08524490907154 Latitude - Example: 18.194608875237545 Longitude - Example: -63.08524490907154 The formula I’m using in the {Latitude} column is: IF( FIND(",", {Lat/Long Combined}), LEFT({Lat/Long Combined}, FIND(",", {Lat/Long Combined})-1 ), {Lat/Long Combined} ) The formula I’m using in the {Longitude} column is: LEFT( RIGHT({Lat/Long Combined}, LEN({Lat/Long Combined}) - LEN({Latitude}) - 2 ), FIND( ",", RIGHT({Lat/Long Combined}, LEN({Lat/Long Combined}) - LEN({Latitude}) - 2 ) & "," )-1 ) When there isn’t a value in the {Lat/Long Combined} column, the {Latitude} fields are blank, but the {Longitude} column is populating #ERROR! Any help would be much appreciated. Thank you

Ok so I am trying to use an if statement to put in a date but then if it doesn’t meet the condition I want it to be blank. I keep getting an error. Here is my formula: IF({Sample Status} = Out, (DATEADD({Date Checked Out},7,“days”)), BLANK()) Can anyone help? Thanks

Hello, how can I extract the 6" and the 8" from these strings?

I am using a script feature to add data in the table. One of my field type is “DATE”. Data coming from the API is in string. Hence, its not allowing me to add the data in AT, showing error - Error: Field “zzfldrJZNkO8gUQl69P” cannot accept the provided value.

Seeking help with a “If” formula to create buckets in a new column. For example, in column A i have series of different percentages. IN column B i wish to create a formula and returns a “text” if it meets the criteria…for example, if A1 = <100, “<100”, ifA1 = 100to120, “120”, ifA1 = 121to140,“140” and so on. Hope you can help Cheers

I am using a script feature to add data in the table. One of my field type is “Number”. Data coming from the API is in string. Hence, its not allowing me to add the data in AT, showing error - Error: Field “fldqIRRqhaalc20cqo3” cannot accept the provided value.

Im trying to figure out how to make the insurance deduction for my drivers automatic but only once a week when I run their payroll settlement.

Hey all! Here’s my situation: I’m working on a table with multiple linked records, and I have a rollup that displays the MAX(values) of a linked date field. Here’s my problem: I want said rollup field to display the date ONLY when another condition is met. When I use only MAX(values), it works as expected, but when I add IF(Condition,MAX(values),BLANK()) as below, it returns either a blank cell, or a zero. Here’s what the rollup formula looks like: And this is the result I get: I know I could get around this by creating a separate column for the IF() statement, and another column to show the Final Study On-Platform only if the IF() statement is true, but that’s messy. Hoping for a cleaner solution as my base is already getting quite large.

I’m trying to create a formula field to denote project “wave” based on a date range. So, if the target completion date is between 3/1/2020 and 5/30/2020, this would be “Wave 1”, if the target completion date is between 6/1/2020 and 8/31/2020 this would be “Wave 2”, etc. I’ve made a few different attempts, but it doesn’t like my formula. Any ideas?

I have a field that is called Re-Exam date. Each Re-exam date is the first of a month in a year. I want to count the total number records of the Current Re-Exam Date (the current month), Next Re-Exam Date (Next month), and the previous month Re-Exam Date. So let’s say a person had: 20 Re-Exams for the Re-Exam Date of 4/1/2022 14 Re-Exams for the Re-Exam Date of 5/1/2022 8 Re-Exams for the Re-Exam Date of 6/1/2022. I can total these in a formula to 42. And when the current month is 6/1/2022, the window shifts so I get the total of 5/1/2022, 6/1/2022, and 7/1/2022.

Hello! I have a table that has multiple single select fields with the options of “Complete”, “In Progress”, and “To Do.” I was wondering if there was a formula to count the number of fields with “Complete” as the selected option and turn that into a percent complete of all the tasks. Thank you!

I am trying to count the number of key phrases in a list (text field). I found help on Split Text to Columns on how to count words, but am wondering if I can adapt the formula in such a way that counts phrases instead? Here are some screenshots! In the first image I would like record 1 to have a word count of 2 rather than 4. The second image shows the current word count formula.

Is it possible to extract the color name or hex code of an option in a select field? I have this select field and I want to create a filter view that we won’t need to continuously update as we are constantly adding new categories. Everything for Marketing will be color coded ‘hot pink’ no matter what. I want the filter to basically be ‘if the select field is this color, then show record in this view’. My thought was that I could use a formula field to extract the color, and base the filter off of that formula field. So if the select field is ‘hot pink’ the formula field would say ‘hot pink’ and the view would be ‘if formula field is hot pink then show record in this view’.

Hi, I am using the Kanban view to follow the application process of my students. I would like to see on every card how long this card has been in this stack to quickly see if a card has been in a stack for too long. And if possible, I would like to be able to quickly see on a card if it has been move rencently in this stack (with a different color for this card for exemple). Do you know if it is possible to do this ? Thanks !

Hi, I can’t seem to figure out what I’m doing wrong. I’m trying to pull the link for the last attachment added and the only way I thought to try was using Regex. I tested in regex101 here: regex101: build, test, and debug regex I’m using this in a formula field: REGEX_REPLACE({Image URLs},'.*\\((?=[^(]*$)','') and just get an error. I’m trying to extract from an attachment field: 190423-LMOps005Pool-Fitness0130_blue.jpg (https://dl.airtable.com/.attachments/cad9e1ef16011261083de0d3af0f372e/dbf5c759/190423-LMOps005Pool-Fitness0130_blue.jpg, 190423 LMOps005Pool Fitness0130.jpg (https://dl.airtable.com/.attachments/34893e2547f354b5377bd0ca0d39d118/e6877e1d/190423LMOps005PoolFitness0130.jpg

Hello lovely community! I have a table [Assets] with new entries being created based on video assets in a dropbox via a make(integromat) integration. thats working nicely. each file ends with a version tag “_v2205181930” which is _v YY MM DD hh mm - which i can extract, read and convert to a date format that airtable understands. each file auto-links itself to an entry in a [Main] table using a 3 digit ID number. this also works nicely. if i have two entries in [Assets] 101_SomeRandomWords_v2205181930.mov 101_SomeRandomWords_v2205182000.mov both linked to entry “101” in [Main] i want for any entry in [Main] that has more than one linked asset, to compare the date tag in each, find which is the newest and for any older entires tick a checkbox column called “Old” I cannot for the life of me work out how to achieve this having tried mutiple formulas, lookups, rollups and even poked aorund at scriptin

Good afternoon everyone. I’m trying to create a field that calculates the appropriate pay given the work type. In my case, there are 3 different work types, (1. Booked gig, Not booked gig, and Custom Rate). In case 1 where it’s a booked gig, the base pay is $125. If a team member surpasses $125 in sales, then they’ll receive a 20% commission on any sales after. (example, $500 revenue is generated, $500-125=$375*.2=$75+125=$200) In case 2, there is no base pay, but team-member makes 40% commission on all sales made. (example, $500 revenue generated, $500*.4=$200). In case 3, a custom rate is set and field is populated by custom rate given. I’ve been using the following formula, but my issue is that it’s still calculating the commission even if the commission benchmark is not reached for case 1. SWITCH({Work Type},“WAE - Club Booked”,(((Revenue-125).2)+125),“WAE - Club Not Booked”,(Revenue.4),{Custom Rate})

Hello, I am not a specialist in formulas and especially in IF formulas. I have a description column and I would like it to be filled with the content of my product description column fr when language fr (single selection) is selected, same for EN Thank you Mélissa

Hello there I’d like to create the hash (md5, sha-1, whatever) or a concatenation of two text fields in an Airtable table. I have absolutely no idea how to do that - while in Google script it’s super easy… Does anyone have an idea please? Thanks! Best

So im building a base for my trucking company. I am the owner as well as driver, I also have three owner/operators who are paid by percentage of the load gross. Im stumped on how to take myself out of those calculations - I need to track the money that I make but there are no deductions or percentages taken out of my money as im the boss :slightly_smiling_face: Also how to make a deduction automatic but it would come out of the weekly settlement for the driver, not each load. Thanks - Dale Clarke

Hello! I’m new to Formulas on. Airtable and would love some assistance. I have tried to figure this out through reading the channel and articles and watching videos. I haven’t found the right resource to help me so I’m hoping one of you can. I already have my NPS calculation on the bottom. I want to use the NPS formula and break it down by quarter and year. I already have a quarter and fiscal year conversion. I have tried multiple formulas and nothing seems to be working to add that into my NPS formula. Thank you!