Formulas

Hi, I work for a charity that distributes surplus food to members of the local community for a small weekly fee. I use Airtable to keep track of, among other things, how much each person pays each week, versus how much they should’ve paid. I use two linked tables to track this - one on which I record each individual transaction, and another that uses rollup fields to add each member’s transactions together so I can see each member’s history at a glance. I’ve set it up so that the Rollup table shows me how much (if anything) a member owes us in total, but the actual maths for that is done in the Transactions table. At the moment, one food delivery costs £3, but we’re about to increase that to £4. My trouble is with how to get Airtable to reflect this change. If I just change the formula for the amount owed from {Packages delivered}*3 to {Packages delivered}*4 then it’ll re-calculate every member’s payment history and tell me that everyone is £1 per week in debt, so I’m hoping there’s an

I’ve been trying to figure out how to make this crazy formula happen, but I’ve hit a wall. Can it even be done? Check out below and let me know how you’d accomplish such a thing… In the base, users select from a list of deliverables via a linked record field that contains everything a client can order for a particular asset (InstagramAd, SnapchatAd, Master, etc.) Each deliverable has a generic file title that follows a specific convention: Everything between the exclamation points is replaced with information specific to the asset when the deliverable is chosen on the Order table. So the above generic file title auto-generates this asset-specific file title: Figured it out :slightly_smiling_face:

How would I remove everything after *? Using this works but also gets rid of the ones that only have 1 name. LEFT( {Name}, FIND( " * ", {Name} )-1 ) Maslow *Loyal, Lovable Companion Max Bella Hope’s Boy 2 Hope’s Boy 1 Chase *Snuggle Bug (pending)

I’m looking for help writing a formula to show what pay a staff member has earned based on a variable. I have hours worked (at 30 per hour for one role, and 25 for a different role), and trainings complete at 50 per session. How do I write a formula that looks at the “role assigned select field” and calculates based on the correct rate of pay? Thanks,

I’ve created a formula to subtract no.of days from a date, this has worked, however a number of the fields are blank so returning an error, I’ve created a separate formula for that too which also works, but I don’t know how to join them together in the same formulae? DATEADD({Date auto-renew goes into effect},-{Auto renew notice period},‘days’) IF({Date auto-renew goes into effect}, " ")

Having trouble with an IF formula. IF({Job Size}<=10,DATEADD{Order Start Date,7,‘days’},DATEADD{Order Start Date,14’days’}) I’m trying to generate a date based on our ‘Order Start Date’, where if the integer entered in the ‘Job Size’ field is less than, or equal to, ‘10’, it will add 7 days to the ‘Order Start Date’, and if not (specifically a larger number), it will add ‘14’ to the ‘Order Start Date’. I’m not seeing what I’m doing wrong :woman_facepalming:

Hey Everyone: I have a form that takes multiple inputs such as Date/ Status/ Update String. They all link to another table where I can see the most recent update per item. So as my team updates items from week to week I have a view of the most recent. The issue is that not each input updates from week to week. So I want to use a conditional formula in my form table that will see if the input Update Status is blank and if it is then take the previous update that was not blank. Having a hard time figuring this one out. I hate that forms if left blank blow out a record that was there. I should be able to conditionally control those by saying if no response keep previous.

Table A contains individual evaluations of project performance. I want to create Table B that summarizes the evaluations by Project Team (average of individuals’ responses for a specific project). Table A fields include: Project Name I Year l Responses to Evaluation Q’s What is the best way to link both tables so that individual responses for a specific project automatically appear altogether in 1 cell in Table B? Below are the tables that I hope to create. All the separate responses in Table A to autopopulate in Table B according to the projects they are linked to so that I can average all responses by projects in Table B.

We have a weird way of keeping track how many interviews our interviewers have done. I would like to know how to count the number of times “1” comes up in each cell across all columns from Jan 2020 - August 2020. So in my simple way of formulating it would be something that translates the following (number of times “1” comes up in Jan2020) + (number of times “1” comes up in Feb2020) + (number of times “1” comes up in March2020) + (…etc etc). You can access a view example here: https://airtable.com/shrVjcpeWwW1jC51n Let me know if something is not clear! Hope somebody has some ideas or an answer :slightly_smiling_face: Thanks in advance.

Hello everyone! I need help with this situation. From cells with text followed with (number) I would like to extract just the number value to be able to sum in a new column. See the example of what Im looking to do it. Thank you very much!

I am running a membership organization, we have “Member Since” that is a year, e.g 2014, 2018, etc. I want to calculate on a column to the right, how many years they have been member. Something in simple words like =TODAYS YEAR - “Member Since” (in the picture it is “PGM Since”) So that this adapts to every year. Otherwise I can just keep it to =2020-“Member Since” Anybody can help with that? How to get automatic todays year into a formula

Hello Everyone, I think this should be fairly simple. I have two tables. Customers (auto populated when an order is made) and the Quiz Results (auto populated when a quiz is taken on the website.) I would be simple to link records and look up the appropriate data. We want it all to be automated though. The email address is most likely to match can I search(email) to link records then look up the fields. Thanks in advance @Mohamed_Swellam @Justin_Barrett BH

Hey Everyone: I am having an issue that I am sure is solve-able but I am stuck. I have one table that houses all my company roadmap features. I then have an additional table that has a form for my team to go in and fillout weekly updates. The issue is that not every column updates each week. Right now I have the Roadmap Items Table rolling up the max date and then using a formula on the other table to use that date to only pull in the most recent update for each column. That all works fine. Where it falls apart is when a user submits the form but leaves one of the section unfilled because it doesn’t need to be updated from the last time. Is there a way to set it so I get a super set of the most recent update for each of my columns across multiple entries? Description / Planned Activity/ Update / GTM Date?

If a date and time field is not blank then copy to date column

Hey there. I’m trying to create a field that populates the fiscal year that a project is published in, as it’s different from the actual year. So if the date of the project is between December 1, 2003 and November 30, 2004, it’s FY04. And so on for the following years ad infinitum. Any help would be appreciated; I’m having trouble with date formulas and I trawled through posts but couldn’t find a solution that worked for me. Thanks in advance.

I’m trying to use a formula field to calculate a date for benefits eligibility like health insurance. For example, if an employee starts on August 6th, 2020 we require them to wait until the first of the month following their first 90 days of employment until they are eligible. With the DATEADD formula, I can add 90 days, but I’m not sure how to take the result from 90 days to the first of the following month. With the example above the result I’m looking for would be December 1st, 2020.

Hi - I am trying to write out a long nested IF statement and am having issues! Can anyone help with this formula? IF(AND({What category survey did you work on?}=“Category 1”,{Did you also conduct Qual Coding?}=“No”),{Cat 1 Survey Rate (LC) (from Countries | 60dB base)}, IF(AND({What category survey did you work on?}=“Category 1”,{Did you also conduct Qual Coding?}=“Yes”),{Cat 1 QC Survey Rate (LC) (from Countries | 60dB base)}, IF(AND({What category survey did you work on?}=“Category 2”,{Did you also conduct Qual Coding?}=“No”), {Cat 2 Survey Rate (LC) (from Countries | 60dB base)}, IF(AND( {What category survey did you work on?}=“Category 2”,{Did you also conduct Qual Coding?}=“Yes”),{Cat 2 QC Survey Rate (LC) (from Countries | 60dB base)}, IF(AND({What category survey did you work on?}=“Category 3”,{Did you also conduct Qual Coding?}=“Yes”),{Cat 3 QC Survey Rate (LC) (from Countries | 60dB base)}, IF(AND({What category survey did you work on?}=“Category 3”,{Did you also conduct Qual

I’m getting stuck on this formula, please help! I have the following fields: Status (single select) Due Date (date) I would like to have a new field “days past due” that shows the number of days past the due date IF the status is “past due”. Otherwise, leave the field blank. I’m probably close (??) , but I tried the following two methods and am getting an error. IF({{Due Date} >= TODAY()}, (DATETIME_DIFF()({Due Date},TODAY,‘day’)), " ") IF({Status} = “Past Due”), ((DATETIME_DIFF()({Due Date},TODAY,‘day’)), " ")

I am trying to divide the total of two fields. The first is currency, the second is a number. Wanting currency as a result; eg total weekly pay / total hours worked = average hourly pay. Here’s what I’ve tried and both have come back as INFINITY: SUM({Total Paid})/SUM(Hours) SUM({Total Paid}/{Hours}) This gets an error: SUM({Total Paid})/SUM{(Hours)}

To minimise duplication of data across my table I’ve converted a large number of Single Line Text fields to Lookup fields linking from a central sheet. Unfortunately the main use case for this data was access via URL parameter filterbyformula method from the branched sheets (this is important as there are some unique values on these sheets too). I was previously using: filterByFormula=Find("Tuesday", Day) The associated values in the to Name, Description, Price fields were being returned to populate components in an interface design tool I use called Framer. Unfortunately I’ve just learned that because they are now Lookup fields (eg calculated fields) it’s not that simple. The values are no longer populating. Testing shows its the Lookup fields causing the issue and a bit of research revealed that I may be able to to use an array function to bridge the gap. I’ve tried versions of the following formula but to no avail: filterByFormula=Find("Tuesday", ARRAYJOIN(Day)) Do I need to refere

Hello! I am somewhat new to airtable so still trying to figure out some basics. What I am trying to do here is take the average values of each categories per seven days and average them. Is this possible? I.e. I would like to take the measurement of weight in lbs from days 1-7 and average that giving the result in another column, then for 8-14, 15-21, etc . . . I want to do this also for the columns as well. So far I cannot find a way to do this. Bonus: If it’s possible to specify this by the Date field every seven days starting on a certain day even better!

This is the current formula I am using; IF({3P Impressions}>0,{Booked Revenue}-({Booked Revenue}{3P Pacing}),{Booked Revenue}-({Booked Revenue}{1P Pacing})) But when the value returned is negative, I’d like to display “0.” The format of the field is currency.

I am new to airtable and I am using airtable as our Products database, and when we make a new product it needs to get a EAN-13 barcode. We get the first 9 numbers from the government, then we add the next 3 and the last one is calculated. Field 1 = 9 first diget Field 2 = auto number Field 3 = adding “0” so the cell has 3 number in it Field 4 = Field 1 & Field 3 Field 5 = calculated last number Field 6 = Field 4 & Field 5 The formula is: CEILING((VALUE(MID({EAN+rad},1,1))*1)+(VALUE(MID({EAN+rad},2,1))*3)+(VALUE(MID({EAN+rad},3,1))*1)+(VALUE(MID({EAN+rad},4,1))*3)+(VALUE(MID({EAN+rad},5,1))*1)+(VALUE(MID({EAN+rad},6,1))*3)+(VALUE(MID({EAN+rad},7,1))*1)+(VALUE(MID({EAN+rad},8,1))*3)+(VALUE(MID({EAN+rad},9,1))*1)+(VALUE(MID({EAN+rad},10,1))*3)+(VALUE(MID({EAN+rad},11,1))*1)+(VALUE(MID({EAN+rad},12,1))*3),10)-((VALUE(MID({EAN+rad},1,1))*1)+(VALUE(MID({EAN+rad},2,1))*3)+(VALUE(MID({EAN+rad},3,1))*1)+(VALUE(MID({EAN+rad},4,1))*3)+(VALUE(MID({EAN+rad},5,1))*1)+(VALUE(MID({EAN+rad},6,

Hi all, Who can help me out with converting a rollup result to a specific data type? I use a rollup field with ARRAYUNIQUE(values) in which the values are dates. Normally, this will be one single date (since I am checking if all values in the array are the same). The field of origin is of the date/time type (which I have enforced by a formula and Airtable recognizes it as a date). However, the date as displayed in the rollup field can not be manipulated by any formula in another field. It appears as if the data type of the rollup field (which is an array, of course) can not be determined, which makes it impossible to convert. Things I have tried that did not work: ARRAYJOIN(ARRAYUNIQUE(values), "; ") to convert the array into a single string. DATETIME_FORMAT(ARRAYUNIQUE(values), ‘D/M/YYYY’) as the rollup formula to convert the array into a date (or even an array of dates) Any ideas?

Hi there! I’m trying to build out a formula that allows me to use checkboxes to update an application status column. As of now, this is my current formula: IF( AND({Reviewed}, {Accepted}), ‘Accepted’, IF( {Accepted}, ‘Error!’, IF( NOT({Reviewed}), ‘Pending Review’, ‘Rejected’ ) ) ) My hope is to have a three-column system where if you check {Reviewed} it displays the formula column as ‘Reviewed’, {Reviewed} and {Accepted} as ‘Accepted’, {Reviewed} and {Rejected} as ‘Rejected’, and {Reviewed} and {Accepted} and {Rejected} as ‘Error!’. Please let me know how to go about this. Major thanks!