Formulas

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

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: {Interna

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

I'm reaching out for your expertise. My sample table has three date fields: Joined, Separated, and Date Rejoined. It also has two formula fields: Prior # of Years from Joined date to Separated and Total Years. I need your helpto correct the formula for calculating the total years of membership.I am trying to calculate the total number of years they have been members. I can do that, but the problem is that when they join, separate, and rejoin, we don’t give them credit for the years they were gone/separated. Here is the formula I used for Total years. I’m sure my formula must be wrong for the “total years including prior years".     DATETIME_DIFF(TODAY(), {Joined},'days')/365 - {Prior # of Years from Joined date to Separated}But for members who have never separated, it gives me NaN for prior and total. Prior years' formula is:     DATETIME_DIFF(Separated, Joined,

I assume there is not formula reference for this. Is there anyone with prebuilt formula that will identify when to use the s, ‘s, or es on a name? To comply with the standard English rules here: https://www.merriam-webster.com/grammar/what-happens-to-names-when-we-make-them-plural-or-possessiveI’m sure I could take 2+ hours figure it out, unless you by chance have it 😀

We have a list of names and dates, like so McClelland, David (1820 - 1899) We would like to separate the dates from the the name, so everything in parentheses would be extracted and moved to a new column. I know there must be some code for this, but I am not sure what it is. Help! Kevin 

Hi all,I need to generate a 4 digit code from the year a record was created and how many records have come before in that year.E.g the 5th record that has been created in 2023 = 2305.I have reformatted the referral date DATETIME_FORMAT({Date of Referral},"YY") but unable to get a COUNTIF function to work to count how many referrals have been made before this one where the referral date is in the same year.

So, I have some data that looks like this: 二代目 歌川広重; 1826 - September 17, 1869I want to sparate the japanese name, at left, from the dates, at right. It’s not always Japanese, but it is always seprated by a semicolon. Formula suggestions? Thanks in advance!

This formula is very handy DATETIME_PARSE({WEEKNUM}&’’,‘w’) however I need the first day of a work week to be and Monday and the last day of the workweek be Sunday How can I modify this formula?

Hi there, This is a common question that I wish it does not have to involve a formula.  But I guess it should.  Can you tell me the way to write a formula to specify how I can account the total number of active projects for one month (Feb 2025)?   I have a start date and end date.   Combo Request DateNeeded By DateDate format MM/DD/YYYY When I write the formula it is not capturing the correct records active in Feb 2025. IF(  AND(    OR(      DATETIME_FORMAT({Combo Request Date}, 'MM-DD-YYYY') >= '02-01-2025',      AND(        {Combo Request Date} <= '02-28-2025',        {Needed By} >= '02-01-2025'      )    ),    {Needed By} >= '02-01-2025'  ),  1,  0) Do I need to specify status on-going, complete, in progress?

Hello, Currently I have a date field called Date, I would like to make a formula that looks at this date and outputs the first Tuesday of the previous month.Example{date} - Shows March 13, 2025Formula - Should show February 4, 2025 (First Tuesday of Previous Month)I was able to find the 1st date of the previous month utilizing this, but I cannot seem to figure out how to get the first Tuesday. DATETIME_FORMAT(DATEADD(DATEADD({Date}, -1, 'month'), -(DAY({Date}) - 1), 'days'),"MM/DD/YYYY") 

Hi, I am fairly new to Airtable, so I am trying to learn how to do things more efficiently. I have a question about formulas.Is there a way to select a field based on an input without using the SWITCH() function? I’m thinking something like a FIELD_SELECTOR() (← this a made up function as far as I know, but maybe something like it exists?), where I input the text display_name of a field, and output the value for that field. Or is this something where I need to start learning how to integrate python, and airtable built-in functions are not powerful enough?Example:I am making a database with taxonomic information (ie. latin names of living things). I have all the taxonomic ranks from Phylum to Species as separate fields. I have a field called taxonomic_rank that indicates the lowest taxonomic rank in the row. I want to make a function to create a display name that outputs “taxonomy_name taxonomic_rank”. So functionally, A row that say

Hi, I want to be able to copy the result in one field into another single-line text file so that I can look it up. The first field is multi-select. All I want to do is convert it into a single-line text field. I need this to look up the text from another table. I need to do this because the first field is already a lookup in this table from yet another, and I need to be able to link some of the details from a third table altogether. I hope this is making sense. I don’t know how else to explain it. Thanks S

HiI am a novice using Airtable, but I am learning….slowly.  I have the free plan, and I’m trying to figure out how to add recurring events to my calendar easily. For instance, I would like to add a recurring event every Friday between two specific dates. Any help would be appreciated.

Here's a question for you all,If you have a check box that asks a question such as [Would you like to add this service?] Where checked is yes. How can I get a formula field to fill with a price result?i.e. IF({New Service}, ….{New Service} Being the checkbox field. What goes next? Thanks in advance.  S 

Hello, I want to know if there is a formula to identify a text within a link cell, in this cell conditions that a home meets appear. Example: unreleased, released, foundation, walls, roof, finishes, finished. I want the formula to identify if the cell evaluated says without releasing the result is UNSTARTED, if it contains released the result is IN PROCESS or if it contains finished, the result is TERMINATED. I have tried with the IF formula, but I have not succeeded. Thank you 

HiI’m creating on airtable a KPI for restaurant and my challenge is to find the right Formula that worksi generate the formula with the AI but doesnt workplease help regards 

Hey! We have a difficult airtable formula to create that outputs 1 or 0 based on how soon a date is. Could you tell me if you could help?This is the problem:We have a variable called {Deadline}, which is the deadline to respond to a job. We have a job board linked to airtable. However, this is also the deadline for us as recruiters do our work with the applicant. It's still up on the website even minutes before the deadline. So if someone responds, it's too late, which is frustrating for them. But we still would want them to apply in case we have the time.So we need AT LEAST 8 hours (a 9:00 until 17:00 workday) * 60 minutes (for more accuracy) = 480 minutes to do our recruitment work. So we need a formula that says: IF we have less than or equal to 480 minutes of work during work hours until the deadline, the formula should be 1 (indicating to us that we should give a warning on the website when someone applies that we might not make it but will try). If it is alr

Hello. I’m very new to Airtable and need an assist! I have this formula in Google Sheets: =BYROW($G$15:$G,LAMBDA(i,IF(i="",,VLOOKUP(i,$F$1:$G$2,2,0)))) VLOOKUP references this lookup table: 1 UsedLikeNew 2 UsedVeryGood 3 UsedGood 4 UsedAcceptable 5 CollLikeNew 6 CollVeryGood 7 CollGood 8 CollAcceptable 10 Refurbished 11 New  If a number there matches a number in a column  starting in cell G15 (via BYROW), then it returns the category in the lookup table associated with that number. So the column might be: 357410 And the fo