Formulas

Im trying to write the same formula I have in excel in to Airtable and Airtable erases everything but the first if(and()) statement. IF({Statement} = 1, "3",     IF(AND({Statement2}<=50, {Statement2>0),1),     IF(AND({Statement2}>50, {Statement2}<=100, 1.5)),     IF(AND({Statement2}>100, 2))) I don't get a

I have a formula field that gives me a date.then I have a normal date field "current". For example, if I enter a date in the "Current" date field, then the formula field should calculate 2 years from this date and give me the date in 2 yearsExample: “current” has the value: October 1st, 2020Formula field calculates 2 years further10/1/2022works quite well so far If the “current” field is empty then the formula field should take the date of “approval” and calculate 3 years from there.Example:“Approval” has the value: October 1st, 2017 then the formula field calculates 3 years further so: October 1st, 2020 also works.but every time this formula is executed, I get a linked tablea new data set is simply created even though this is not what is wanted.here is my formula:  DATETIME_FORMAT(IF({Letzter HU/ AU Termin}, DATEADD({Letzter HU/ AU Termin}, 2, 'years'), DATEADD({Ers

Hi All, I am trying to build a Reminder portal to track some key dates within Airtable and was wondering how in Airtable can I use Formula or if there is any other way to find the date that is due to come from a range of dates (sample data below - I can use the MINIFS function - =MINIFS(A1:D2, A1:D2, ">"&TODAY()) in excel to find the smallest date that is greater than today's date). But how do I achieve this in Airtable ? I couldn't find the Minifs formula function in Airtable  ABCDE1Q1

If you're working with numbers and formulas are returning different results, rounding differences or precision errors could be the cause.Solution:Ensure that both formulas are using the same precision (number of decimal places).Use the ROUND() function in Excel or SQL to limit precision.

Hi Airtable Community,I’m currently managing a database where payments are recorded in a “Payments” table, attendance is tracked in a “Attendance” table, and student information is kept in a “Students” table, all to maintain good data normalization. However, I’ve run into a significant issue when trying to create a “Debtors” table. Creating a unique list of student-class combinations from the “Attendance” table was not as straightforward as I had hoped. It required exporting the data to Google Sheets to generate that unique list. Additionally, I was able to automatically link each payment to the attendance records by using an automation based on a shared key (student-class-period) between the payment records and the attendance records. These challenges were overcome, but they clearly could have been handled more efficiently if Airtable allowed data from payments to be transferred indirectly. However, since relationships i

Hey there! I am trying to allocate monthly expenses across our business units (BUs)  based on pre determined splitting criteria, some of those criteria change month over month, such as the percentage of revenue each business unit is producing. What I'm trying to do right now (but failed at it) is linking four different tables: 1. Transfers --> All the payments we have done in a month2. Suppliers --> A list of all suppliers we currently have3. Splitting criteria --> A list of all the possible splitting criteria (Equal splitting across BUs , splitting by  revenue%, etc) 4. Splitting % --> Given each splitting criteria, how would the percentages be assigned to each BU each month. For example if the split is by revenue %, what percentage of revenue each BU produced every month, or if it is equal splitting all BUs get 33% every month. This table has the following columns: month, splitting criteria, pctg to BU1, pctg to BU2, pctg

DescriptionThere appears to be an issue with Rollup Fields when used in conjunction with Linking Records and date columns. The problem occurs when the Rollup formula becomes more complex.How to ReproduceCreate a Rollup Field that references a date field in Linked Records.Set the Rollup formula to MIN(values).Result: Some records correctly display dates (as expected, since not all linked records have values in the corresponding date field). Change the Rollup formula to a more complex one, such as: IF(MIN(values) = BLANK(), "-", MIN(values))Result: All records now display "-" instead of the expected dates. 

Hi guys!Losing my mind trying to figure this out – I thought it would be a super simple formula but just cannot get it to work. Here's the lowdown:I'm mapping out placements for an editorial website – there's a carousel that will feature 5 articles each week, rotating between 12 different articles. I want to be able to count how many times an article has already been used in the plan.So, I've got a table here that's grouped by each week (ie. "1. 9 Nov"). Below is an example screenshot showing the first 3 weeks (but there are 7 in total):A breakdown of each column:Name: The name of the week (so it can be referenced in "List of placements")Article: This links to another table with a long list of hundreds of articlesPlacement: A single select field with

Being new to airtable and not wanting to pollute the forums with silly questions I can look up myself, I've been trying to go to the online help to solve my own questions.  Unless I'm missing it, most of what they provide is an organized set of articles which is to be frank, frustratingly useless to this old school gray haired developer.  I was hoping to find a simple itemized list of terms with reference/example type information, almost like a dictionary. Every other programming language has this kind of thing, and you can easily find terms that sound like what you want with a quick description (reference style) and a few examples.  Does this kind of thing exist, or is it just the article format?  I know I can pay for teams and do the AI thing, or go ask chatgpt, but I'd rather just do it old school and research it quickly.Thanks.

Hello, in a form, i ask people to select some items of a multiselect field. them i would like to create a new field with the complementary -> all items except the selected ones). do you know if there is a way to do that ?

i'm trying to make a formula to pull out the last name from a field that sometimes has full names in it & sometimes does not. i found this formula in a very helpful Airtable blog:TRIM(MID(SUBSTITUTE(Employer," ",REPT(" ",LEN(Employer))), (3-1)*LEN(Employer)+1, LEN(Employer)))this worked for me when i was parsing a field that always had "Title First Last" as the number of words. however, my new field sometimes has a middle initial. i changed the formula to (4-1) to account for the middle initial, but it doesn't work for names without a middle initial.i'm guessing there's a way to tell the formula to look for the word that is the right-most word separated by a space, but honestly i don't even fully understand this formula, so i'm not sure how to modify it.Here's an example of data in the original field & the output:

Can someone give me some advice on how to update the yellow columns as shown in the example below?I have a fillout form that the approvers can update the approval status of the applicant. On saving and the record gets updated, I'd like the counts updated.  Would I do this on an automation of a record update of the member approval table?  

Hey Airtable community,Been hitting my head against the wall for the last hour with this. I'm trying to write a formula for:If {Donor First Name} is not empty, then the field would read: {Donor First Name}&" "&{Donor Last Name}And if {Donor First Name} is empty, then the field would read: {Name}And if {Name} is empty, then the field would just remain blank.Here is my stab at the formula, which I am getting an error message for: Any ideas how to make it work? 

Hello, I am trying to just multiply Roll Ft Size x Lin Ft. But I keep getting an error. What am I doing wrong here?Thank you for any help here.  

Hello, I'm creating a fairly simple base to track all of the violations for an HOA board. I'm trying to create a field to calculate the total number of times a house has gotten the same violation in the past year.The Base has 3 tables, "Homes" with address and owner, "CCRs" using 'CCR ID' as the name of the violation, and "Violations" that collects the date of the violation, the 'Home', and the 'Violation'So, I basically need to have another field that counts how many times the same 'Home' and 'Violation' are both in the same row over the past 365 days.I got this to work by using grouping. In the attached picture, the highlighted number is the number I need to get to populate in its own field. Thanks in advance for any help! 

Hi there, Is there any way I am able to sync my Airtable contacts with my iCloud contacts so all of the contacts on my Airtable will also be on my iPhone? And vice versa. Without having to do it manually of course. Let me know, thank you.

Can anyone recommend a license-free icon library compatible with Airtable Formulas and Interface Text fields? I see them used in templates, but would love to have access to a complete library.

Good afternoon! I’m working on gmail automation and inserting $ amount fields into the content of my message. I’m using the following formula and noticed that the trailing zero is missing for an amount: CONCATENATE(‘$’& ROUND({Daily Rental Fee},2)) Missing zero: How can I get the zero to appear?

I have multiple tables that deal with budgeting, and in each one I've got a similar setup. I track the total budgeted and then how much we've spent manually, then a formula determines the difference and another formula looks at that difference and determines the status of the budget. It works great 95% of the time and looks like the below.Total BudgetedTotal SpentDifferenceStatus$100,000$100,000$0Matches Budget$500,000$650,000-$150,000Over Budget$300,000$200,000$100,000Under BudgetThe problem I'm seeing

HI i want to ask some questions i have a two table Product and Category, in product i have linked to another record field with the name 'Categories' and i want to filter the record in Product table with id that i have in Category table i have tried this but it's always return empty records. https://api.airtable.com/v0/<basedid>/<tableid>?filterByFormula={Categories}='recVXBZfPO530BgxK'Do you have any solutions?

Hi,I am trying to extract text and date from subject field in gmail into two seperate fields but I can not get the formula right.The subject will be in this format  "16610 6/9"16610 should be extracted as text into seperate field and 6/9 into date field (september 9)Can anyone help?/Michael     

Hi, I am looking for some help with a formula that assigns a specific shipping box to a product based on product dimensions. I have a table for Products and Boxes. The example dimensions of 2 product are: Product 1 : 50cm x 40cm x 5cm Product 2: 80cm x 60cm x 8cm The products will be assigned to one of these two example boxes: Box Size 1: Fits products measuring (40-60cm) x (30-50cm) x (3-6cm) Box Size 2: Fits products measuring (60-90cm) x (0-70cm) x (7-9cm) I am having trouble with the IF or IF/AND formulas. Any help would be appreciated. Thank you,

Hi,I am new to Airtable and now very tech savvy in general so I'm sorry if this is an obvious question.I have a table with a list of my discovery calls and then I record when these turn into paying clients. I want to summarise how many sale I make per week and per month. When I did this in excel I created a separate sheet that had the weeks of the year listed by date and by week number. Then I set up a formula to capture all sales made in that specific week. How do I do this in Airtable?Thanks!

Hi everyone,I’m encountering some challenges with defining a rollup field that combines data from three tables in Airtable. Here’s the setup:Buildings (fields: name, etc.)Employees (fields: name, hourly_rate, etc.)Timesheet (fields: employee_name, building_name, day, hours, etc.)I want to create a view that summarizes the total salary for each employee in each building. The view should include the following fields:employee_namebuilding_nametotal_salaryThe total_salary should be calculated as follows:Employee.hourly_rate * (sum of all hours in the Timesheet where building_name matches the building in the view).Can anyone provide guidance on how to set up these rollup fields and calculations to achieve this view?Thanks in advance for your help!

I need to calculate the number of days a ticket was open, but excluding weekends.However, Airtable wont let me change my work days to be Sunday-Thursday.What formula can I use as a workaround, to count all days of the week minus every Friday and Saturday of the year?