Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,944 Topics
  • 18,246 Replies
5944 Topics
S
SidStuntParticipating Frequently
asked in Formulas
Time change after CONCATENATE formula

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!

40
S
V
VeeLawNew Participant
asked in Formulas
Finding out the maximum number of accounts

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? 

62
V
C
Cristiano_VolpiParticipating Frequently
asked in Formulas
Control text from a list

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  

64
C
A
Ashley_BNew Participant
asked in Formulas
Formula based on Single Select

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?

116
A
D
David_GoldsteinInspiring
asked in Formulas
Formula to dynamically calculate record sequence number in a sorted group

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

950
T
M
Maurizio_polverParticipating Frequently
posted in Formulas
Sum different values

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

151
Databaser
B
BastianbbNew Participant
posted in Formulas
Formular Error

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

41
B
Samir_Ghosh
Samir_GhoshKnown Participant
posted in Formulas
Suggestion: Add "Formula Script" field type

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?

00
Samir_Ghosh
M
MoniKNew Participant
posted in Formulas
Ingredient stock rollup

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!

143
Dimitris_Goudis
M
Maurizio_polverParticipating Frequently
posted in Formulas
Formula for converting time in hours ex. 12.30-16.00 = 3.5 hours

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!

20
M
Konrad_Par
Konrad_ParNew Participant
posted in Formulas
Formula for a position in dynamic leaderboard

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

141
Konrad_Par
K
Katy_RichardsParticipating Frequently
posted in Formulas
Formula returning extra character

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({Rollup Festivals Entered}),(LEN(ARRAYJOIN({Rollup Festivals Entered},", "))-LEN({Static Festivals Entered})))) What am I missing? 

02
K
F
FRANCOIS_PLAISANew Participant
posted in Formulas
Getting airtable to suggest from an list of available employees, the one who has the lowest hours

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?

60
F
N
ni5ni6New Participant
asked in Formulas
Color record based on formula field has ERROR condition

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 

122
N
M
mariuspittieNew Participant
posted in Formulas
Seeking Guidance on Dynamic Ranking System in Airtable

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 recommended approach to automatically assign ranks based on RP ranges in Airtable?What formula or automation can I use to ensure a player's rank changes automatically when their RP fluctuates?I've atte

760
M
M
maedhbhveedNew Participant
asked in Formulas
Trying to calculate CSAT score using Airtable (formula, rollup, linked records, count)

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 one for 'TOTAL' which I added in my efforts to calculate the score (it can be d

284
M
A
ADKNew Participant
asked in Formulas
Format Rich Text if (Search)

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.

342
A
4
4pineNew Participant
posted in Formulas
Future Inventory & Ship Out Dates

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, suppose we receive an INBOUND delivery of 200 units of raw material on Monday, with another 400 units arriving on Thursday. If a customer places an order on a Sunday evening and requests 200 units to be s

31
Dimitris_Goudis
V
VirginieNew Participant
posted in Formulas
Formula bank balance

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?

41
Dimitris_Goudis
I
Imee_LeeKnown Participant
asked in Formulas
Using IF Formula

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

182
I
ConnorTomasko
ConnorTomaskoParticipating Frequently
asked in Formulas
Field needed that displays most recent status based on date field

(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 solution here. @Ben_Young1 ?

395
ConnorTomasko
J
JuanArLoNew Participant
asked in Formulas
Formulas > Números Consecutivos > A partir de un número en Especifico

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 manager, and I want to assign consecutive numbers to these projects, but with the first number being a specific one.For example:Ord

311
H
C
cevison2New Participant
asked in Formulas
Conditional Formulas for VAT

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! 

684
C
U
UrbnJNew Participant
asked in Formulas
Rounding up to a certain number

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

332
U
E
ecosystemcNew Participant
asked in Formulas
Date Formula Help

I need help creating a formula.Basically I need this to do this:If there is an extension marked yes than the move-out date changes to how many day per the extend plus the move-out date. Ex if there is an extension original move-out date was 1/1/23 and there was 30 day extend the new date would be 1/31/23.If there is no extension then move-out date is move-out date. Ex: 1/1/23 is move out no extension then 1/1/23 stays the move-out. I would think this formula is it but it is not. IF({Is there an extension} = "yes", DATEADD({Lease End Date} + {If there is an extension how many days approved}, "days"), IF({Is there an extension} = "no", {Lease End Date}))

83
Dimitris_Goudis

Badge winners

Show all badges
Terms & Conditions