Formulas

So I use airtable to organize content for my websites and it’s been great. I initially started using it to create a web archive for a artist syndicate that needed a place to put up all their media, which is where I first encountered this issue. When uploading to wordpress using WP All Import Pro I need to have a bare url, but airtable dumps context into the field when you query the url. I was able to get around this using a regular expression to search for the beginning of the actual url and exclude everything before and ignore special characters. That worked fine considering that I only needed 1 attachment per post. Well now, I’m doing something similar to my own photo/video site and have created a portfolio post type where I can upload multiple photos into a gallery for each post. Thus I have multiple attachments per record in airtable, which the regular expression does not work with since there are multiple urls. So my question is how can I get airtable to spit out

Hi,I have a few dates and want to generate the day of the week initial based on the date in a column.If the date is 6/29/2023, then it would return Th.I've tried If and Switch, but my formulas are definitely wrong I think because it is related to a date. Ideas?

use case, crop planning.Tables:1. crop reference (list of crops, planting times, etc.) this reduces having to put crop info (spacing)2. inventory (i can link to a crop and add the "variety" info, quantities etc.) this worked GREAT!3. planting schedule (date of planting, variety planted.) I just need to pull ONE variety by choosing from the varieties listed in the inventory. i want this to be fed by available inventory which i'll update regularly.so in the planting schedule, i want to enter date planted, then choose which one variety i planted using a single select that is populated with the values of the varieties. the tables are successfully linked and i have the lookup values, i just want to choose ONE variety per planting date. i think i might be linking to the wrong table? or formula needed? see attached...I can't find where "crop copy" is? 

I am working on a field that pulls a result based on a number range.   I aim to convert the number of days from a person's hire date and an incident date to a set phrase such as "Less than 3 Months",  "4 Months to 1 Year", etc.   My first field converts the difference between their hire date and the incident date to the number of days.   My thought was to create a formula that says if the number of days is between 0-90, then the result is "less than three months," and so forth.   My fields are "Hire Date", "Incident Date", & "DateDiff".  The field I need to formulate is "Length of Service".   What is the best approach to get my outcome?  

Here is my formula which I need to add a few more statements too.    IF(AND({LOS2} >= .0, {LOS2} <.26), "First 3 Months", IF(AND({LOS2} > .25, {LOS2} < 1.00), "Within 1 Year", IF(AND({LOS2} >= 1.00, {LOS2} < 2.00),&n

I'm having issues getting a formula to round down prices to the nearest $2.50.I've tried FLOOR() FLOOR({Price}, 2.5)and ROUNDDOWN()ROUNDDOWN({Price},2.5)but without any success. Thank you in advance.

We are using the "Groups" feature which is available on the enterprise tier, and would like to see what formulas are available when a group is used in a "user" type field. Example:Column 1: type User - selecting "Group A"Column 2: type Formula - automatically showing "Person 1", "Person 2", "Person 3" that are part of "Group A".Not sure if a formula exist to expand groups into users as shown above, so how to go from column 1=> 2? Many thanks.

Hi everyone I have a primary field that’s a formula, concatenating from other fields, date-name-contributor(s) The contributors field is a linked field, and the formula relies on the lookup field “contributor last name”. So if the contributors column is “John Smith, Jane Doe” the lookup would show “Smith, Doe”. However, the formula will combine the last names without a comma, displaying “SmithDoe”. I’ve tried making a new formula field that takes from the lookup, but using that formula field in the primary field formula returns the same result. Is there any way to get the data in a linked field to be used in a formula with comma formatting? Thank you!

I am building a database for an investment firm where they purchase and sell investments. I have a column that calculates the multiple earned on an investment. For example, if cost = $100; proceeds on sale = $150 (proceeds / cost) then the multiple for this investment is 1.5x.Each investment's multiple is calculating just fine but I cannot figure out a way to calculate the firm's total multiple as it is not simply a sum of the multiples, it has to be recalculated at the firm level (i.e. sum of all proceeds on sale / sum of all costs). I want to be able to calculate this number based on pivots/groups/filters. For example if I wanted to know the multiple based on investments made in a certain geography or industry.Attached is a screenshot for the number that I need to calculate at the firm level. Any ideas? I would love to just carry the formula that is in the column to the summary row.

Hi all, I am looking to create a field that will automatically create a string from multiple sources.I am looking to create a bar schedule and I've found that the Gannt view is best for this as it allows me to export as a PDF. My issue is that I'd like it to include more info in the description. Essentially I want the record's name, the start and the end date included. I was wondering if there is a formula that could automate this for me.

Hi I am new to airtable and trying to figure out the formula, but stuck on figuring how to accomplish the following.  We have the following fieldsDeal Value = X AmountNo of Payments = can be 1 or more If its 1 = then we need the formula to be (Deal Value / 1) = goes in Payment #1 Amount ColumnIf its > 1 = then we need the Deal Value / No of Payments = go to multiple Payment Columns (Payment #2, Payment #2....... Payment #12) We have created multiple columns with a formula as Payment #2, Payment #3..... Payment #12 with the formula as {Deal Value}/ {No of Payments). However, if the No of Payments is 1, this scenario replicates for that row. We like to write a formula for the payment fields (2-12) which can derive the payment amount by IF No of Payments = 1, then "0" ELSE  {Deal Value/No of

Wow, I swear I've asked this AND seen it answered, but can't find it. I've created a junction field that contains the sponsor name and a single select field for the year: On the table where the sponsors originate, I want to show the MAX year. I've tried lookup and rollup and various suggestions from the forum, but I always get "0." Here's the current set up, based on a suggestion from @kuovonne in a previous thread. Instead of a rollup field, I have a lookup, then a formula to output MAX.The lookup is great, but you can see the formula always shows "0." The formula I'm using is MAX(ARRAYUNIQUE(ARRAYFLATTEN(

We interview students and each student goes through 2 interviews by 2 different interviewers. Each interviewer has their own column with their interview time. See an example. If we take the interviewer Ross as an example, how can I get to the point where I have only one column of the interviewer's name and his interview hours? I want to get to this view- 

I’m building the process using a tally form and airtable.1. User fills in a form that includes a question as follows - when do you want to start the challenge?2. The answer which is a date is feed into airtable (formatted as friendly date, time and time zone)3. Then, I want to sent an email reminder on this date (from point 2) at 11 a.m. How can I do this in airtable? i tried using a formula but couldn’t find one that gives me the same time output - at 11 a.m.Thanks,Jacqueline

Hi Everyone,I'm new to Airtable, so please forgive me if this is a silly question. I'm a big Google Sheets user and trying out Airtable for the first time.I have two tables:ContentTextUserDateIDWord ListWordClubWhen a new item is added to "Content", I would like to know if the field called 'Text' contains any word listed in my Word List (separate table).Example:"My favourite player is Cristiano Ronaldo" is added to Content.I would like the "Club" value in my Content table to update with "Portugal" based on the mention of Ronaldo (which is in my "Word List" table).From the reading I've done, I may need an Automation or a Formula - I'm unsure.Thanks in advancePaul

Hi everybody! Is there a possibility to somehow combine two Multiple Selection fields through an Option?In one Multiple Selection field I have Companies (A,B,C,..). In the other one,I would like to chose ONLY from the matching Bank Account for them (for company A  from A1, A2, A3, for company B from B1, B2, B3, ...) Is this possible somehow? Thank you all for all kind of helpGrimi

Hello,We are running a time study with our teams and we are bringing our data into Airtable from a time tracking tool.We are running into a scenario where a designer could have multiple time stamp entries for the same task on a project. The problem is we need to be able to sum up like project task records to get the overall total time they spent doing the task, thus creating a new record with the summed time number.Is there a way Airtable can do this? I started looking at rollups and formulas and I am not too sure where to start or if I am going down the right path.       Thank you!

Hello AT Community!  Today I am bringing you my biggest challenge yet!  I have been banging my head over this for two days and I cannot make heads nor tails of it lol - I'm hoping you guys can give me some possible solutions to make this work!  Here's the situation:An organization has staff members and volunteers.  They want to be able to record and project their staff and volunteer growth.  They track these metrics:# StaffStaff LongevityStaff Replication Rate# VolunteersVolunteer LongevityVolunteer Replication RateLet's just focus on volunteers for simplicity!  In Year 1, they start with 0 volunteers.  Staff recruit volunteers and by Year 2, they have 5 volunteers.  Those volunteers will serve an average of 2 years before moving on.  In one year, roughly half of their volunteers will recruit a new volunteer.  So in Year 2, our 5 volunteers will recruit 2.5

I have a column of case numbers. The numbers can be made up of a number only, for example, 832, or a number with a slash and a number after it, for example, 832/1.I need to create a function that searches for all the numbers that do not have a slash and adds a slash and the digit 0 to them. For example, case number 832 will become 832/0.In Excel, this can be done with the function IF(ISNUMBER(SEARCH), but this is not supported in Airtable. Do you have a suggestion on how to do this?

I am attempting to filter my call list based on how many concurrent Voicemails I receive from a prospect - these are contained in a Rollup field called 'Call Result' - I've created a new formula field as follows: IF(FIND("Voicemail, Voicemail, Voicemail", {Call Result}), 1, 0) HOwever I am always getting 0 back even when Copy/pasting the values in Call Result clearly shows multiple Voicemails that should be found, here is a random copy/pate from the rollup field: Voicemail, Call Me Back, Call Me Back, Appointment Approved, Voicemail, Voicemail, Voicemail, Voicemail, Voicemail, Voicemail and yet this one is coming back as 0 from the Formula. Any idea

I am attempting to sort my leads by how many concurrent Voicemails I've gotten for a prospect (so if someone gets 6 Voicemails in a row they are a lower score than someone that's only gotten 3). I have a Lookup field called 'Call Results' which displays all the values related to a Contact's call results from the Touchpoints table like so:Voicemail, Call Me Back, Call Me Back, Appointment Approved, Voicemail, Voicemail, Voicemail, Voicemail, Voicemail, VoicemailThe challenge I am facing is extracting the amount of HIGHEST CONCURRENT Voicemails as an integer to use for sorting. So for example in the example above there are 7 concurrent Voicemails as the highest amount (note I am not counting the first Voicemail since that doesn't descore a lead but higher Voicemail numbers mean a lead is going cold). Any input on how I can achieve my lead scoring goal or alternative approaches would be much appreciated.Thank you!

Hello!I am trying to use a formula that will look at the resources that are selected for the job to see if they are double booked.The formula will need to look at the resources column and then also look at the start and end dates column to see if the resource is booked on two different jobs on the same dates that it marks it as true or something so I can filter for conflicts.In the attached picture both of those rows should be marked 'TRUE' in the formula field because the gray van is double booked over a period of the same dates.Can someone help? Thanks!

I've been successful using Airtable data with quickchart.io to create charts that can be viewed in airtable or placed in Documint templates. What I'm trying to do now is "dynamically" code the chart inputs vice "hard coding"Specifically: I would like to select the fields to plot using a multi-select field and have that dynamically encode the chartURL. Example: FieldsToPlot includes 4 items (Units, Rate, Shared, Credit)- and from this I need to dynamically create this code:"&data1=" & ARRAYJOIN({Units}, ",") & "&data2=" & ARRAYJOIN({Rate}, ",") & "&data3=" & ARRAYJOIN({Shared}, ",") & "&data4=" & ARRAYJOIN({Credit}, ",")I can use substitute() to alter the data from FieldsToPlot- but I'm stuck on incrementing the number since it won't always be 4 items. I've thought of using a count function to determine how many items, but I'm not sure how to increment the number in a formula without using a loop.

Can anyone see what I am doing wrong here.Two fields, one date (as text type) one time (as text type) - The formulae field SHOULD make these into a single date/time field - This is the formulae I have, but I get errors on any dates with two characters as the day - I have tried various formatting DD/MM etc with no joy!. DATETIME_PARSE(CONCATENATE({TU Start Date} & " " & {TU Start Time}))

I managed to write a grammatically correct formula that I thought encompassed my query, but I'm getting a #ERROR! on the output.My aim is to add a conditional statement to the formula @AirOps was kind enough to format for me -SUBSTITUTE(IF(ISBN13!="", CONCATENATE(ISBN13, "5", {US Price})), ".", "")-where if {US Price} was less than 4 digits, it would add a 0 after the five, before the price. (Ex. if the price is 9.99, the output should end in 50999 instead of 5999.) This is what I came up with: SUBSTITUTE(IF(ISBN13!="", CONCATENATE(ISBN13,"5",{US Price},".","") &IF