Formulas

Hello--We're developing an Affiliate program where the affiliates offer a free month on our app. We're gathering new user data from Typeform forms that are automatically dropping in the information into different Airtable sheets as shown below.I'd like for all of the new users to then be put into another table that I can sort by start date, but I'm not sure how to do that.  Thanks in advance!

Hi everyone, I’m facing an issue with counting linked records in Airtable, and I’d love your help! I’ve created two tables for managing group interviews:1. “Entrevues Dispo” (Interview Slots) – Tracks the availability of each interview slot.2. “Entrevues Inscriptions” (Interview Registrations) – Stores candidate registrations linked to the available slots. In the Entrevues Dispo table, I have a linked field called “Entrevues confirmées” (Confirmed Interviews), which pulls all the candidates linked to a specific interview slot. The relationship works, and I can see the correct linked records. The Problem: I’m trying to calculate the number of candidates per slot using COUNTA({Entrevues confirmées}). However, it only counts 1 record, even though there are multiple linked records (e.g., 2 or 3 candidates). I also tested with a Rollup field using ARRAYJOIN() and confirmed that all linked records appear correctly (e.g., emails of all candidates). Despite this,

I am new to the free version of Airtable which I am using to keep track of my craft stamps. I have at the moment around 500 items which have sentiments listed. When I try to find a particular sentiment I search for eg has any of, but it gives me "enter comparison value" and all the sentiments I have created. What I want is just a search box so I can just type just one word eg happy, so all those sentiments are found. What have I done wrong please and how can I rectify it.

Hello, I am trying to get this to work but all I get so far is a massive headache haha 🙂Context : organizing automatic reminders to clients we're meeting at a trade show. On the day of the meeting, I want to send a reminder to the invitee at 8:00 local time (and of course this local time changes depending on where the trade show is) to tell them they have a meeting with us that day.I have two tables.> One for trade shows with Trade Show Name (linked to the other table) and various fields, one being a number field providing the hours difference as well as a time zone field 'Europe/Paris' for instance, this being the time zone where the trade show takes place. > One for meetings with plenty of fields, including one that comes from our booking tool (Calendly) and where I've set up a webhook and the meeting time always comes as UTC. I have there also a lookup field repatriating the hours difference as well as the timezone field.How do I create a field that will set the t

I have a base with two tables: Employees and Projects.Employees has a primary field of Employee ID and Project IDs.Projects has a primary field of Project ID, Project Manager(s), and Status.In the Employees table I want the field "Project IDs" to be automatically populated with the corresponding Project ID from the Projects table when the Employee ID matches any names in the Project Manager(s) and when Status is not "Completed".Sounds like it should be simple. But I can't seem to figure it out. Thanks for any assistance! 

I created a form for our rental car contract in Fillout. Everything is populating correctly into airtable except the time. My issue is that I put the "date - time out" in the form eg 12/19 8:00pm. It will populate to airtable as 12/20 3:00am if I have the airtable time set to UTC. if I change the time in airtable to Denver (which is correct) it populates properly from fillout to airtable. HOWEVER, when I open the PDF everything in changed back to being a day in the future at 3:00am. I think it will work properly if I create seperate fields for date and time, which I can do, but it seems like it should be able to work by doing it the way I am. I'm sure this is a timezone code I cant for the life of me figure out. Please help, Thanks!

I have this formula for calculating the quarter and the fiscal year of when an invoice should be paid.Program Start date is 7-4-2024 (which is Q3). I want it to be paid the quarter after (so this one would be be paid Q4, FY24) but its showing Q3 - FY25. How do I adjust? the formula below for a "Program Start Date" of 7-4-24 to show that it should be paid Q4-FY24? IF({Program Start Date},"Q"&ROUNDUP(VALUE(DATETIME_FORMAT(DATEADD({Program Start Date}, 1, 'months'),'M'))/3, 0)&" - FY'"&IF(VALUE(DATETIME_FORMAT({Program Start Date},'M'))>=1,DATETIME_FORMAT(DATEADD({Program Start Date}, 1,'year'),'YY'),DATETIME_FORMAT({Program Start Date},'YY')))

Hi everyone, I need to create a single field that automatically shows the top 3 clients for each employee, based on the total number of bookings (records) they have assigned to them. Ideally the field would be dynamic and will adjust automatically. We are using this field to integrate with external platform, Glide.We have separate tables for clients, employees and bookings, and all utilise linked records for other purposes so we are very familiar with them. I'm just unsure about how to pull this data automatically, and if it can be done as a multiple linked-record field or if it would only work as a text formula.I understand a lookup field can be used to show the first or last 3 items based on the lookup source, however I can't sort this to show the highest ranked. Example below. I really need to show the top 3 clients for each employee, and be able to easily click into their details like a linked-record allows. Any ideas you have would be much appreciated. 

Hello,I have a project table and a milestones table. I want to update the status of my project record to “complete” when all the statuses of the linked milestones are also “complete”i have a roll up field which pulls back the unique array of the milestones, so I have all the milestone statuses comma separated as a text string, but I can’t figure out the formula to look at that field and determine if all the milestones are showing complete or not 

Hey there,Thank you for reading me !I have a small issue while using lookup fields within a formula.I've got 3 differents tables : projects, actions, animationsA projet is made of several actions which are also each made of several animationsThe project primary field is a formula : Year / Name (2024 / Orange)The action primary field is a formula : Project primary field lookup / Action Name (2024 / Orange / soccer)The animation primary field is a formula : Action primary field lookup / Date (2024 / Orange / soccer / 12/12)My issue is that in action & animation primary fields, some unnecessary "" are added, i get :Action primary field : "2024 / Orange" / SoccerAnimation primary field : """2024 / Orange" / Soccer"" / 12/12I have tried a few formulas but I don't get how I can remove them. Any ideas ?Thank you for your time, have a nice day ! 

I am looking for a way to parse a lookup field (Sprint Start Date) that contains multiple, comma-separated date values, and pull the most recent date into a new field. For example:IF --> Sprint Start Date = 2024-10-12, 2024-12-22THEN --> Formula Field = 2024-12-22 I have tried to use the following formula to parse the Sprint Start Date field and convert it, but it always returns the same value, or an error:DATETIME_PARSE(MAX(IF({Sprint Start Date}), VALUE(DATETIME_FORMAT({Sprint Start Date}, 'YYYYMMDD'))))Outcome #1: 12/31/1969 Outcome #2: #ERROR!I have also tried this, which gets me closer, but not quite what I'm after:DATETIME_PARSE(MAX(IF({Sprint Start Date}), VALUE(DATETIME_FORMAT({Sprint Start Date}, 'YYYYMMDD'))),'YYYYMMDD')In this case, I get one of two outcomes:Outcome #1: The correct date, minus one daye.g. If Sprint Start Date = 12-02-2025 the formula field returns 12/01/2025T

Hi, I have two tables for which I'd like create a formulaTable 1Feature NameFeature End Date Table 2Sprint NumberSprint Start DateSprint End DateI'd like to create a formula that checks if "Feature End Date" is between "Sprint Start Date" and "Sprint End Date" and if so, assign a Sprint Number accordingly. How can I achieve that in airtable?

“Client Table” contains clients in column A. One client to a row. Each client belongs to a group (Group 1, Group 2, Group 3, etc), which is listed in column B.  “Project Table” contains projects. The name of each project is in column A. Each project has one or more clients. One project could have clients from multiple groups. I’d like a column in the Project Table to list all the groups represented in a project.  Thanks!

I have a Projects grid and a Tasks grid.The Projects grid has fields for Project name, start date, and end date. The Tasks grid has fields for Task name, start date, end date, and reference to the Project name.  For each Project, I would like the start date to be the earliest Task start date among all the Tasks that reference that Project.Similarly, I would like the end date of each Project to be the latest Task end date among all the Tasks that reference that Project. Thank you in advance for any help!!  

Hey All,I'm trying to get this formula to produce one of three outcomes based on a products variance % from its target FOB, but for some reason it is not producing anything. I have used this formula multiple times successfully on other tables, but for some reason it is not working for this case. I have rewritten it a couple times and have checked the reference field to see if there are any issues and still can't figure out why its not producing an outcome.   Feel free to ask any clarifying question. Any help will be greatly appreciated!- Erich IF({Precost FOB % Variance} = BLANK(), BLANK(),IF({Precost FOB % Variance} <= -0.05, "RED: More than 5% off target",IF(AND({Precost FOB % Variance} >= -0.0499, {Precost FOB % Variance} <= 0), "YELLOW: 0% - 4.99%",IF({Precost FOB % Variance} > 0, "GREEN: Meeting or Exceeding target"))))

Hello AirTable community! I am trying to create a separate table that takes just the created by name, their email, and the number of approved records they have (in this case, approved is referring to any record in the original table that receives a check mark) from the create table, and populate just those values in the notify table. The values are populated in there through an automation (if record has a check mark in the create table, put these specific values into the notify table)The issue I'm running into is that in the notify table, if a user has multiple records approved, then their name will appear multiple times. I would prefer it to be their name, their email, and then a column with a summed up numbers of how many records they've had approved ( want it to be a real time moving number). I cannot figure it out for some reason. Please help, thank you!!Current OutputCreated By NameEmailNumber of RecordsSpongebob SquarePantsssquarepants@bikinibottom.com1Spongebob SquarePantsssquar

I have an excel spreadsheet with many calculations.  Given my current understanding of formulas, it is very much like excel so my question is below.Formula in Excel=IF(SUM(N22:Q23)<>0,SUM(N22:Q23),"")Field names in AirtableN22= シャトル台数(Shuttle Qty)Q23= 予備シャトル台数（Sp. Shuttle Qty）So my airtable formula should be as followsIF(SUM({シャトル台数(Shuttle Qty)}:{予備シャトル台数（Sp. Shuttle Qty)}<>0,SUM({シャトル台数(Shuttle Qty)}:{予備シャトル台数（Sp. Shuttle Qty)}),"")But I keep getting an error. Where are my assumptions wrong?  It is simple addition problemOrder Qty - ShuttleShuttle Qty28Spare Shuttle Qty2Total Shuttle Qty30

Hi everyone!I use Airtable to track event time logs (like a clock in/out system).  I attached the base structure to this message.How can I make the "totalMins" linked field (linked to "mins" in the "timelogs" table) automatically select all of the results instead of clicking them manually one by one?Thanks! 

Hi,I was trying to extend the below formula to give me records due 2 weeks from now so I modified this:IF(WEEKNUM({Flight Start},"Monday")=WEEKNUM(TODAY(),"Monday"),"Current Week",IF(WEEKNUM({Flight Start},"Monday")=WEEKNUM(TODAY(),"Monday")+1,"Next Week"))To this:IF(WEEKNUM({Flight Start},"Monday")=WEEKNUM(TODAY(),"Monday"),"Current Week",IF(WEEKNUM({Flight Start},"Monday")=WEEKNUM(TODAY(),"Monday")+1,"Next Week",IF(WEEKNUM({Flight Start},"Monday")=WEEKNUM(TODAY(),"Monday")+2,"Two Weeks"))) The formula was accepted but it is not giving me any results. Does someone know why?Do I need to trigger the formula? 

Hello all!I need help with a formula field. I believe I will need to use the IF function. Each week, I have to do math based on data input in 2 different fields and then multiply that number by a static field to get my total. I have to do this for 50+ records each week, so I'd love to save myself the time and energy and have my TOTAL field know which of my two fields it should compute against my static field. A screenshot is provided so you can see, and below is a little more explanation.Thank you for any help!Each week, some of the shows I am tracking are in preview performances and some are in actual performances, but never both at the same time. This number can change each week for either, and each show will start in previews then move to performances. I input the number of performances into the appropriate column, then multiple this by the {Production Seats} field to get my {Capacity}. Basically IF {Previews} has a number in it, multiply that by the {Production Seats} to get {

Hi, I am hoping that someone might be able to help with a nested IF Statement I have a field that determines the size of an advert. I want another field to list the price for the advert based on the selection in the first column.This is the If Statement I created, but something is not quite right because it always shows the value £0.00IF(  {Advert Size}='Full Size','£100.00','£0.00',  IF({Advert ID}='Half Size','£50.00',  IF({Advert Size}='Quater Size','£25.00','£0.00'  )  )) Thanks in advance for your help.  S

Hi,I have a field that has my clients' phone numbers. I also have a RingCentral account that I make phone calls from. I would like to be able to click on the phone number and make a phone call automatically from the RingCentral app. Does anyone know of a link I can use directly in Airtable to accomplish this?

Hey all, first post in here. Has anyone else noticed that when creating a view and setting the filters (particularly on conditions relating to a date field that includes a time) there is no way to set the filter to read hours rather than days? My use case:- I want to set up a view to trigger an external automation (zapier) exactly 48hrs before a specific date-time- I set the filter to DATE = number_of_days_from_now = 2 However, that doesn't work as it doesn't include time in the calculation. e.g. DATE = 2023-09-19 16:00:00So then I want the filter to register a new row at: 2023-09-17 16:00:00But instead, it ignores time, adding a new row at: 2023-09-17 00:00:00 (as soon as it reaches the correct day basically) despite this being 16 hours early (but technically correct by 'days' alone).I've tried using DATETIME_FORMAT to extract just the time - but there isn't a way to read that usefully in the view. So I'm probably going to have to do an awkward workaround

Sorry in advance if this has already been asked and answered a million times. Basic Situation - I am a bookseller, and my POS is a POS when it comes to order integrations. So, I have to create a bulk upload in a spreadsheet of hundreds of books per week. This is fine, it’s doable, whatever. But it does require a lot of tedious data manipulation which I am hoping Airtable can reduce. The Issue - I need to compare one CSV file (the Purchase Order) to a different CSV file (our item database) in order to see which books are new and need to be added to our system. If they are new, then I need to format the data to match our POS’s upload template. If they are reorders, I need to check if there has been a cost or price change. I can easily do this in Google Sheets with vlookup formulas, but, again, having to copy and paste and move things around between different worksheets has gotten old very quickly. The Question - Can I use Airtable to create a template that allows me to just upload the tw

What is wrong with my formula that it is producing "#ERROR!" instead of an empty cell when {Most Recent Completed Event Date} is blank? DATETIME_FORMAT(IF({Most Recent Completed Event Date}, DATEADD({Most Recent Completed Event Date}, 9, 'month'), ""), 'M/D/YYYY') It is working perfectly for the ones that have a date in that field (e.g., if "Most Recent Completed Event Date" is 5/13/2023, it gives me 2/13/2024). If "Most Recent Completed Event" is empty, I want the formula to leave the cell empty, hence why I put the empty quotes as the alternative option. But instead, it gives me "#ERROR!" I tried looking up similar questions but I'm stumped. Thank you!