Formulas

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 the end, I just need to have a date field

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 could also come through as a Rollup. Once it’s in the Bookings table, I have an automation that copies it into a long text field so that the \n formatting splits up the da

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 Result is wrong or NAN for lookup date Who can help me fix this issue?Thank you! screenshot from my basecalculation for my end date, gives corre

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}, {Separated}, {Joined}), DATETIME_DIFF({Separated}, {Joined}, 'days') + DATETIME_DIFF(TODAY(), {Date Rejoined}, 'days'), IF(AND({Joined}, {Separated}), DATETIME_DIFF({Separated}, {Joined}, 'days'), IF({

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",), {Mentor (from Link to Forms)},{Mentor (from Link to Forms)}, "*") But that is not doing it for me. What am I missing?Thank you!~Lisa

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") 

Hi! Very new to this process and Airtable, so I’m sorry if this is confusing/doesn’t have enough to go on, or uses the incorrect terminology. If anything needs clarification please just ask! I’m currently trying to build a scheduling base that will update itself with the next date that should be assigned in the process if the timeline deviates from the original schedule. I’ve managed to get most of it figured out, but I’m having trouble with two of my fields/formulas returning incorrect values. I keep overlooking it, so I’m wondering if someone else can help me or offer advice. The field my formulas are based on is: {Due Date} The formula fields (in order) that are made based on that are: {Article in Progress} {Outline Due} {Outline to Client Expected Date} {Draft Expected Start Date} The date fields (in order) that can update the formula process if the project goes off track are: {Internal Outline Delivered} {Outline Delivered to Client} The current process starts by using the due

Hi there, hoping someone can assist me. I need a formula that will note true/false based on an attachment field.For further context, this will be used in another formula that combines various fields into a link which goes to a custom Typeform. The fields from Airtable are set as hidden fields, but at the moment I'm having issues with the logic because it can't recognise if the data is true/false since the entire file name is showing. Having this terminology would be good so I can simplify the link and the Typeform logic.I tried working with the TRUE formula but couldn't get that to work.Let me know if you need more information. Any assistance would be appreciated.UPDATE - I've found a solution;IF( {Attachment Field}, "TRUE", "FALSE" ) 

Hello,I am in need of a formula that can calculate a duration of time for payroll for hourly employees. I have a column with Start Time and one with End Time using duration. I also have separate columns for both to capture AM or PM. I just can’t seem to get the formula correct and getting the correct duration. If I need to change my columns I am up to however it can work. I do have a date column to if that helps with the formula. Thanks in advance for the help.

I have the above time stamps. What I need to do is calculate the hours/minutes from 8AM to the Start Date 1, as well as End Date 1 to 5pm. How can I accomplish this? 

I’ve seen a lot of wild posts about this topic, but I was wondering if there was a a more simple and concise explanation for how to convert a field containing an integer in Unix Time, to a Date field. The Airtable knowledge base has incorrect information: https://support.airtable.com/v1/docs/converting-unix-epoch-time-to-current-date?highlight=unix%20time