Formulas

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!  

Hey! I have 3 checkbox fields that indicate the completion of a task. I want to show completion of the tasks using the newly released progress bar on the Percent tab, but without having to manually type in the thirds (1/3, 2/3, 3/3). If I use a formula to sum up the checks as a third, then use another formula to make it into a percent, I still won't be able to get the bar since the formula field does not come with that feature. How do I get that % into the percent field without having to create an automation? Is that the only way, or is there an easier/simpler way to go about this?Thanks in advance 🙂   

Hi,I'm trying to figure out if there is a script or formula to allow for when cells are updated in a column of a Google Sheet to update a corresponding column in Airtable. Here's the scenario I have:I have a Google Sheet that is organized by client project and it tracks the number of project hours used. I'd love for when the corresponding client project's client hours are updated in that Google Sheet that it is updated in the corresponding client record field in Airtable. I see a lot of stuff for updating a Sheet based upon an Airtable trigger, but not the other way. And I know that there is a Google Sheets API to add a row, but that's not what I need either as no new rows are created, it's just a field being updated.Hopefully this makes sense. Would really love any guidance on this that someone may have.Thank you!Julie 

Does anyone know if it’s possible to generate a url in Airtable that would both prefill fields AND autosubmit the form. I would like to be able to send a client a link (generated from an Airtable formula) via e-mail, that once clicked, would submit an Airtable form automatically with prefilled information. In other words, clicking the link would take them directly to the form submission confirmation page. All they have to do is click the link (or button in the e-mail) and their form is automatically submitted. I know that this is now possible using Stacker.app, but I was hoping for a similar solution simply using e-mail. In Stacker.app they add "&autosave=1 at the end of the url, but this doesn’t seem to work for me outside of that app. I’m guessing that this can probably be done using a e-mail button or link with a Zapier webhook, but I was hoping to avoid that if possible. If there is something similar to the “&autosave=1” feature, that would be ideal. Thanks!

I'm tracking book publications and each of the titles in my database will have several dates for different formats - Hardback, paperback, ebook and Audio...I'm trying to create a formula that will automatically select the earliest of these dates so that I can use that to trigger something else.I've searched other questions and found this:MIN(VALUE(DATETIME_FORMAT({Date Field #1},'YYYYMMDD')),VALUE(DATETIME_FORMAT({Date Field #2},'YYYYMMDD')),VALUE(DATETIME_FORMAT({Date Field #3},'YYYYMMDD')),VALUE(DATETIME_FORMAT({Date Field #4},'YYYYMMDD')) )I feel like this is partway there but because some books don’t have all formats some of these fields will be blank for some titles and where that’s the case I just get an error message. Is there a way to tell it to ignore any blank fields?Thanks 

I have an airtable that has the following data: [ { "id": 46, "createdTime": "2024-11-22T06:48:11.000Z", "Campaign": [ "rec1OnuA36COKk2rC" ], "Status": "Posted", "Account": [ "recFO8uEMB7wZaSYw" ], "External ID": "3914", "Created": "2024-11-22T06:48:11.000Z", "Last Modified": "2024-11-22T19:42:20.000Z" }, { "id": 47, "createdTime": "2024-11-22T06:48:35.000Z", "Campaign": [ "rec1OnuA36COKk2rC" ], "Status": "Posted", "Account": [ "rec6lTjVaKUR6gSuQ" ], "External ID": "urn:li:share:7265617134745976832", "Created": "2024-11-22T06:48:35.000Z", "Last Modified": "2024-11-22T06:48:35.000Z" }, { "id": 49, "createdTime": "2024-11-22T06:49:14.000Z", "Campaign": [ "rec1OnuA36COKk2rC" ], "Status": "Posted", "Account": [ "rech7SZ12yAqOSdmS" ], "External ID": "18114831709420263", "Created": "2024-11-22T06:49:14.000Z", "Last Modified

Is there a way to see which link was used to create a specific record? We use AirTable forms for surveys and have lots of different survey links. I’m looking for a way to see the specific form link that was used to submit that specific survey. Is this possible with a formula?

We're a publishing company and we keep track of content that we publish over the course of the year with these different columns showing publish dates. I'd like to do 2 things:Create 1 column that combines the dates (combining them so that they remain separate would be ideal, but otherwise, it could just be text formatted like this "MM/DD/YYYY, MM/DD/YYYY").Then I'd like to have another column that shows the oldest date and a column that shows the most recent date.Another column that just adds the number of times the content has been published, so counts the number of fields with a date in them.The error you see currently in the far right field had the formula that I tried on my own which was:DATETIME_FORMAT({Publish Date 1}, 'MM/DD/YYYY') & "," & DATETIME_FORMAT({Publish Date 2}, 'MM/DD/YYYY') & "," & DATETIME_FORMAT({Publish Date 3}, 'MM/DD/YYYY') & "," & DATETIME_FORMAT({Publish Date 4}, 'MM/DD/YYYY')A screenshot is attached. Thanks in advance!

Hello. I'm trying to create an IF formula that returns some text if the date field is a specific date. Each date would need its own specific text.e.g. If my date field was 05/01/2025, then the IF formula field would return specfic text (e.g Day 1).The bit I'm struggling with is doing this with lots of IF formulas in the same formula field. (I need about 47 different dates each returning their own specific text. Is this the easiest way to do this or am I missing a trick somewhere? Thanks in advance.