Formulas

Hello --I am interested in developing a formula to assign ID's to a series of payments based on type of payment, date, and information on payee's name. This is for tracking payments in a program that is pairing experienced mentors with mentees.There are various payment categories (Mentor Initial Payment, Mentor Final Payment, Mentee Stipend Payment), the year in which the mentorship in question started, the name of the mentor, and the name of the mentee(s) that I would like to integrate. I'm hoping to develop something where if this was a Mentor Initial Payment to John Smith for mentoring Joe Boggs and Jane Doe in 2024, based on values in the Payment Table (payment type and date), linked records Mentor table (First and Last Name of Mentor), and linked records in the mentee table (First and Last Name of Mentee or Mentees), a unique payment would auto calculate producing something like MIP_2024_JS_JBJD.The first part is obvious to me (IF function), I've looked up how to extract year

 Hey there. I came soooo close to getting a working formula for what I was trying to achieve but fell at the last hurdle.I have a date field and a number field from a lookup. I am trying to create a formula that gives me a new date based on the the date field and the plus or minus number in the lookup number field. I am using this formula currently which is working and showing the new dates correctly.DATEADD(Date, VALUE(Deadline&""),'days')  I am trying to update this formula so that it only takes into account workdays but anytime I also try to add this, I get an error in my formula field:WORKDAY(DATEADD(Date, VALUE(Deadline&""),'days'))doesnt seem to work. Wondering if someone can see where I am going wrong. Thanks in advance.

{Rate (from Contract LU)}/{Frequency (from Contract LU)}Rate is a currency field and Frequency is a number field. Both reside in the Contract LU table.I have been using the above simple formula in invoicing successfully for quite a while. I recently had to create a pro rated  invoice for the first time, so I could not use the formula as is.My approach at the moment is to create a checkbox field for pro rated invoices if it is pro rated. To use it I need to be able to write a formula that allows me to enter a pro rated amount if the pro rated checkbox is checked or to allow the original formula to continue if it is not checked.First, is this the best way to do this? Second, how do I write the new beginning of the formula to allow an input.Many thanks in advance.Scott

Hello,I've created a database with a form and applications that allow each employee to make holiday requests.They can apply for leave over two months (example: from 10/08/24 to 16/09/24).My requirement: I need to know, for each employee, how many days of holiday they have taken each month.Additional information:I have the date of the first day of leave + the date of the last day of leave + the total number of days of leave (excluding public holidays and weekends). If they only take half a day, the number of days off is 0.5.What can I do?Thank you for your help.

Please help I need a formula for reoccurring seasonal milestones.  We have seasonal launch dates and milestones in each season that occur at intervals;  -130 wks, -80 wks, -56 wks.  I want to calculate the due date of the milestone based on season launch date  & milestone -wks (-50wks) to generate the due date for that milestone.   Please help! 

So I have a single Attachments field in a table that allows for multiple individual files to be attached to a record. I want to create another field that counts the number of files that have been uploaded/attached to that record. I then want to them pull this number into the primary field of the table to provide a quick way to see how many files have been attached to the record (e.g., 5 videos, 11 screenshots). However, the built-in ‘count’ formulas seem to be only counting the occurrence of an attachment for the record, not the number of individual files within the record; so as a result, it is only showing things as having 1 or 0 attachments, even though there are multiple individual files that have been attached to that record within the Attachments field. I came across an old thread about counting attachments (for some reason it’s preventing me from including the link here) but it seems outdated given the formulas now available, plus I could not get the formula to work at all. Any

I’ve attempted to follow the recommendations here to detect duplicates in a field but have not been successful and could use some expert eyes on what I’ve implemented. I did put a reply on that thread but since it’s a couple of years old, figured I should start my own question. Here are 2 screenshots from the 2 tables in play. Anyone see where I went wrong? TIA!

I have two tables:Key Source FormulaSourcesWithin the Key Source Formula table I have a field called URL FormatAn example entry is:   https://www.website.com/search/?q="Company"&t=weekIn the Sources table I have a value called Company Name Texte.g. Apple, which I want to substitute into the URL Format. This should return a result like:https://www.website.com/search/?q=Apple&t=weekThe SUBSTITUTE formula I have is:SUBSTITUTE({URL Format}, "Company", {Company Name Text})It returns #ERROR.I have even used the Airtable AI to write this formula and it doesn't work.Any help much appreciated

Hello, I am new to airtable My issue is I want to calulate the difference between two dates and calculate the seconds also, but i cant even add the seconds to the date how this could be calculated if it’s not even added. Im using the Datediff formula, is there’s any other solution?

Hi All, Apologies if this is a question already answered. I want to create a IF formula that reads for a single select sell and alters a formula dependant on what the single select field says. For example- where Delivery Month is my single select cell =IF(OR(Delivery Month = "MARCH",Delivery Month = "APRIL",Delivery Month = "MAY"), (Cost*2*4)+8), IF(Delivery Month = "FEB", (Cost*2*6)+8, ))When i place above in, the cell accepts it, but no calculation is done and cell is left blank. I am unsure why as i have checked spelling and spaces in my single select cell Thanks in advance for your help

I researched older posts and found similar topics such as this, but nothing solved that answers this particular question....I want to create a field with a formula that lists any items not selected.  An applicant has to acknowledge acceptance of the "rules", and we need a list of rules they did not accept.Is this an easy do?The sample form is shown below. The ideal answer is that the 3rd item would show up in the new field with the formula. 

Hi! I've searched and can't seem to find a simple solution to this problem.I have a multi-select column titled "Deliverable Owner" and there can be more than one person assigned as the Deliverable Owner.Based on these deliverable owners, I would like to create a new column that is a formula, which segments deliverables into "Team A", "Team B", "Team C" or "Multiple Teams".The formula ideally would work like this: If Deliverable Owner contains "John" and only "John" then assign "Team A", If Deliverable Owner contains "Ashley" and only "Ashley" then assign "Team B"...etc...and then last if Deliverable Owner contains multiple names, then assign "Multiple Teams".Is there a simple way to achieve this? Thanks in advance!

Hi guys,Hey, guys,Please help me solve a problem - I have a table of tennis matches, which has fields Player 1 and Player 2, which are linked to another table Players. In the matches table next to the Player 1 and Player 2 fields, I want to add a single select field Winner, which will have only 2 values: Player 1 or Player 2.Is there a formula that will allow me to create a single select from the values of the neighboring fields?Thanks.

Hi all, I've got a fun little challenge on my hands designing a system to track our family's board game collection and gameplay.  Here's my base structure:Games - list of all the games in our collectionPlay Log - tracks the date, time, location, players, and outcome of all the games we playScore Sheets - this is the connecting table between the logs and the players since there can be many players in a log and a player can play many gamesPlayers - list of playerslet's use a sample record, tracked all the way through my base:Myself, hubby, and son play a game of Monopoly.  The play log tells me we played at our home on 10/16/2023 at 6:00pm and then lists the 3 of us as players linked to individual records in the Score Sheets table.  In each Score Sheet record, our final scores are recorded and calculated to determine the winner which is fed back into the Log table as the outcome.Now, multiply that same scenario by 10-15 more games played with a variety of other people.Here

Hello community! I have 15 separate single select fields with the possible answers of: 1. Firsts2. Second3. Third4. No Preference 5. I do not select this UWC I would like to create the individual columns "First" "Second" "Third" "No preference" and have the answers filter into these columns (rather than having all 15 individual columns open). Does anyone have any script suggestions to make this possible? The 15 single select column titles are: - Atlantic, Changshu, Costa Rica, Dilijan, East Africa, ISAK, LPC, Maastricht, Mahindra, Mostar, Pearson, RCN, Thailand, USA, Waterford Kamhlaba  Esentially, if someone selects:- First: Atlantic- Second: Dilijan- Third: Costa Rica- I do not select this UWC: all other columns I want to be able to see the Atlantic in column "first", Dilijan in column "second" and Costa Rica in column "third" so I an hide the 15 individual columns but still see the information I need more succinctly. Any help wo

Hi all! I am attempting to utilize data from "Sheet A" (specifically item names and quantities) and SUM the "Quantity" in "Sheet B" when the "item name" from Sheet A matches the "item name" from Sheet B. Is this possible in Airtable? I've played around with a few different formulas and field types but none have successfully produced what I'm looking for. I've also created a grouped view in Sheet A that groups by Item Name and it shows the Sum in that view if that helps.If possible, I would prefer for it to be a clean formula/rollup/link/or just about any other method than manually looking up the info from one sheet and keying it into another. To clarify - all of this information is housed in one Base just multiple sheets.Any assistance is appreciated!  

Hi Guys, I'd like to use Airtable to build a utility settlement tool with the tenants. My tenants pay an advance for utility every 6 months; after 6 months (this period might differ), I record the meter reading and fixed and variable utility prices (the utility prices might differ between months, it's not common, though) calculate how much they should pay vs. how much they paid in an advance. I have 6 tenants. I use it for the following utilities: electricity, heating, and water. I've been doing it in Google Sheets so far, but AirTable interfaces would be useful for me. I'm trying to figure out the tool's structure: if I should put the dates of the readings in rows and the utility kind in columns or the other way around. Do you think it makes sense to use Airtable for this purpose?  Thanks for your help.

Hello All,I've been attempting to create a more efficient way for my organization to sort through volunteers and create a matching system to the elderly. So far, I've created two main tables: Volunteers and Old Friends that are populated by a use of forms. There are certain columns that are referenced in the forms controlled by other tables i.e. hobbies. Using Scripting in an attempt to create a system that can sort through the Volunteers table and Old Friends table to match based on the hobbies (to start, I plan on using other variables once I get this part working). Any help would be greatly appreciated!Here are the links to the two tables:Volunteers - https://airtable.com/app63KIAsdReM2VbZ/shrsqIBAQVIhCcfEAOld Friends - https://airtable.com/app63KIAsdReM2VbZ/shrLDOcutrx5CNfjRThis is what I have from Scratch but I'm unsure where I am going wrong: // Fetch the tableslet volunteersTable = base.getTable('Volunteers');let oldFriendsTable 

Is there any way to use formulas and linked at the same time? I want a column to add data from the columns A + B + C to = ABC and then have ABC show up on a different tab. Is that possible? 

I'm using a public airtable view with a button that passes data to a web form. Works on desktop but not on mobile.looks like this: "https://myurl.com/bid?bid=" & ENCODE_URL_COMPONENT({Deal Name}) & "&new=" & ENCODE_URL_COMPONENT({Current Bid Value}) & "&id=" & ENCODE_URL_COMPONENT(DealID) I had to flatten a lookup cell and turn it into {Current Bid Value}, which works. But for some reason on mobile it clears the query string. It should pass something like this: https://myurl.com/bid/?bid=Joel&new=120000&id=recaAB2Q4b8H2iYgL  Instead of: https://myurl.com/bid/ any suggestions?

Hello! Here is my situation and what I would like to get accomplished: I have three tables, 1,2,3. T1 has a list of candidates, who will either pass or fail (based on my selction of a single select value). For those who pass, I want to add their data to T2. Now this is simple using a Linked record in T2 that points to the record in T1. I am using T3 to host values. I consume these values in T2 as lookups in order to make some calculations. (eg. if score<T3 score then “expert”) My question is, when I add a new person from T1 to T2, such that they appear in t2 thanks to the linked reorc functionality, how do i also call the records from t3 in order to keep doing the calculations? It seems every time there is a new record in t2 i have to also copy paste the linked records from t3 for them to show up in their respective lookup columns for the calculations to make sense. I tried filtering by those t3 linked record values thinking that any new record will auto-populate the corresponding l

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 any errors, it just erases everything after that first if-and. Im sure it's something small that Im doing that's causing it not to work but I cannot seem to figure out exactly how to write this in a way that returns the values I need. This entire document is essentially going to be built like this because it relies on data from 3-4 columns. Thanks for your help.

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({Erstzulassung}, 3, 'years')), 'MM.YYYY')  

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  ABCDE1Q1Q2Q3Q4Next  Reminder23/3/20246/6/20249/11/202411/11/202411/11/2024Appreciate the help !

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.