Formulas

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.

Hello! We operate in an industry dealing with perishable goods, where the timing of receiving products (INBOUND) and the customer's requested shipping date (OUTBOUND) are crucial factors. We are trying to create a base structure/formula so we can keep track of inventory.Our primary challenge is to accurately determine the current inventory of raw materials on any given day. This inventory depends on the items we currently have on hand and any anticipated future deliveries of raw materials (INBOUND) for the upcoming week, provided they arrive before the customer's designated ship-out date (OUTBOUND Ship Date). Furthermore, our system must consider existing customer orders placed for the week and determine which incoming delivery of raw materials these orders will draw from.For example, suppos

Hello,I have a problem with a formula linked to a look up. I have a table with my banking transactions (linked to my bank) and another with forecast expenses. I did a look up to get the bank balance and I made a formula (Bank balance-expenses) for the first line the calculation is correct but for the other lines it does not give me the balance calculated above. Do you have any ideas to solve this problem?

I have these two fields Formula on Contract Yield % is this:({Contract Rent PW}*52)/{Purchase Price}I need to replace the formula in the Contract Yield, to an IF formula.More likely:When the result on Contract Yield is 0%,   If Contract yield = above 1%, use Contract yield formula, but if it's 0% I need to use Appraised Yield Can you please help me what is the correct formula to make it work?I already tried (IF({Contract Yield}=0,{Appraised Yield}but it's not showing the correct results

(Similar to this post though with an additional layer)I have a table with Interactions. There's a Status single-select field and a Timestamp field (since this data is being imported from elsewhere) that shows when the data was captured in the original system.There's a second table named Opportunities. All the interactions with one individual are linked to their respective Opportunity record. I need a field in the Opportunities table that reflects the Status of the linked Interaction record with the most recent / max Timestamp.The solution to the post I linked above pulls the max date, which would work if all I needed was the most recent Timestamp date pulled into the Opportunity record. However I need to know the most recent Status based on the Timestamp.Any thoughts? I may be missing a simple

Muy buenas tardes,Estoy montando mi propio gestor de proyectos, estos proyectos quiero numerarlos con números consecutivos, pero que el primer número sea un número en especifico.Por ejemplo:N. de Pedido: P003245 (Esté sería el primer campo de la tabla)Además, al crearse un nuevo pedido debe de aparecer automaticamente el número consecutivo (en el caso del ejemplo superior, P003246).Extras de funcionamiento:Además, voy a utilizar el apartado de formulario, para introducir los pedidos de una forma más automática y sencilla (no voy a ser yo la persona encargada, y se necesita sencillez). Mi consulta sería, ¿Existe alguna forma de que en el Formulario el N. de Pedido (automatizado anteriormente) aparezca de forma automática? Sin tener que incluirlo a mano.Muchas gracias____________________________________________________Hello,I'm setting up my own project

Hi, I'm new to airtable and am trying to get a simple conditional VAT formula to work. I feel like I am circling the solution but I'm not quite there. Essentially I am trying to track various hires and purchases for work, some of which have VAT and some do not. I have a Gross cost but I need the NET cost (without VAT).I currently have a VAT Field that is a single select field with options None and 20%. I'm trying to write a conditional formula for the NET Cost that is based on the VAT field and essentially works as follows:IF {VAT="NONE", Gross AmountIF {VAT="20%", (GROSS Amount/120)*100I keep getting invalid formula and error messages and I'm not quite sure what I'm missing.Any help greatly appreciated! 

Hello all,I have a problem that I'm trying to fix, and I have not been able to fix it with the posts with related questions.I have a field with numbers that I want to round up to another field with numbers. I'll explain the situation:So it's about ordering products. I get an order recommendation let's say for product A this is 55. But the products are packed in boxes of a certain quantity (let's say 20), so I cannot order 55 pieces, I'd have to order either 40 pieces or 60 pieces (2 or 3 boxes of 20 pieces).Is it possible to use a formula to automatically round the number upward to meet the required amount (in the examples this should be 60)?