Formulas

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 a

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,

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

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

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’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

I am trying to solve the problem with dates validation.The prerequisites are:A date field to check A text string where days of the week AND times of the operation of an establishment are stored. This string might have a format of:H24 or DLY 1500-0530 or MON-FRI 0700-2300 SAT SUN 0800-2300 or MON 0415-1415 1900-2000 TUE THU 0600-1300 1830-1930 WED 1800-2000 FRI 0415-2000 SAT 1400-1600 SUN 0415-0730 1200-1415 1900-2000I need to check if the date in the date field falls into these periods of time and use the result TRUE/FALSE. Any idea on how to create a formula will be greatly appreciated. Thank you

Hi all, I have a base that date and timestamps new records. But since I had to import thousands of records from an old spreadsheet, it doesn’t correctly reflect the original date/time in the CREATED TIME field. So I created a field called DATE + TIME - MANUAL that is a date field that has the original submitted dates for the old records. All future records will get a submitted date/time in the DATE + TIME - AUTOMATIC field. In a third field called DATE + TIME SUBMITTED, I need to use a formula to say that, for a new record, if the DATE + TIME SUBMITTED - MANUAL field is blank, to use the data in the DATE + TIME SUBMITTED - AUTOMATIC field, but using this formula doesn’t bring in the data from the automatic field if the manual field is blank:IF(NOT(BLANK({Date + Time Submitted - Manual})),      {Date + Time Submitted - Manual},      {Date + Time Submitted - Automatic})Am I doing something incorrectly? Or is there a better way to achieve this? In t

Hi All, very new to AT!  But this has been an incredible community but I haven’t been able to find an answer to this question:I have a column on Table A called “System A Identifier”I have a column on Table B called “System B Identifier” The column on Table A will ALWAYS have more data than the one in Table B.  I would like to create a Table C that links to the “System A Identifier” in table A and a new column that shows me a “Exists: or “Missing” value if that record is available or not based on the field “System B Identifier” column. I think an IF formula is the way to go, i just have not been able to figure it out. Thanks!

Bonjour, je cherche la page des formule AIRTABLE sur le site AIRTABLE , Merci.Hervé

I know there has to be a easier way that writing out a huge if/else We are collecting Birthdates - Got thisWe parse the birthdate against today’s date to get an age - Got this workingWe currently have single select field called Age Bracket that contains the following options to select one based on the AGE field Under 2121- 29 30 - 3940 - 4150 - 5160 - 6170 - 7180+I am working transitioning the manual step of the team is having to do - to select the correct age bracket for the age result (For our Demographics)Thank you in advance for answering how to do this. Cause for some reason my brain just isn’t seeing this the way it needs to.

Hello :) I’m not sure if this is possible but I would really appreciate any input or advice… I am overseeing the gig booking process for a band. I have 2 tables:Bookings EventsIn the Events table I have a summary field which concatenates various pieces of data about the event. I want to use this so that, when I am looking at the overall Booking, I can see a summary of all the info for each Events that is part of that Booking. The formula for this summary field is: CONCATENATE("\nEvent Name: "&{🟣 Event Name}&"\nDate: "&{🟣 Event Date (pretty)}&"\nEvent Time: "&{🟣 Event Time (pretty)}&"\nInfo on sound reqs: "&{Long text field 1}&"\nInfo about parking and dressing rooms: "&{Long text field 2}&"\n") At the moment I am pulling it into the Bookings table as a Lookup, but I think it c

I want a field that tells me if a volunteer shift is full, using the Volunteers Needed number field and the Volunteers Assigned linked records field. If I could write the code in plain English, something like:IF [number of records in Volunteers Assigned] is equal to or greater than {Volunteers Needed}, then display “YES”, IF lesser than, then display “NO” Checkbox field checked/unchecked instead of “YES” or “NO” text would also be cool but not necessary. I can figure out the IF part, but can I get the [number of records in Volunteers Assigned] just in the code, or do I need a Count field to find that and then use that Count field in the formula?

Hi there, I have two tables:One that stores a sale One that stores interactionsEach sale has a date. Each interaction also has a date. There is a client dimension, each client can have multiple interactions. I can link to the interactions in the table where I see the sales. I'd like to calculate how many days are between the last interaction, and the actual sale. In my test, there are three interactions (note: European date notation):11/2/2025, 28/11/2024, 6/12/2024These are shown via a lookup value in my sales table.The sale is formatted as follows: 11/4/2025 08:10If I create a formula to calculate the number of days between last interaction and sales date, I get an incorrect answer: 20189I think this is because the dates are not treated as an array, but as one text string. Anyone an idea how to solve this? Thanks!

Hello,I want to calculate the length between a start and an end date. Seems easy, but the result is wrong for the fields that came from a lookup field.Below a screenshot from my base and my calculations.Here an explanation:Start Date = automatically set as record is created, formatted as “European” End Date = calculation, depending on how my record ended. This calculation is correct, so nothing wrong here. The format is set to “European”  Date moved to fridge = manually selected from calender End date option 2 = lookup field (original field is date that the record was created) End date option 3 = lookup field (original field is date that the record was created) Total days = wrong calculation: formula: DATETIME_DIFF({End Date}, {Start Date}, 'days') format: number Result is correct for manually selected date in date format

My table includes three date fields: Joined, Separated, and Date Rejoined, along with two formula fields: Prior Years and Total Years of Service.I’ve now added two additional fields: Date Separated 2 and Prior Years 2, to account for members who have separated and rejoined more than once. Some members join, then separate, later rejoin, and eventually separate again. I need to accurately track their total time as active members, excluding any periods when they were separated.The original formula for Total Years of Service worked well—until I had to include a second separation date.Here is the formula that worked originally but now I need to take into account the second separated date.If anyone can rewrite this for me, I would be grateful. I am not good at formulas. IF(AND({Date Rejoined}, {Se

Hi - I have a project management and resource tracker AirTable built. I’d like to be able to look at how many hours someone worked in a previous week. Currently I have a rollup from “time entries” to “team” filtered by a formula within time entries that calculates if a time entry was within the last 7 days. But, leadership would like to review this information on (mostly) Thursdays for the previous calendar week. I’m not able to simply adjust the number of days as leadership may look at this information on a Wednesday or Friday.  Is there a way to calculate this information?  Thanks!

I would appreciate any input for such task:I need to determine if TODAY() falls within a date range, say from March 1st to October 31st, regardless of the year.So I need to compare TODAY() with start date and end date without the year, but only with day and month.I cannot figure out how to do it.Thanks for any thoughts

Hi there, I am trying to output text that is taking the text from the lookup field "Name" and taking it away from a field called "Conversations" Currently, I am trying to do this in a formula but for some reason it keeps saying #ERROR!