Formulas

Hi there, This is a common question that I wish it does not have to involve a formula.  But I guess it should.  Can you tell me the way to write a formula to specify how I can account the total number of active projects for one month (Feb 2025)?   I have a start date and end date.   Combo Request DateNeeded By DateDate format MM/DD/YYYY When I write the formula it is not capturing the correct records active in Feb 2025. IF(  AND(    OR(      DATETIME_FORMAT({Combo Request Date}, 'MM-DD-YYYY') >= '02-01-2025',      AND(        {Combo Request Date} <= '02-28-2025',        {Needed By} >= '02-01-2025'      )    ),    {Needed By} >= '02-01-2025'  ),  1,  0) Do I need to specify status on-going, complete, in progress?

Hello, Currently I have a date field called Date, I would like to make a formula that looks at this date and outputs the first Tuesday of the previous month.Example{date} - Shows March 13, 2025Formula - Should show February 4, 2025 (First Tuesday of Previous Month)I was able to find the 1st date of the previous month utilizing this, but I cannot seem to figure out how to get the first Tuesday. DATETIME_FORMAT(DATEADD(DATEADD({Date}, -1, 'month'), -(DAY({Date}) - 1), 'days'),"MM/DD/YYYY") 

Hi, I am fairly new to Airtable, so I am trying to learn how to do things more efficiently. I have a question about formulas.Is there a way to select a field based on an input without using the SWITCH() function? I’m thinking something like a FIELD_SELECTOR() (← this a made up function as far as I know, but maybe something like it exists?), where I input the text display_name of a field, and output the value for that field. Or is this something where I need to start learning how to integrate python, and airtable built-in functions are not powerful enough?Example:I am making a database with taxonomic information (ie. latin names of living things). I have all the taxonomic ranks from Phylum to Species as separate fields. I have a field called taxonomic_rank that indicates the lowest taxonomic rank in the row. I want to make a function to create a display name that outputs “taxonomy_name taxonomic_rank”. So functionally, A row that says Arthropoda in Phylum and nothing in the lower ranks

Hi, I want to be able to copy the result in one field into another single-line text file so that I can look it up. The first field is multi-select. All I want to do is convert it into a single-line text field. I need this to look up the text from another table. I need to do this because the first field is already a lookup in this table from yet another, and I need to be able to link some of the details from a third table altogether. I hope this is making sense. I don’t know how else to explain it. Thanks S

HiI am a novice using Airtable, but I am learning….slowly.  I have the free plan, and I’m trying to figure out how to add recurring events to my calendar easily. For instance, I would like to add a recurring event every Friday between two specific dates. Any help would be appreciated.

Here's a question for you all,If you have a check box that asks a question such as [Would you like to add this service?] Where checked is yes. How can I get a formula field to fill with a price result?i.e. IF({New Service}, ….{New Service} Being the checkbox field. What goes next? Thanks in advance.  S 

Hello, I want to know if there is a formula to identify a text within a link cell, in this cell conditions that a home meets appear. Example: unreleased, released, foundation, walls, roof, finishes, finished. I want the formula to identify if the cell evaluated says without releasing the result is UNSTARTED, if it contains released the result is IN PROCESS or if it contains finished, the result is TERMINATED. I have tried with the IF formula, but I have not succeeded. Thank you 

HiI’m creating on airtable a KPI for restaurant and my challenge is to find the right Formula that worksi generate the formula with the AI but doesnt workplease help regards 

Hey! We have a difficult airtable formula to create that outputs 1 or 0 based on how soon a date is. Could you tell me if you could help?This is the problem:We have a variable called {Deadline}, which is the deadline to respond to a job. We have a job board linked to airtable. However, this is also the deadline for us as recruiters do our work with the applicant. It's still up on the website even minutes before the deadline. So if someone responds, it's too late, which is frustrating for them. But we still would want them to apply in case we have the time.So we need AT LEAST 8 hours (a 9:00 until 17:00 workday) * 60 minutes (for more accuracy) = 480 minutes to do our recruitment work. So we need a formula that says: IF we have less than or equal to 480 minutes of work during work hours until the deadline, the formula should be 1 (indicating to us that we should give a warning on the website when someone applies that we might not make it but will try). If it is already closed it should

Hello. I’m very new to Airtable and need an assist! I have this formula in Google Sheets: =BYROW($G$15:$G,LAMBDA(i,IF(i="",,VLOOKUP(i,$F$1:$G$2,2,0)))) VLOOKUP references this lookup table: 1 UsedLikeNew 2 UsedVeryGood 3 UsedGood 4 UsedAcceptable 5 CollLikeNew 6 CollVeryGood 7 CollGood 8 CollAcceptable 10 Refurbished 11 New  If a number there matches a number in a column  starting in cell G15 (via BYROW), then it returns the category in the lookup table associated with that number. So the column might be: 357410 And the formula would convert these to: UsedGoodCollLikeNewCollGoodUsedAcceptableRefurbished What’s the easiest way to create this in Airtable? I also have a similar issue with this lookup table, but not with numbers: AMAZON_NA FBA DEFAULT FBM  So I have a column that says either AMAZON_NA or DEFAULT and want to convert these to either FBA or FBM. I used =VLOOKUP(F15, $A$1:$B$10, 2, FALSE) in Google Sheets. I assume the logic would be the sam

Hi,Which Language setting should be used to paste formulas, i mean here:  Several times I wanted to ask it on old forum engine, but then just selected ‘Javascript’ and postponed it until next time. Here it looks not so good, so maybe I’m just missing a better way to do it?Well, in my opinion, the Javascript was better in old forum as well, here it’s too much red, while red supposed to be warning/error color. But maybe it’s just a matter of taste and mine is far from perfect.

I’m working on a voting app, where there are tons of enhancements. There’s a ‘Votes’ field which is of type ‘User.’ Users can add their names to that field in order to vote for that enhancement. This is based on a simple, single table.I want to have an interface page that lists enhancements that have gotten votes, sorted by the number of votes each has gotten. So if I add my name to that Users-type field, the number of votes for that enhancement goes up by one, and the sorted list of enhancements by number of votes gets appropriately updated.I thought I could just do a calculated field with this formula: COUNTALL({Votes})However, that’s giving me a value of one (1) for each record, even though right now most fields have no entries and a few have 2 or three.I’ve only done pretty basic things with Airtable, so I’m hoping there’s a simple way to do this and I just can’t get google to find it for me. Any help you can offer would be appreciated! 

I need a formula to populate a field based on three other fields - where only one will have data. So in fields 2 - 4 below, only one will have data and I want that to show up in Field 1. Field 1 - formulaField 2 - Lookup from table 1Field 3 - Lookup from table 2Field 4 - Lookup from table 3

I use the software to keep track of all my craft projects as well as all my supplies. I am trying to calculate the price of the supplies used for my projects ("cost per item * # of items" kind of thing), but one of my fields has multiple values. When I try the below formula, it always returns a 1.0.{Price/sq in (from Vinyl)}*{Area of Vinyl Used (sq in)} Is there a way to have each value in the "price/sq in" field be multiplied by the "Area Used" field (which only has 1 value)?

Hello, I am new to Airtable and formulas. My current formula is thisIF({Full/Part Time}="Part-time", 0.5, IF({Full/Part Time}="Full-time", 1.0, 0))I want to add a condition that if another field has two selected clients, the part time value of 0.5 will be doubled.The Full/Part Time column is a single select field. The formula isn't coming up with any results. Any idea what I did wrong, or what's missing in my formula? Thank you! 

I have a table for payment processing. When new records get created with a certain status, I would like to find the biggest check number in the table and add 1. Not sure if this would be using automation or formulas but thought I would start here. Thank you!

Hello Airtable community!I inherited a pretty robust Airtable system from a predecessor and have been working to revamp it, but my formula skills are not as good as the person who came before me, and I've been struggling to get IF AND statements to work. The logic seems sound, but they keep returning empty cells.One of the ones I'm struggling with concerns a formula that should return 1 of 2 options if a cell has a specific entry. I can get a single IF statement to return the correct result, but as soon as I try to include an AND, it fails. Here's what I have so far:In my table, the formula field is 'Ship From' with the two options being 'Direct from Printer' or 'X Distributor'. I want the formula to return 'Direct from Printer' if another field - 'Role' - is one of 3 options: Marketing/Sales, Licensor, or Editor in Chief. All other Roles should return 'X Distributor.'So I put in: IF(AND({Role}="Marketing/Sales",IF(AND({Role}="Licensor",IF(AND({Role}="Editor in Chief","Printer","X

Hi there! I am trying to remove NaN values from a formula field where both positive and negative values exist. This is in an effort to clean up two extensions: One chart indicating the number of each value's occurrence and one number where I am trying to get the average of the values, but with NaNs included I receive an infinity. Here is my current formula:DATETIME_DIFF({Due Date},{Date Completed},'days')+IF({Date Completed},{Due Date} = "NaN", BLANK())Thank you in advance!

Hi,I'm trying to create a table to track income and the records feed from different tables using automations which are assigned a deal type as they're created. If they're type A the commission is 15% of the payment amount, if they're type B the commission is 10% of the payment amount and if they're type C then the commission field needs to return 0. I don't know if it makes a difference, but the payment amount field is a formula. I've tried SWITCH({Type of deal},"A", {Payment amount} * 0.15,"B", {Payment amount} * 0.10,"C", 0) But I just keep getting ERROR in the results. I tried looking here at nested IF formula and came up with:  IF(AND({type of deal} = ”A”, {Payment amount} *0.15,))IF(AND({type of deal} = “B”, {Payment amount} *0.10,))IF(AND({type of deal} = “C”, {Payment amount} *0))But there's clearly something bigger wrong there that I can't see because it says it's not a valid formula. Any suggestions would be hugely appreciated, thank you

Hi there,I'm looking for a formula to automatically calculate the Venue and Producer split of the Budget and Budget Committed as per the "Deal." However, at present, the "Deal" field is a text entry, and I wondered what the best way to achieve this is? Many thanks,Tom

Hi There, I know I should already know this but I always get nervous when dates are involved. I want to be able to highlight either a field or a row when a task in the row passes the deadline. I don't need to know any other information like how many days over it is, I just want it to be highlighted to bring attention to it. I have a [Date Due] column and a [Status] column. The idea is that the status column would be set to "On Time" or "Overdue" to allow filteringCan anyone tell me what formula I can use to trigger the change in the status column?Thank you for your time. S

I’ve been using Airtable for a while now, and while it’s a fantastic tool for organizing data and streamlining workflows, I’ve been contemplating how I can enhance my note-taking system using Airtable formulas. My idea revolves around integrating a simple online notepad feature within Airtable to streamline my workflow without overcomplicating things.Airtable’s versatility and customizability make it perfect for managing complex data sets, but I feel like an integrated online notepad could improve the way I interact with my notes directly inside Airtable. I’m thinking of using a set of formulas to create a simple, text-based interface where I can store short snippets of information, thoughts, or even checklists. This would eliminate the need to flip between different apps or browsers to jot down quick thoughts, making the entire process smoother.An online notepad typically refers to a digital tool or platform that allows users to write, edit, and save notes directly within a web browse

Hey there,Thank you for your time helping me !So i'm a mushroom farmer. I cultivate different batchs simultaneously. Each time I harvest one, I create a record for it, with the date of harvest, the batch harvested and the weight harvested. There is a tab dedicated to it.I also have another tab which contains more infos about each batch, and also a sum rollup of the harvests of each batch.My main metric to know if a batch is good is the yield, I compare the total weight of the batch to the amount harvested. For example, I harvested 10 times a batch, the total is 300kg of fresh mushrooms on a batch of 1000kg of mushroom substrate, hence 30% yield.But I'd like more complex infos, for instance I'd like to know on which specific date of harvest I crossed the 10% yield mark, the 20% ... So i could know for each batch after how many days I crossed the 10%/20% markI've tried some combos of rollup and formulas but nothing works so much, any ideas ? 

I'm new to Airtable formulas, so I would appreciate your input.I created a formula that I absolutely KNOW was working. But suddenly the field where the calculation was says #ERROR! now although it said 100% before. I can't figure out what has changed because I didn't change the fields used in the formula.I would appreciate your help.The field name is Sold v Booked %The formula is:IF({# of Winners Booked},IF({Number of Winners/GCs Sold},{# of Winners Booked}/{Number of Winners/GCs Sold})) The # of Winners Booked field is a Lookup field from another table. The Number of Winners/GCs Sold field is a Number field. I wasn't sure if the above formula would work because the # of Winners Booked field had 1, 1 in it as it's looking up multiple records.  So I was thrilled when the Sold v Booked % field showed "100%" which was correct since it was 2 that sold and 2 that booked..&

Hey everyone, I’ve been spinning my wheels on a simple issue for the last few days and hoping someone can put me out of my misery. I have the above dataset and hoping to split it up automatically using a formula. The data comes in via Zapier and the delimiter is always a ,. I need a solution that can run automatically. Few keynotes on the data; Could be anything from 1-3 records. They won’t always be the same The length of them will also vary FWIW I have only two records now and have formulas in place based on searching around. They are; Product ID A IF( FIND(",", {Product ID purchased}), LEFT({Product ID purchased}, FIND(",", {Product ID purchased})-1 ), {Product ID purchased} ) ``` Product ID B RIGHT({Product ID purchased}, LEN({Product ID purchased}) - LEN({Product ID A}) - 1 ), FIND( ",", RIGHT({Product ID purchased}, LEN({Product ID purchased}) - LEN({Product ID A}) - 1 ) & "," )-1 ) ``` [quote="Ryan_de_Metz, post:1, t