Formulas

In order to compute a change in a value between a prior record and a newly inserted record, is there a way to get the most recent prior value for a field in a table?Assume table is sorted (e.g., by DateTime).Ideally with a formula something like the pseudocode below where the “Prior_Value” and “Current_Value” are a number, such as: an amount, a quantity, a weight, a height, a length, et cetera.Delta_Value = [Prior_Value_from_Prior_Record - Current_Value_from_Current_Record]Thank you for any and all suggestions!Rick

HI guys, I’m trying to use the Concatenate function to help with my primary field names, but the date I’m pulling in from a ‘date’ field is a little messy. Here’s my formula... CONCATENATE(Client," ",'|'," ",{Month Date}," ",'|'," ",{Record Type}) And this is what comes back…Crystal Maze | 2025-09-01T00:00:00.000Z | Income - Forecast Is there a way of amending the date format within the concatenation formula?  Ideally it would look something like this…Crystal Maze | Jul-25 | Income - Forecast Many thanks in advance!

Hello everyone,Any clarity or information on this time and date issue would be awesome!I have 2 tables which are linked through a record. In each table, I have a formula field which produces a date. I need the dates to match. Unfortunately, the formulas are not the same. One is based on a date field in the first table and is a dateadd formula. In the second table, it is a createdtime formula. On the first day of operations, the date is submitted through a form to the date field in the first table. The formula field in the first table adds one day to this date so that process which happen on the second day are kept track of. In the second table which keeps track of the processes which happen on the second day of operations, I have the createdtime field. I need this field to match the dateadd field so that I can automatically link the records. However, something is happening with the formulas such that they are not the same day when I want them to be. Does anyone know how to fix this?I h

Hi there!I want to transform a ChatGPT output (30 words/names) into a multi-line column (30 lines) in Airtable. I'm using Make as my automation engine and its Iterator module. I'm having trouble finding the right array formulahere is the one I have: split(trim({{5.choices[].message.content}}); newline)It would be awesome if someone could help me!! 

Hello,I am working with a formula field that I have created to take a Select list value from Field 1 and change it into a number. Example:Switch ( {field 1}“Choice 1”, ‘1’,“Choice 2”,  ‘2’)The output of this is a text value of 1 or 2 not a numeric value. The tool will not allow me to use the following , the 1 and 2 turn red.Switch ( {field 1}“Choice 1”, 1,“Choice 2”,  2)Using the first switch function how do I now set the 1 and 2 to a numeric value?

Hello. I am working on a form to list specific medications, the vendor it originates from, cost, qty, etc. I’d like to be able to input the brand names of medications into the form and have the generic names of that specific medication auto-populate in a field below the brand name on the form and in the grid view. I’ve tried creating a separate table and inputting the medication brand and generic names in a list, but I cannot figure out how to use conditional formatting (or lookup) to populate the generic name in the form. Hopefully this makes sense. I’m struggling to describe what I’m looking for. Example: Single Line Text Field: Medication Brand Name - Adrenaline Conditional Field: Medication Generic Name (to auto-populate) - Epinephrine

Hello!I am trying to create a field that returns matching values from 2 other fields.In the screenshot below, ‘Budget’ and ‘Offered’ have the same options (multi-select fields). I would like to have the third field, ‘Budgeted and Offered’ that shows the matching options from those fields.   How can this be accomplished within airtable?

Hi, I’m looking for a formula that will flag when a name is appearing in different columns in the same row. This is to flag scheduling conflicts. Column A, Column B, Column C, Column DParticipant X, Participant X, Participant Y, Participant Z Here is my current formula for 2 columns:IF(AND({Column A} != "", {Column A} = {Column B}),"Duplicate")It works. But when I try to add more columns, it breaks down. Can anyone help? Or is there another method altogether?

I have an IF formula to populate a name column.  I’m putting in a substitute formula to remove extra spaces, and it appears to be accepted (no errors) but then it just vanishes.  It leaves the “Substitute” there, but not the info about removing the spaces.Here is my original formula:Here is the substitute formula showing no errors: When I hit confirm change, it reverts to this: Any help would be appreciated, I have no idea what I’m doing wrong.  (And apologies for the large screenshots, I’m unable to make them smaller for some reason).

Hi all,I'm trying to create a gantt where I could provide a start date for the first task and from there the start dates and end dates of all other tasks would be calculated automatically (the start date of a task is the MAX of the end dates of all its predecessors).I have a the predecessors as self Links to the same table. I have their max as a Rollup.I have the end date being the Start Date + Duration, using this formula:DATEADD({Start date},Duration/60,'minutes')and I have another field called "Actual Start Date" in which I intend to provide the start date of the first task manually.When I try to add the formula for the Start Date I hit the circular reference error: I saw this post from 2018 describing the same need, but was hoping that since then it became possible.Anyone knows if this can be done? 

What happened to the Pretty Print link?  It no longer works, but there are so many references to it for formulas.

We are using Airtable to organise our plant nursery. We need it to track the stock of plants in the nursery and match to orders etc. Its a bit different to normal stock tracking where we might track "current stock" by adding to stock when we get in stock and subtracting as orders go out because we have to grow the plants from seed and there are often plants dying, not being good quality etc. So we often have to go out and count the actual plants in good condition on the tables to get an accurate idea of stock count. But we also want to be able to subtract from the stock count automatically as stock is sent out. So we have a conflict between what we want to enter as our current stock levels - if we have a calculation in the current stock field - auto subtracting deliveries - we can't simply type in a number when we do a manual stock count. Any suggestions on how to resolve this ?

Hi all, I am copying over historical data into a new Airtable system and I am using the following formula for calculating the number of days taken to complete a task IF(  OR({Date Completed}, {Date Completed Override}),  WORKDAY_DIFF(    IF({Start Date Actual Override}, {Start Date Actual Override}, {Start Date Actual}),    IF({Date Completed Override}, {Date Completed Override}, {Date Completed})  ),  BLANK())It works just as intended, it counts the number of workdays to complete a task, using the default field unless an override date is being used.However, there are one or two results that are just way off. As an example, one record has the following values:Start Date Actual: blankStart Date Actual Override: 1/3/2025Date Completed: blankDate Completed Override: 23/4/2025Completed Task Duration: 474831What is baffling me is that there is another record with exactly the same dates that is correctly returning a value of 15.The default fields are auto generated via a formula, and the ove

Hi, for a given record, I have data attached to linked records of linked records that I’d like to lookup. I remember this was really easy to do in FileMaker, but I can’t seem to find a way to do this in Airtable, unless I add lookup fields in each table? Any idea/solution would be greatly appreciated - thanks!

Hello hive mind geniuses! Bear with me, I’m not sure how to explain all this, so I’ll start with my use case scenario and end with my current setup so far.I’m trying to create a file folder/mail merge printing system for genealogy & correspondence names. The problem I’m trying to resolve is having to copy and paste information over and over into label templates, whenever file folder or mailing labels need to be fixed, reprinted, or duplicated.Currently, I have to copy and paste from within each name or address cell in my spreadsheet. Even if I rearrange the spreadsheet to display vertical columns rather than long rows of cells, a full copy/paste of multiple cells shows mini tables inside the label borders, instead of formatted text columns. It’s exhausting. So the urge is to save the label sheet for when new labels need to be printed as replacements, edits, or extra file folders or envelopes. But then every time you need a few or many new labels printed, you have to hunt them down

Hey guys, probably an easy thing to do but I want to name an entry according to the right “quarter candidate” field without having to create a super long “IF” formula (I want to update this table without having to update the formula everytime I add a new quarter candidate to the list)My goal : in an other base each new entry have the current date added to it, according to this date, I want to have a field with the right quarter candidate because the date is after the “input start date” and before the “Input end date” Thanks for your help !   

I have a formula field that returns the URLs of an attachment field. If there's only one attachment, it returns one URL in parentheses. But multiple attachments take this form: "File Name 1 (URL 1), File Name 2 (URL 2)".Here is an example: "Screenshot 2023-07-03 141051.png (https://dl.airtable.com/.attachments/c53420d1c5692651db4f6c34e5ddc19f/8256aa0b/Screenshot2023-07-03141051.png), Screenshot 2023-06-23 130019.png (https://dl.airtable.com/.attachments/1d08cfcdd7ce28fa575f0c20738ae2ea/bc5c5421/Screenshot2023-06-23130019.png)".I want to write a formula that will extract only the URL in the last set of parentheses, no matter how many other files are listed in the cell (or just the single URL if there's only one file). In other words, I want to extract the URL of the most recently uploaded attachment, which is always the last one listed in the cell.

I have two tables, Table 1 and Table 2, with different fields. When a specific record is populated in Table 2, I want the values of some of its fields to automatically appear in a corresponding record in Table 1where the cells were initially empty,  based on the primary fields of both tables, I’m calculating a total in Table 1 based on entries into records in several tables and need to have the total in Table1  automatically updated whenever the values change in the other tables. What is the best way to achieve this? IOW, what is the equivalent of XLOOKUP or Index Match in Airtable? 

I have two tables, Table 1 and Table 2, with different fields. When a specific record is populated in Table 2, I want the values of some of its fields to automatically appear in a corresponding record in Table 1where the cells were initially empty,  based on the primary fields of both tables, I’m calculating a total in Table 1 based on entries into records in several tables and need to have the total in Table1  automatically updated whenever the values change in the other tables. What is the best way to achieve this? IOW, what is the equivalent of XLOOKUP or Index Match in Airtable?

Hi EveryoneI’m sure that this has been done before but I can’t seem to get anything I try to work Im looking to create a forula filoed that looks up and displays the PAYE Month a date in another field falls into for example 17/04/2024 would lookup and display PAYE Month 102/10/2024 would display PAYE month 620/08/2025 would display PAYE Month 5  I am dealing with the tax year in another field so I dont want to worry about the year below is the parameters I have tried if statements I have tried a switch example but cant get anything to work  I have tried setting a year start date and using dateAdd to add on the number days after the year start  but I just not getting anything to work right  If someone could point me in the right direction I would be really gratefullCheersJackieFrom Date   To Date   Days Between No. Days from start of tax year Should Display  6th april 5th May 29 29 PAYE MONTH 1 6th may 5th June 30 59 PAYE MONTH 2 6th june 5th

I need to create a test/quiz for my employees in order for them to be certified in a subject area.I'd like to create a form with 30 questions, each question with up to 4 multiple choice answers.  I have an answer key, indicating which of the answers is correctIs there a way for the employee to complete the form and submit and AT automatically compares the answers to the answer key and provides the number answers correct and the percentage of answers correct out of 100.

Hi know that creating a recipe with several ingredients and do the calculations So I was thinking to do the recipe vertically Is that working for me ? Please advise  Regards 

Can anyone tell me how I would write a formula that will return the lowest date from a series of dates? this would be very simple in excel, but it seems impossible  

I have a list of names in the format John Smith MD or John Smith, MD (some with commas, some without) and I’d like them to be in the format Dr. Smith. Is there a formula that can do that for me?

I’m exporting data to a pdf via Typeflow. One of the fields is a Link to multiple records, which happen to be street addresses.  When I export the data to a pdf, the default view is to separate each linked record by a comma.  I’m trying to create a line break instead of a comma.   I created a formula field with SUBSTITUTE(Addresses,”,”,”\n), however the street addresses contain commas, so this didn’t work. Any ideas for another work around?   Huge thanks in advance.  Current format:Record 1, Record 2, Record 3 Desired format:Record 1Record 2Record 3etc