Formulas

Hi all, I have a two-table base. The first table/screenshot below is for survey responses; each record is an individual survey response. The second table/screenshot is where the responses roll up for each survey. Is there a way within the rollup filed formula to achieve my highlighted desired result? I'm trying to avoid adding multiple rollup fields for each response type and then concatenating all of the information in a new column.    

I just started using Airtable and I'm trying to remove the date/time from a column or create a new column that has the date/time removed with just the remaining text. The column is linked to another airtable and it is using the primary field, so I don't think I can change it from there. For example, in the column it will have-2023-09-20T00:00:00.000Z Opinion on Policy2022-10-03T00:00:00.000Z Environmental NeedsI want to removed the date and time and have the column say-Opinion on PolicyEnvironmental NeedsThanks for the help! 

I am trying to create a URL to search Google Shopping for a specific combination of (Brand, & Product Title), however i am noticing that if either the Brand or Title has more than one word, the URL does not form correctly:Ideally I would use the Google Shopping ID for the product search, but that is a separate URL/search entirely. I also want to add UPC to this, but that shouldn't be a problem as a UPC is not two words (however sometimes multiple UPCs may be searched, so the same issue might arise).If anyone is able to advise, I would greatly appreciate any help!

I am trying to have a formula which give me the possibility to have on the same line two fields separated by a coma. This is tricky, because, i need also to have in the formula that if the field is empty if it not showing the empty space or make a break line.Right now I have this, IF({MARQUES}, " " & {MARQUES} & "\n") &IF({DESIGNER}, " , " & {DESIGNER} & "\n") &IF({DATATION}, "  " & {DATATION} & "\n") &IF({COLLECTION}, " " & {COLLECTION} & "\n") &IF({FABRICATION}, " " & {FABRICATION} & "\n") &IF({PRODUIT}, " " & {PRODUIT} & "\n") &IF({MODELE}, " - " & {MODELE} & "\n") &IF({MATIERES}, " " & {MATIERES} & "\n") &IF({COULEURS}, " " & {COULEURS} & "\n") &IF({DETAIL}, " " & {DETAIL}) &IF({GRIFFES}, " , " & {GRIFFES} & "\n") &IF({PROVENANCE}, " " & {PROVENANCE} & "\n") &IF({TAILLES}, " " & {TAILLES} & "\n") &IF({CONDITION REPORT}, "

Hi-  I'm a pretty new Airtable user, and have been trying to find solutions via the community posts, but haven't seen anything exactly addressing what I'm attempting to do.I am trying to populate a field with a number of hours to be worked, only if the month for that work falls within a date range.  In Excel, I was able to make this formula work:  =IF(Column's Month<=Completion Date,Average Monthly Software Hours,0)-IF(Start Date>=Column's Month,Average Monthly Software Hours,0)I tried to replicate the formula in Airtable that I successfully did in Excel, but it isn't returning the same values.I've attached a screenshot of the base as well as the formula I tried.  It feels like there's something wrong with how I'm handling the Column's Month formatting, but I can't figure out what...Many thanks in advance!-Robin

Hi allI'm trying to make a URL to link directly to show just images in an attachment field.I've created a shared view, and am happy making a formula which replaces the record ID to the correct record but I can't get the attachment ID programatically.  If I omit it from the URL (ie airtable.com/appXXX/shrXXX/tblXXX/viwXX/recXXX/fldXX/ then it directs the user to the full record page - I just want them to see the attachments so need the "att" IDAny ideas?Hope that makes sense!Andrew

HiI have a field called {Total Duration} where the user enters the length of a video in h:mm:ss format. This formula is going to be used to calculate the costs of video editing. We charge in 30 seconds units. 1-30 seconds is charged as one unit, but 31-60 is charged as two units.I have been using the following when {Total Duration} was set as a simple number fieldCEILING({Total Duration} /30) * 30This almost works. If I enter 32 it rounds up to 60, which is what I want, but if I enter 31 it rounds down to 30.  Any ideas how I can solve this? 

Hi there!I am getting start and end date as a text in one field.BUT format could be different in each record (mm/dd/yyyy OR yyyy-mm-dd)I want to find a formula to split A field (which could be formatted in two ways) into start (B) and end (C) datesTime shown in B and C is not relevant. Most important to have it formatted as date.ABCTest Dates (single line text)Test Start Date (Formula Formatted by ISO)Test End Date (Formula Formatted by ISO)11/28/2022 - 12/23/20222022-11-28 02:00 2022-12-23 02:002022-11-28 - 2022-12-232022-11-28 02:002022-12-23 02:00Thank you in advance for any input into this!

The Utilization Summary works differnt than I expected, and I can't figure out why.I think this image summarizes my question: In short:I have 5 working days (40 hours) per employee as ''Available hours''And my tasks are split into working days, where ''Hours required'' is calculated as [WorkingDays]*8Now in the example above I have a ''task'' of 15 working days, spread over 3 weeks, resulting in 5 days per week. I would expect to get three weeks with 100% utilization, but instead, I get three weeks with 300% utilization. It seems like Utilization Summary takes all Working Days for every week the task occurs in.How can I solve this?  

Hello! I figured this out before and accidentally cleared it a while back without noticing. For context, I am trying to find the total cost associated with ordering additional curriculum books. In the form submitted, customers can select additional books for purchase at $45 for K-2nd and $60 for 3rd-6th.I am trying to find the sum of:multiplying the following columns by $45: Additional K English, Additional K Spanish, Additional 1st English, Additional 1st Spanish, Additional 2nd English, Additional 2nd Spanish multiplying the following columns by $60: Additional 3rd English, Additional 3rd Spanish, Additional 4th English, Additional 4th Spanish, Additional 5th English, Additional 5th Spanish, Additional 6th English, Additional 6th Spanishfor a total cost to appear in "Additional Books Purchased:" ex: 1 additional K Spanish & 1 additional 6th English would equal $105 in the additional books purchased column. Any help is greatly appreciated! 

Hi there, I'm essentially trying to set up two overwrite fields in my formula. So if field 1 is blank, then use the data from field 2, but if field 2 is blank, then use data from field 3.I have the formula for one overwrite which works fine (see below), but I can't seem to work out how to add another condition to it. IF({Company Invoice Contact}, {Company Invoice Contact},Company) I want it to work like;IF Invoice Contact Overwrite is blank then use Company Invoice Contact but IF that is blank, then use Company.  Any help would be much appreciated.

Hi,I want to create an IF formula but the result I get is not what I expectedHere is the formula: IF({Date effective de fin du cadrage (from Date effective de fin du cadrage)}<{Date estimée de fin du cadrage (from Date estimée de fin du cadrage)},"In time","Late")I want to get: "In time" if the effective end date of the scoping is before the estimated end date of the scoping and I want to get "Late" if the effective end date of the scoping is after the estimated end date of the scopingHowever, I'm getting "Late" whereas I should get 'In time".Could you please help me? Thank you very much

I’m trying to use automation to copy a record and create duplicates of all the linked records in the original record. Here’s what I’m trying to do:Create a new record and update it with all the fields.Search the other table for all the records that are linked to this one.Use a repeating group to create a record in Table 2 and copy the information from each record that is linked to the new record.Unfortunately, I’ve tried so many ways, but it’s not working. Could you please help me?

Hi AT Community, I'm new to Airtable and ’m trying to create a formula that would display the Monday of the week / "week of" date for a given date. Monday is the start date of the week. I've tried formulas from other posts but when I add the formula to my field, it gives an error.The formulas I've tried are:DATETIME_FORMAT({Date},‘e’)+1,‘days’),‘M/D’))--   CONCATENATE("Week of ", DATETIME_FORMAT(DATEADD({Date},-DATETIME_FORMAT({Date},"e"),"days"),"M/D"))    --DATETIME_FORMAT(IF(WEEKDAY({Date Due})>1,DATEADD({Date Due},-WEEKDAY({Date Due})+1,'days'),IF(WEEKDAY({Date Due})<1,DATEADD({Date Due},-WEEKDAY({Date Due})-6,'days'),{Date Due})),'l')I really just want to get the Monday of the week when the record is created. So if the record was created on, say, January 19, 2023 - the Monday of that week would be January 16, 2023. And the field would display exactly "January 16, 2023."Thank you in advance! 🙏

Hi there.In a field, I have used the formula below: DATETIME_FORMAT({Datum gesprek}, 'DD-MM-YYYY HH:MM')&", "&{Traject begeleider} Weirdly, the outcome makes the time change by a few minutes. I can't figure out why. Setting timezone, or changing other aspect doesn't help.  Could someone give a solution for this? Thank you!

I am working with a subscription table and every row is an entry every time a customer subscribed to an account (It is possible for a customer to have more than 1 account). I have a field for Start Date and End Date, and I want to know the maximum account the customer had at some point, so I am thinking that if I would know which dates overlapped, I can then record it to a different table but is there any formula in airtable  where I can do that? 

I have the followign problem.In a cell I have a list of tags, separated by a comma, i.e.Apple, Banana, GrapeThen I have a control list of allowed tags, that isApple, Banana, StrawberrySo, the formula should eliminate the tags Grape is it is a now allowed tags, and return this resulApple, BananaAny ideas on how to obtain it?Thank you very much in advanceRegards  

Hello - Situation: Volunteers on the team have a 2-year tenure and when those 2 years are up, they can opt-in for 1 more year. My table shows their "start date" and then I have a column showing their "end date" using this formula DATEADD({Current WON Role Start Date}, 2, 'years') and have an automation that alerts me 60 days before that end date.  Need: What I'd like to happen then is after the Opt-In option is discussed and if the volunteer decides to add 1 more year to their volunteer work, a formula runs that gives a new end date.  Based on: What that said, I'd want the formula to run only if YES is selected from a drop down. Then I would assume we can take the tenure date (original end date) and add 1 year to spit out a new end date.  I'm not great at formulas and tried - IF({1+ Year Opt In}='yes', DATEADD(Tenure, 1, 'years'), " ") It doesn't give me an error but also doesn't populate a new date. Any insight?

I have a base used for tracking waiting lists for multiple events. A simplified version of the base for purposes of this question is here. Each record is one person’s entry on a waiting list for a specific event. Each record has a Status that starts out as “Waitlisted”, and then we change the Status to other values as we clear people from the waiting list for an event. We normally use a view that groups the records first by Event Name and then by Status, with records sorted by the Created date/time. This is the first view shown in the demo base. We take people off the waiting list for an event in the order that they signed up for the waiting list. We often have people ask us, “what number am I on the waiting list for X event?” Rather than having to manually count rows (which could be 100 or more) to answer this question, I want to have a field that shows each person’s sequential position on each event’s waiting list, based on the Created date/time. The mocked-up field called “Position

Hello,This table register the time my employes arrive ("Entrata") and leave ("Uscita") using an Airtable database.I managed to have a number that defines the total of hours my employes work during the day using DATETIME_PARSE in the "Worked Hour" Column. I need to sum those hours together in "Total Month" Column (first of all) and then make sure it does consider the month.I'll explain myself better, i would like something like this, using this image.At the end of the formula, i would like to SUM Worked Hours from Line 1 and Worked Hours from line 2, and show it in "Total Month" of the latest created row.As second step, If a new row is created, it checks if the current month in "Created" (i can do it with PARSE i think) is the same month of the row below:- If it's the same month, it automatically adds the value of the total hours in "Total Month"- If it's not, it sets the counting of "Total Hours" to 0 and then it adds the Worked Hour of the day My question is, is it possible to su

BackgroundI am using a "Search Records"-module in Make Automation to compare data in a scenario (=item for sale) against records matching specific criteria in Airtable (=user wanting to buy an ite, like a  "search request").Desired BehaviourThe search formula SHOULD  return the records where:1. Container Brand = Container Brand OR Container Brand = empty OR Container Brand = empty AND2. Height Value = empty OR  Height = empty OR Height Value = +/-8 of Height AND3. Weight Value = empty OR Weight = empty OR Weight Value =+/-10  of WeightIn plain language: Return those records where the Container Brand matches the customer preference AND container sizing is +/- close enough to the height & weight of the customer. If value is missing, then assume a match as the business decision is to avoid "false negatives", thus better inform the customer too many, than too few times.Formula: AND(OR({Container Brand}={{emptystring}},"{{52.Container.Brand}}"={{emptystring}},{C

How about creating a new field type ("Formula Script" or such), where Script (or at least some subscript) can be used for a field formula?  Or optionally allow Script in Formula field?

Hello,We are a vitamin supplement company and we buy ingredients from all over the world. We have 178 different ingredients which are used to create 17 products.  Sometime the same ingredients will be used for five different products.We want to create an inventory table that we will update manually when we receive or buy new ingredients, but decrease automatically when we dispatch ingredients that will be used to manufacture the product.For example, if we bought 20kg of magnesium, we need to add this to the existing stock on hand, ie. 10kg.  A new total of 30kg in stock.  When we manufacture the product SleepEeze we'll send 25kg to our manufacturer, and the stock should automatically decrease to 5kg left as stock on hand.  But I'll also take 2.5kg to manufacture CalmEeze, which should remove from stock on hand.  And so on.   I feel like this is so easy but I can't wrap my head around it. Thanks for your help in advance!

Hello, i'm trying to wrap my head around this particular problem.Main problemI have an Airtable column that shows the time someone has entered and another column that shows what time he finishes, i would like to find a way to create another column able to convert these times in hours workedSecondary problemIt would be nice if it also can considerate the night/day difference, for example starting at 11AM and finishing at 2AM EDIT: They are both Single Text fields Thank you very much for your help!

Hello Airtable Community,I'm currently working on an exciting project using Softr, where I'm creating a ranking system for athletes. Each athlete is assigned a unique score based on their performance in various exercises, and this score is then used to sort the athletes in Airtable.I have connected it already with tables in Softr and sorting based on that score gives me the result I wanted, but I would like also to display what's their position: 1st, 2nd … etc.Do you have any how to write a formula which will give me thei exact position on the leaderboard?Best regards,Konrad