Formulas

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!    

I am hoping someone can help me with this! I have tried and tried to find a solution, but I’m having trouble. Currently, records are named using a PDF filename that is synced from a Google Drive folder. (ie -- 123456789.pdf) The number of digits before in the PDF range from 4 to 9 digits. Currently, the record name becomes G-123456789 in our table.However, we have a new naming convention for our documents. So, I need to pull only PART of the filename to name the record. The new naming convention looks like this:123456789 North Smith 04-03-2025.pdfI need it to pull just the first number (123456789) to use to name the record in Airtable. I hope that makes sense, and I hope someone can help! Thank you!

How do i  find duplicate records based on an email address and remove them autmatically? I have a table that is bringing in registrants for a free event (via zapier) and I am seeing there are some duplicates that are showing up.  Presumably the person forgot they already signed up. I need to track accurate numbers.  How can I do this? Via formula? or automation? Thanks in advance

Hi there, How do I convert date to text in a new field by formula? I have one date filed(2021/5/16 9:00) and need to convert it to text data. Does anyone have a solution?

I have a formula that displays a new volunteers' mentor on a schedule for staff - basically, if there is anything in the field {Mentor (from Link to Forms)}, show that, or else show * (to prevent the field from showing up (empty) on the schedule. Here’s that formula:IF({Mentor (from Link to Forms)},{Mentor (from Link to Forms)}, "*")What’s happening is that this information is pulling through many deployments later - when a Mentor is irrelevant.I want to change the field so it is dependent on the Experience field - which I hope would make it go away when that field is NOT “This is my first deployment.”So:IF(({Experience (from Link to Forms)}, "This is my first deployment",),

I have a base that has 1 table that shows everyone that has registered for an event.  Primary field is the email field, but also has the date they registered. Then in another table I have page views by date. I am trying to figure out how to create a formula that takes the number of registrants and divides it by the page views on a certain day to give me my registration conversion rate. I’m really new to airtable so I”m not sure if this can be created.

I am collecting grades for students, a few of the grade types have subcategories that need to be averaged (some of the nomenclature is strange as it’s being translated from another language). I need to calculate a “Values” score by averaging the “Honesty”, “Empathy”, “Lucidity”, Performance”, “Respect” scores. I’m trying to keep the table design very simple as we will need to eventually translate and teach non-English speakers how to use it.  Is there a way to calculate averages only for select rows, by student? (I would like to calculate a Values score and a Property Respect score by averaging the relevant subcomponents). I understand I can see it with a View, but I want to be able to calculate the actual number to put in a clean interface view. And if I need to collect this data monthly, are the columns I have for Month/Year the best way to do that, and we would just continue adding rows? 

Cannot get this Switch to work and its very simple.  I have a field that has the year of the record stored as a number (ie 2025).  I can easily get this to work with these arguments as a singular IF, but I dont want to nest 5 Ifs. The below will not function, doesnt even give an error, wont even let me save it. Neither Claude nor chatgpt has any suggestions that make sense.  What am I missing?? SWITCH(INVOICE_YEAR, YEAR(TODAY()), "Current Year", YEAR(TODAY())-1, "Previous Year, YEAR(TODAY())-2, "Two Years Prior", YEAR(TODAY())-3, "Three Years Prior", "Older")For this record this works fine:  SWITCH(INVOICE_YEAR, 2025, "Current Year", 2024, "Previous Year", 2023, "Two Years Prior", 2022, "Three Years Prior", "Older")