Formulas

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-1

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 book

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')

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 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 o

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 "Tot

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 t

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

HiI have a formula that compares a rollup to a text field and then returns any new additions to the rollup. However it seems to be returning an extra character, could anyone help me figure it out please?From this:'Nelson Dance Festival- 2024, Royal Tunbridge Wells Dance Festival- 2024, Blackburn Festival of Dance- 2024, Dudley Festival- 2024'I would like it to return 'Dudley Festival- 2024'But I'm getting '4, Dudley Festival- 2024'The formula is this:IF(LEN(ARRAYJOIN({Rollup Festivals Entered},", "))-LEN({Static Festivals Entered})>0,RIGHT(ARRAYJOIN

I am building a schedule/time management base where employees can show their interests for shifts by adding their names to a (multiple select) shift calendar. Employees are unionized and have to be prioritized by the lowest monthly hours given and seniority order. The issue i have is the names are sorted by whom added their names first to the shift interest multiple select column. How can this box by sorted by 'lowest hours, seniority' instead.... OR could i get airtable to suggest the right employee from the list of avaiable employees on a shift?

I have a formula field that calculates a value (results a number) based on a lookup field (e.g. a date). When the lookup field is empty due to a missing input, formula field results with a NaN or ERROR.In that case, I want to highlight RED color to that record so operators understand that they should fix the missing input. Unfortunately, there isn't a way to enter a color condition statement such as "WHEN formula field IS ERROR, SET COLOR red".Is there a solution for this? @dashler78 

Hello Airtable Community!I'm currently working on implementing a dynamic ranking system for my gaming challenge, and I'd love some guidance on using Airtable for this purpose.Project Overview:I want to create a fluctuating Rank Rating like Elo/MMR in competitives video games.. For the sake of clarity lets call this player rating unit RP for Rating points.  RP are based on player performance (each new player start at 1000 RP).Each rank, like Challenger, Gold, etc., would have a specific RP range (e.g., Challenger: 2500 RP to 3000 RP).Key Components:Ranking Points (RP): A dynamic elo system that adjusts with player performance.Ranking System: Each rank defined by a specific RP range.Specific Questions:How can I set up a dynamic RP system that adjusts with player performance?Is there a

Hi 👋 I'm not an expert with formulas, but I think I'm overthinking this / making it more complicated than it needs to be.I have a CSAT (customer satisfaction score) form in Typeform that is being automatically sent to Airtable. What I'm trying to do is:a) calculate the CSAT score (# of 4/5 star ratings, divided by # of total responses, multiplied by 100)b) have it calculated every calendar week (so there should be a new rating every Monday)I can do this manually for now, but I want a way that each week, people can look and see what the score is, without anyone having to manually calculate it.I'm totally lost, although I think I am overthinking this. Any ideas? &#x1f605I've attached screenshots of the set-up currently. We have two sources for the feedback form, so there's one table for each of those.Then I have the 'Score' table, which has 5 records, one for each rating (1-5 stars), plus another

Hello,Want to strike through text in a cell if a given string is found in another cell in that row.  Is this possible?The cell whose text I want to strike through is already a concatenation of various other field values.  It's looking in a different field for the term "OMIT", and if that is found I'd like the text strike-through'd.The "OMIT" will be searched for in a single select field, but I don't think that matters since I've already successfully searched for and IF'd stuff based on single select text contents.TIA for any assistance.