Formulas

Hi all, first-time poster. I'm working with form submissions right now, and I'd like to check if the free text of a Location form field matches any value in an existing table's list of locations. For instance, if we received 'Bayview South' in the 'Location' form field, then I would want to check whether 'Bayview South' appears on the below list:  ABLOCATIONSUMBayview North1Bayview South1Sea Breeze1

Hello Everyone,I am having a hard time with a specific formula. I want to set a formula that will calculate a certain percentage based on what driver is selected in the driver field. Example: for the Dispatch % field i need a formula that will go like this. If driver is Dima set Dispatch % to be {Total invoiced} *.13 if Driver is Alex set dispatch % to be {Total invoiced} *.07 Thank you for the help! 

is it there is a way, preferably with formula/s, to perform element-wise multiplication of two arrays of numbers?For example, if I have two fields with arrays [1, 2, 3] and [4, 5, 6] respectively, can I write a formula that produces [4, 10, 18]?_I would also need a formula for:Column1 (array 1): 2, 3, 5Column2 (array 2): 5, 4, 1Expected output: 7, 7, 6is this achievable?

Hiya all!  So, I am trying to create a formula that uses each of the checkboxes in my table as a way to track payments that have come in.  I need each checkbox to equal/represent to the amount listed in the {Payment Amounts} column and for the number of payments that have come in (ie - the number of checkboxes checked) to be represented by a progress bar where the final tuition amount is listed in the table too as {Tuition}.  I have the below pasted formula so far, but its obviously not working: VALUE(SUBSTITUTE(SUBSTITUTE({Tuition (from KCIT Tracker)}, "$", ""), ",", ""))

Hello,I've been using a very hacky method of generating a Markdown table using a formula in my base. The below example uses Markdown in quotes for the table structure, and then pulls from field data to populate the table.Example: " # DIVINATION " & "\n" & " | Divination | Result | Description | " & "\n" & " |-|-|-| " & "\n" & " | " & " Soothsayer's Deck " & " | " & {Soothsayer's Deck} & " | " & {Soothsayer's Deck Description} & " | " & "\n" & " | " & " Constellation " & " | " & {Constellation} & " | " & {Constellation Description} & " | " & "\n" & " | " & " Position " & " | " & {Position} & " | " & {Position Description} & " | " & "\n" & " | " & " Moon Phase " & " | " & {Moon Phase} & " | " I then use an automation to populate a "Calculation Output" field using an example from

I'm trying to calculate cost/price in my shop by hourly rate. I have the Time Tracker linked with my Duration Field, but something is off in the calculation. IE one duration is 0:43 (0 full hours;43 minutes) but the calculation cost is showing $3,870. It should be $64.4999 with the formula (Duration/60)*90. How do I get it to calculate the correct amount wether the billable hours are 1h:43m or 0h:43m? Thank you in advance

I have two rollup field I need to evaluate and for some reason I can't get it to work.Field1: {rollupStockMinimum}Field2: {rollupNonStockMinimum}Field1 is empty, but Field2 is not.I only want the formular to do something when there are something in both fields.I've tried with this very simple formular  IF(AND(Field1, Field2), True, False)   However for some reason it evaluates to 'True' despite Field1 being emptyI've tried to just do IF(Field1, "True", "False") where it evaluates to False.What is it I'm missing or don't understand?

I'm trying to have the formula with a dateadd calculation be blank of a certain field is blank. However if I put the  DateFormat function around the whole If formula it will either result in an error if the input is Blank or if any option could be blank it will not allow the Formula Formatting as a Date field either way. How can AT recognize a formula field as a date field if the date could be blank based on a conditional formula or related blank field???  DATETIME_FORMAT(IF({Weekday Planner}=BLANK(),BLANK(),DATEADD(TODAY(),(({Auto Date 2}-(WEEKDAY(TODAY())))*24)+15,

Good morning,I don't understand why the function is refused, even though the content is basic. Do you have an idea?Thanks

Okay guys, got a doozie... Basically, in the screenshot I have a table, which is the main table that 99% of things sprout off from. We have customers, who through a webflow form are able to select accessories to add on to their purchase. That gets relayed to the table in the form of a checkbox being selected/unselected. Here's the problem -- I need to calculate accessories inventory, for each individual column AND be able to automatically update the rolling amount in another table, which uses the accessory name as the primary record. I've tried EVERYTHING I can think of from linked records, to roll up fields, to automations, and formulas. If anyone can calculate how this can be done I'll literally send you a $100.

I want to combine cells into one address block, but if there are no addresses I want the cell to remain blank. I'm having trouble creating this. Right now I'm getting a random common in the box: This is the formula I'm using: CONCATENATE({Address 1} & "\n" & IF({Address 2}, {Address 2} & "\n") & {Address 3},", ",{State / County}," ",{Zip / Postal Code})

Hi there,How can I enter hours for one tasks and days for others in the same project?Thank you!

Hi there,For the same task, how can you define a Daily Time Required of 100% on certain days or periods, and a lower percentage on others?Thank you!

I generate records through the week that all pertain to an event on Saturday. For organising these records into a week-by-week historical view (and for ease of human use on Saturday), I want to be able to automatically tag all of these records for the Saturday they pertain to. I have searched and tried a few things - this is the formula I got closest with:IF(WEEKDAY(Created)=6,DATEADD(Created,0,"days"), DATEADD(Created,6+WEEKDAY({Created}),"days")) Where "Created" is the date the record is generated. However, this generates a record for the previous Saturday. I don't really understand the formula I've cobbled together from old threads well enough to understand why. Does anyone have any advice to get it to fill that date as the following saturday from the "dat

I have a formula setup to show a status based on an 'Expiry Date'.This works from a date column {Expiry Date} with a column that works out the difference in days called {Diff}, this uses the formula: DATETIME_DIFF({Expiry Date}, NOW(), "days") Then is the status column I am using: IF(Diff <= 0, "❌", IF(AND(Diff > 0, Diff <= 28), "⚠️")) This works perfectly, my question is how do I add an additional "✅" status for

Hi, Rookie question; I have 3 columns “Full Name” (which has the formula to join the next two columns) “Last name” and “First Name”, putting a comma in between. The formula I’m using is: CONCATENATE({Last name}&", "&{First name}) The issue is when Last name and First name are blank (empty records), the formula runs indefinitely in the Full Name column creating rows and only putting in the comma (see pic). How do I stop this?

Hi Airtable Community, When applying the TRIM function to fields that are lookup fields from another table I receive an error. However when testing other formula’s E.g. Concatenate that does work this particular situation using Linked/lookup fields from another table. I would assume that the all functions gather data in the back-end in the same way. Can one confirm this is actually not possible to use TRIM in combination with Lookup Field type? Thank you

Hi! I am unsure if it is possible to create a formula to make this work for my school, please help!I have a base created to track all applicants. We offer 5 trade classes each held 3 times a year that have a minimum age requirement; age 16yrs and 8mths (Culinary), age 17yrs (Welding and Phlebotomy), age 17yrs and 6mths (Heavy Equipment and Electrical). Currently I have a formula for DOB and age as of the current date. I would like a way to populate which upcoming class the applicant will first be eligible for. e.g. Applied for Culinary Arts, will be age eligible starting with our fall class in October of 2024. Right now I am plugging each DOB into an age calculator based on our start dates and selecting a drop down myself but would love if I could make this work with a formula in Airtable! Thanks!

I have a extremely complex Airtable formula that I need to figure out for a financial company.It's a 3 step formula, calculating Grant $ amount generated per project within a 12 month time frame which begins at client onboarding, so each client's time frame is different.

We have a database of transactions that is monthly via csv import. This contains information like transaction id, amount, duration.Most transactions are simple one-time occurrences; however, some transactions happen over the span of months.For these long duration transactions, we apply a partial payment at day 30, 60, 90, etc. until the final payment which is a remainder of the total transaction. The issue we run into is that the transaction length and amount can adjust part way through.For example, transaction A has a value of 1,000 and duration of 40 days. Day 30 has passed and day 40 is in the future so our system flags this transaction for partial payment.Calculating the day 30 partial payment is simple:Daily rate * 30(1,000 / 40) * 30 = 750The issue happens when we import the following month's transaction data. transaction A imports again, but this time it has been adjusted to have a duration of 43 days and value of 1,100.

Hi there, i'm having issues with letters' case in my filterByFormula text and API call.I'm trying to do a searching box where user can give an input of what they are looking for, and Airtable fetches from a big database. Right now if i'm looking for "Hilfinger" it actually works alright and returns a few records, but if I type "hilfinger" it won't find anything.Here is my API call, i hope you can help:https://api.airtable.com/v0/appk7bV1OTtj*****/Offerte?filterByFormula=SEARCH(%22TEXT+HERE%22%2C+%7BNome+Oggetto%7D)&sort%5B0%5D%5Bfield%5D=Identificativo&sort%5B0%5D%5Bdirection%5D=descWhat would be ideal to happen is that Airtable gets the records even if cases are lower of uppercase.Thank you very much, any help would be much appreciated

Hey Wonderful Community! I am currently having an issue with a formula that I am trying to show as a progress bar. Here are the specifics: I am calculating the percetage of time passed between a start date and an end date. The formula seems to be correct: ROUND((DATETIME_DIFF(NOW(), {Start Date}, 'days') / DATETIME_DIFF({End Date}, {Start Date}, 'days')) * 100, 2) However, when I go into the "Formatting" section of the Formula Field, it gives me a very large percentage which seems to be incorrect (in this particular case the preview shows 345600% but in the formula section, the preview shows 35.56%. I

I'm trying to write a formula that will count days between two dates (assignment_start_date and assignment_end_date) that overlap the current week, not counting weekends.First example:current date is 4/5/2024assignment_start_date =  3/25/2024assignment_end_date = 4/2/2024The result of this would be 2. As there are two days (4/1 and 4/2) that occur during the current week (3/31 - 4/6) and are in the range of 3/25 - 4/2Second example:current date is 4/5/2024assignment_start_date =  3/25/2024assignment_end_date = 4/15/2024The result of this would be 5. As there are 5 days (4/1, 4/2, 4/3, 4/4, 4/5) that occur during the current week and are in the range of 3/25 - 4/15I've tried several versions of this with ChatGPT, however, I have yet to have any luck. The closet I've gotten is the following. However, sometimes the days are correct, but sometimes they are anywhere f

I have a switch formula setup to calculate Experience Brief Due Date and Opportunity Case Due date based on the Estimate size of the project, when an item was added during Quarterly Planning.Is there a way to add onto this formula to return the "Created" date if "When was this added to the Plan?" does not equal "Quarterly Planning"?Current Formula:IF({When was this added to the Plan?}="Quarterly Planning", SWITCH( {Estimated size},"Small  (2-4 weeks)", DATEADD({Ta

I have a switch formula setup to calculate Experience Brief Due Date and Opportunity Case Due date based on the Estimate size of the project, however I only want these values to pull in for items that are added during Quarterly Planning (not Intake-Ad Hoc Requests)-- Is there a way to set up the Switch Formula to be conditional on the value in the far right column (when the project was added)?Here is my current switch formula:Experience Brief Due Date:SWITCH( {Estimated size},"Small  (2-4 weeks)", DATEADD({Target in market date}, "-35", "days"),"Medi