Formulas

Trying to figure out how to do a search to see if a number falls within 2 fields.For example a field with Start year and a field with End year.  Lets say on one record my start year was 1978 and my end year was 1986.  I would like to do a search for 1983 and have it retrieve that record.  Can someone point me in the correct direction??

Hi, to be honest I’m not sure if it actually is a bug, but this function does not follow the ISO standard of numbering weeks. For example, today (2016-10-13) should be week 41, but the function returns 42. Probably because the first week of this year was week 53 according to the international standards.

Hello!I was recently helped on another post to create parameters for the dateadd function. However, it is not calculating dates correctly. Why? How do I fix this?Here is my function:SWITCH(  Task,  'Email for photo, new hire survey, and short bio of new hire', DATEADD({Hire Date}, -7, 'days'),  'Assign desk space for new hire', DATEADD(

Hi,I have a base where I keep my business's time entries for each of the employees.  However, they may be in a different state based on the day, so I'm looking for a way to populate a field for each time entry showing their location.The time entries have a date, a person who performed it (currently as a linked record), and the the number of hours.I currently have a sheet with a separate record for each date and single select field for each employee showing the state that they were in.  Note I can change the organization of this table if needed (for example to entries with a date, a single employee and a location).  Any thoughts on how to best set this up?  I currently have lookup fields for each individual employee in the time entries table, looking up their location on the given date, and then a formula field that uses a switch that selects which lookup field based on the user who performed the time entry.  However, it requi

I need to calculate different due dates for tasks based on Workday instead of dateadd. Here is my original formula: SWITCH(  Task,  'Email for photo, new hire survey, and short bio of new hire', DATEADD({Hire Date}, -7, 'days'),  'Assign desk space for new hire', DATEADD({Hire Date}, -3, 'days'),  'Give Uniform'

I want to filter to the infinity values but am unsure how to do this. I don't want to remove them using an IF statement or the other tricks you can use to have them become zero or empty values. The first screenshot shows the data and that there is one value with Infinity. When I try to use the filter condition to equal the infinity value I am unable to enter the word infinity because it only allows numbers. Next I tried to filter to a value greater then the highest value other then infinity and this filters out everything including the infinity value. How can you filter to the infinity values?    

Hi,I want to create a formula to create a name for a field that consists of "FIELDA" or "FIELDB" If "FIELDA" has no value i would like to show "FIELDB" in the name instead. If "FIELDA" and "FIELDB" both have a value, I would only like to have "FIELDA" filled in the name of that field. If both have no value, no value should be shown. Thanks for helping out! Mark 

I'm stuck. I need a field type and formula to display the cumulative annual profit or loss (P/L) displayed as a percentage (%) and based on a Q1 starting amount and subsequent amounts entered in Q2, Q3, & Q4 fields.This seemed easy until I got started. Now I'm digging a mental hole I can't climb out of :-). Help... thank you.

I have a [Anniversary] field and a field that calculates the next anniversary, so I get reminders and write a card for my volunteers. Here is my problem:The basic formula works fine when there is an anniversary in the field. When there isn't one, I get the #ERROR, which one would expect. I tried ISERROR(...) and IF(ISERROR...) to get it to show blank, but I can't get it to work. All the fields go blank, even if there is a date.Here is the working formula in the AV_Next field:DATETIME_PARSE(DAY(Anniversary) & "-" & MONTH(Anniversary) & "-" &(YEAR(TODAY()) + IF(DATETIME_FORMAT(Anniversary, "MMDD") > DATETIME_FORMAT(TODAY(), "MMDD"),0, 1)),"D-M-YYYY"

Hi,I've created a formula to calculate my patients' current age based on their date of birth (Lookup field). When I look at the record in grid view, it gives me an error message, but when I check the formula for accuracy, the calculation preview gives me the correct calculation. I'm not sure why there is an error in the record in grid view if the formula is accurate when I check and edit it. HIPAA-protected info has been redacted in the screenshot below. DATETIME_DIFF(TODAY(), {Child's Date of Birth (from Lookup) 2}, 'years') & ' years, ' & MOD(DATETIME_DIFF(TODAY(), DOB, 

Hello dear community,I have already searched but found nothing. I want to use a value from one row (for example, row 1) in a formula in row 2. In row 2, the value from row 1 should be used minus the value from row 2 (same field). How does that work?Automation is not the right way, as the dataset is not newly created (trigger), but already exists. Specifically, it is about the following:I want to use the value "km" from the field "refueled at km" in row 1 and calculate it in row 2 in the field "kilometers driven". This value is calculated from the field "refueled at km" in row 1 minus "refueled at km" in row 2. Can someone help me? Thanks in advance.

Hello,Our team has reduced available hours in the summer, and I'm trying to figure how to represent this when using the Utilization summary in Timeline view so that their Available Hours are reduced during a set start and end date. Thank you!

Hello--We're developing an Affiliate program where the affiliates offer a free month on our app. We're gathering new user data from Typeform forms that are automatically dropping in the information into different Airtable sheets as shown below.I'd like for all of the new users to then be put into another table that I can sort by start date, but I'm not sure how to do that.  Thanks in advance!

Hi everyone, I’m facing an issue with counting linked records in Airtable, and I’d love your help! I’ve created two tables for managing group interviews:1. “Entrevues Dispo” (Interview Slots) – Tracks the availability of each interview slot.2. “Entrevues Inscriptions” (Interview Registrations) – Stores candidate registrations linked to the available slots. In the Entrevues Dispo table, I have a linked field called “Entrevues confirmées” (Confirmed Interviews), which pulls all the candidates linked to a specific interview slot. The relationship works, and I can see the correct linked records. The Problem: I’m trying to calculate the number of candidates per slot using COUNTA({Entrevues confirmées}). However, it only counts 1 record, even though there are multiple linked records (e.g.,

I am new to the free version of Airtable which I am using to keep track of my craft stamps. I have at the moment around 500 items which have sentiments listed. When I try to find a particular sentiment I search for eg has any of, but it gives me "enter comparison value" and all the sentiments I have created. What I want is just a search box so I can just type just one word eg happy, so all those sentiments are found. What have I done wrong please and how can I rectify it.

Hello, I am trying to get this to work but all I get so far is a massive headache haha 🙂Context : organizing automatic reminders to clients we're meeting at a trade show. On the day of the meeting, I want to send a reminder to the invitee at 8:00 local time (and of course this local time changes depending on where the trade show is) to tell them they have a meeting with us that day.I have two tables.> One for trade shows with Trade Show Name (linked to the other table) and various fields, one being a number field providing the hours difference as well as a time zone field 'Europe/Paris' for instance, this being the time zone where the trade show takes place. > One for meetings with plenty of fields, including one that comes from our booking tool (Calendly) and where I've set up a webhook and the meeting time always comes as UTC. I have there also a lookup field repatriating the hours difference as well as the timezone field.

I have a base with two tables: Employees and Projects.Employees has a primary field of Employee ID and Project IDs.Projects has a primary field of Project ID, Project Manager(s), and Status.In the Employees table I want the field "Project IDs" to be automatically populated with the corresponding Project ID from the Projects table when the Employee ID matches any names in the Project Manager(s) and when Status is not "Completed".Sounds like it should be simple. But I can't seem to figure it out. Thanks for any assistance! 

I created a form for our rental car contract in Fillout. Everything is populating correctly into airtable except the time. My issue is that I put the "date - time out" in the form eg 12/19 8:00pm. It will populate to airtable as 12/20 3:00am if I have the airtable time set to UTC. if I change the time in airtable to Denver (which is correct) it populates properly from fillout to airtable. HOWEVER, when I open the PDF everything in changed back to being a day in the future at 3:00am. I think it will work properly if I create seperate fields for date and time, which I can do, but it seems like it should be able to work by doing it the way I am. I'm sure this is a timezone code I cant for the life of me figure out. Please help, Thanks!

I have this formula for calculating the quarter and the fiscal year of when an invoice should be paid.Program Start date is 7-4-2024 (which is Q3). I want it to be paid the quarter after (so this one would be be paid Q4, FY24) but its showing Q3 - FY25. How do I adjust? the formula below for a "Program Start Date" of 7-4-24 to show that it should be paid Q4-FY24? IF({Program Start Date},"Q"&ROUNDUP(VALUE(DATETIME_FORMAT(DATEADD({Program Start Date}, 1, 'months'),'M'))/3, 0)&" - FY'"&IF(

Hi everyone, I need to create a single field that automatically shows the top 3 clients for each employee, based on the total number of bookings (records) they have assigned to them. Ideally the field would be dynamic and will adjust automatically. We are using this field to integrate with external platform, Glide.We have separate tables for clients, employees and bookings, and all utilise linked records for other purposes so we are very familiar with them. I'm just unsure about how to pull this data automatically, and if it can be done as a multiple linked-record field or if it would only work as a text formula.I understand a lookup field can be used to show the first or last 3 items based on the lookup source, however I can't sort this to show the highest ranked. Example below. I really need to show the top 3 clients for each employee, and be able to easily click into their details like a linked-record allows.

Hello,I have a project table and a milestones table. I want to update the status of my project record to “complete” when all the statuses of the linked milestones are also “complete”i have a roll up field which pulls back the unique array of the milestones, so I have all the milestone statuses comma separated as a text string, but I can’t figure out the formula to look at that field and determine if all the milestones are showing complete or not 

Hey there,Thank you for reading me !I have a small issue while using lookup fields within a formula.I've got 3 differents tables : projects, actions, animationsA projet is made of several actions which are also each made of several animationsThe project primary field is a formula : Year / Name (2024 / Orange)The action primary field is a formula : Project primary field lookup / Action Name (2024 / Orange / soccer)The animation primary field is a formula : Action primary field lookup / Date (2024 / Orange / soccer / 12/12)My issue is that in action & animation primary fields, some unnecessary "" are added, i get :Action primary field : "2024 / Orange" / SoccerAnimation primary field : """2024 / Orange" / Soccer"" / 12/12I have tried a few formulas but I don't get how I can remove them. Any ideas ?Thank you for your time, have a nice day ! 

I am looking for a way to parse a lookup field (Sprint Start Date) that contains multiple, comma-separated date values, and pull the most recent date into a new field. For example:IF --> Sprint Start Date = 2024-10-12, 2024-12-22THEN --> Formula Field = 2024-12-22 I have tried to use the following formula to parse the Sprint Start Date field and convert it, but it always returns the same value, or an error:DATETIME_PARSE(MAX(IF({Sprint Start Date}), VALUE(DATETIME_FORMAT({Sprint Start Date}, 'YYYYMMDD'))))Outcome #1: 12/31/1969 Outcome #2:&nb

Hi, I have two tables for which I'd like create a formulaTable 1Feature NameFeature End Date Table 2Sprint NumberSprint Start DateSprint End DateI'd like to create a formula that checks if "Feature End Date" is between "Sprint Start Date" and "Sprint End Date" and if so, assign a Sprint Number accordingly. How can I achieve that in airtable?

“Client Table” contains clients in column A. One client to a row. Each client belongs to a group (Group 1, Group 2, Group 3, etc), which is listed in column B.  “Project Table” contains projects. The name of each project is in column A. Each project has one or more clients. One project could have clients from multiple groups. I’d like a column in the Project Table to list all the groups represented in a project.  Thanks!