Formulas

Hi all: I'm trying to simplify the process of converting a short ID code to a longer ID code inserting leading zeroes where needed. The ID codes are structured to track Week, Day, and Segment numbersFor example, if the input data is 1.2.3 (week 1, day 2, segment 3), the necessary output needs to be formatted W01.D01.S03The wrinkle is that while day and segment are always going to be a single digit, the week value could either be one digit or two digits. I've managed to get it working with a series of breakouts that takes the input field, breaks out the week, day, and segment into separate field, adds leading zeroes where necessary, then concatenates them back into the final output id. But it takes 8 separate steps/unique fields to accomplish.  I'm looking for a way to do it in fewer steps. Here's how I have it working currently:Input: "ShortID" (e.g. 1.2.3, 12.7.1) -- manually entered 1) "BreakoutWeek" &n

Hello, I'm trying to check if my "inputValue_1" field occurs in a string pulled from a lookup field (named 'test' in the screenshot below). I'm trying to use this to check if inputValue_1 has a valid input, as defined from this lookup field.The formula I'm using is `FIND({inputValue_1}, {test})`, which should ideally return the position where `inputValue_1` occurs inside of `test`.  I've tried SEARCH(), but just get errors. This leads me to believe that FIND() is right, but something is off, since I am just getting position zero no matter what. I would expect `new south wales` to return 1, and `victoria` to return 18. Any help is appreciated.

I have addresses in multiple lines and am using REGEX EXTRACT in Airtable to separate the components (street address, suburb, state, post code) before sending them on for shipping labels.I've used REGEX_EXTRACT({text input}, "^(?:.*\n){1}") to extract the first line and this works in most cases except when the street address is over two lines (see example B).Can someone please help me with a REGEX EXTRACT that extracts every line except the last line (whether the address is 2 or 3 lines)?Or is there a simpler solution I'm not aware of? Example A1 Test StreetRichmond, Perth 3021Example B1 Test StreetUnit 6Richmond, Perth 3021For reference on how I'm dealing with the rest of the components...I've used REGEX_EXTRACT({text input}, "^.\r?\n(.)") to extract the last line (Subrub, State, Postcode) and then used REGEX_EXTRACT({text input}, ".*(\d{4})") to extract the post code.Once I have the pos

Hello,I have a multiselect column "Products" with empty, single, or multiple strings of product name inside.E.g. Xbox 360, PS4, etc...I want to create a separate column's, based on data from another table, that would take into account that column.1. Would calculate number of separate strings (Clear)2. Would replace string with number (Price of that product) (Data of price is stored in another table)3. Would calculate total sum of all those product prices. How would you recommend to achieve that, via Replace, Scripting or something else? And how? Would highly appreciate help!

Hi,We create forms to capture registrations for workshops. The dates and times of these workshops are set as Singapore time, but our participants register from all around the world, so we need these dates and times in the form to show up in their local timezone.This used to happen automatically, but now it doesn't.Can someone show me how we can have these dates and times show up in their local timezone? But in this case, I do not know which timezone the participant will be in when they are registering, so do not have the specific timezone.Thank you in advance

Hello, I want to calculate the total cost per tickets ONLY IF they are a specific type, and then I want to deduct that from a total amount that I set manually. My fields are currently set up: Total tickets (# field)Per ticket cost ($ currency)Total cost (simple formula: {Per ticket Cost}* {Total Student tickets}) Revenue source (single line text)Right now, I have "IF({Revenue Source}="X", {Total cost}-40,000)", but it's returning 0. Any tips? Thank you!! 

Hello everyone!So I've written a formula, it works, but I need a few tweaks to it so that it runs exactly as I want it to. The thing is: I am stuck. The formula looks like this:   IF( {Betalingsmetode} = 'PBS', IF( OR( WEEKDAY({Trækdato (FarPay)}) = 2, WEEKDAY({Trækdato (FarPay)}) = 5 ), {Trækdato (FarPay)}, IF( {Trækdato (FarPay)} < DATETIME_PARSE('10-' & DATETIME_FORMAT({Trækdato (FarPay)}, 'MM-YYYY'), 'DD-MM-YYYY'), DATEADD(DATETIME_PARSE('10-' & DATETIME_FORMAT({Trækdato (FarPay)}, 'MM-YYYY'), 'DD-MM-YYYY'), IF(WEEKDAY(DATETIME_PARSE('10-' & DATETIME_FORMAT({Trækdato (FarPay)}, 'MM-YYYY'), 'DD-MM-YYYY')) = 3, 0, 7 - WEEKDAY(DATETIME_PARSE('10-' & DATETIME_FORMAT({Trækdato (FarPay)}, 'MM-YYYY'), 'DD-MM-YYYY'))), 'days'), DATEADD({Trækdato (FarPay)}, MOD(10 - WEEKDAY({Trækdato (FarPay)}), 7), 'days') ) ), IF( OR( WEEKDAY({Tilf

Hi Airtable Community!I'm not sure what I'm doing wrong with this formula. We are changing our pay rates on April 1st and I want to program my payment field to reflect this change. My table has a date field called "Fecha !!!" and a lookup field for our new rates and our old rates.If the record has a date before April 1st I want it to use the old rates. If it has a date on or after April 1st I want it to use the new rates. Here's what I have so far! I'm a beginner! Thanks for your patience and  advice 🙂

I have four Airtable fields {Total Cost}, {Event Date & Time}, {Equipment Charge}, {Event Duration}, and {Number Of Techs}If the {Event Date & Time} happens during the Day time, the rate is $69.If {Event Date & Time} happens during the Evening the rate is $79.If the {Event Date & Time} happens during any holidays, the rate is $105.If the {Event Date & Time} happens during a weekend, add $60 to the {Total Cost}.I created a formula to calculate the {Total Cost} but it is not working. Your help will be very appreciated.See the Formula below:------------------------------------------------IF(DATETIME_FORMAT({Event Date & Time}, 'h A') >= '7AM'AND(DATETIME_FORMAT({Event Date & Time}, 'h A') < '7 PM'),{Event Duration} * {Number Of Techs} * 69,IF(DATETIME_DIFF({Event Date & Time}, DATETIME_PARSE('2023-01-01'), 'days') IN [0,1,2,361],{Event Duration} * {Number Of

HiI've more than 10,000 questions and counting, and many questions added daily, the issue is finding and labeling then deleting the duplicate, duplicate are not exactly unique same question written with similar words but different context , and sometimes exactly the same but with extra word.i used the 2 duplicate extensions and it is very useful but it need too much manual work (see attachment )so my question, what Formula can i use to label any question in separate column and flag the question as duplicate and linking the duplicate form with lookup/rollup, so i could ? i wish the result to be like that . thank you , i will really appreciate your answers.

Hi,In table Courses, I have a "Related Lessons" field that is a link to table Lessons"Related Lessons" field contains 3 Lessons' records: L1, L2, L3In table Courses, I want to create a second field, named "Open Related Lessons" that contains 2 linked records only: L1, L2. The remaining L3 records should be excluded based on filters.--> How to achieve that?

Hi everybody, I'm trying to come up with a formula that would add after some text (taken from another field) the number of spaces needed to reach a total number of character.Let's say I have to get to 10 total character, starting from the text from a field name "Text".If text is "ab" I would need the formula to add 8 spaces after "ab" in order to reach 10 total charactersIf text is "abc" the formula should add 7 spaces after "abc" to reach 10 total characters, and so on.Is this possible?

Hello there,I have been using Airtable for a month and I absolutely love it.I am creating a basic HR system. I want to create a formula for the employees’ paid leaves.How do I automatically make it add up in Airtable, using the Hours off (8 hours/day)?Is there a formula for that?Thanks in advance.

Hey guys, I am new to AirTable. Here is the problem I am facingI have 2 tables, Apartments and Locations1. An apartment can only have 1 Location.2. A location can have multiple apartments. I want to do an API call to filter apartments with some contraints which will return me a max of 100 apartments.It is possible that all those 100 apartments belong to 1 location so my end result is 1 location. Is there any way I can add a filter that returns me unique values?Eg. If apartment 2 has the same Location as Apartment 1, return it just once and continue. I want my end result to be 100 locations in 1 API call.

Hi!I was wandering if it is possibile to use customers' emails as unique ID through with connect different tables (ex. Table Contact, with only info about the customers; table Customer journey, with info about first contact, conversions,...)Thank you!

I'm new with these and I think i'm having a big mistake but I cannot find it.I'm trying to extract the number of hours for an specific duration record.Obviously I use HOUR command but it doesn't work!As you can see in screenshots, the field Hours works in the following way:Hour({Time})But, it generates ERROR!What am I doing wrong?Thanks!  

Hi all, is it possible to have a formula that searches if a text field contains at least one item of an array?Specifically, I would like to achieve the following:I define topic names and related keywords for each one manually in table #1:TABLE 1: TOPICName (string) | Keywords (Multi-select) Animals | dog, cat, elephant,Fruits | apple, banana, orangeColors | red, blue, yellowI have some articles in another table and I want to update the Topic field by checking if the article title contains items of the array field from table 1.TABLE 2: ARTICLESTitle (string) | Topic (Lookup) (this should be the field updated by the formula)The dog is sleeping | AnimalsThe apple is red | Colors, FruitsI k

Hi Airtable ExpertsWe have a formula field field which calculates the conversion from euro to dollar, including the import duty. The outcome is shown in USD (field  "exwUsdStandard" = currency, 2 decimals to the right), this works fine.The next field ("exwUsdSpecial" = currency, 2 decimals to the right) shows a price in USD), is shown perfectly in the record. This value is not calculated with a formula.Now, I want to combine these two fields into a new field ("priceExwUsd"), however, I am not able to format it as currency $ with 2 decimals at the right side of the ",".What can I do to make it as currency, $, 2 decimals to the right?Thank you in advance 😄Gerben (from the Netherlands)  

Hi,I am needing help creating a formula.It would be if (15% referral says no then it would calculate the daily rate times 15%), if (15% referral says yes then it would be daily rate)

I need help finding all formulas used in a base that would have used 2022 in the formula to either point to 2023, or re-write.  If i need to export all formulas to review, that would work too.

I'm trying to build a formula that will look up a currency value from a record, determine whether it is between two ranges of currency, and then enters a percentage (the federal poverty level) in a new column. I'm getting errors in the formula below. Any ideas about what I'm doing wrong? IF(  {yearly income} < 22763,    "125%",    IF(AND(      {yearly income} > 22763, < 27315),        "150%",

Hi, I have a users table with a sigle select column called "status" with the values "Not verified" and "Verified". When I create a new record, the Status field should automatically be set to "Not verified". Does anyone know how I can do this? (Couldnt find a solution). I tried an automation, but this is too slow. I am using Softr.io which accesses that field right after the row is created. That slight delay of the automation, already is too late. Many thanksSimon

Hey,Have some trouble with the summary bar. I’m trying to put up a sales report where I need to see the average in percent. According to the former Excel sheet against airtable I get two different values.In the Excel sheet I get an average of 10,9% and in airtable I get the average 11,83%, using the formula (((sum - {Brt.f.}) / {sum}) -1)*(-1)

the circled part is the focus… let take for instanceIf the "display attachment field" is not empty and the "cloud hosted field" is empty, a formula to display the display attachment field and not the cloud link fieldIf the "display attachment field" is not empty and the "cloud hosted field" is not empty, a formula to display one of the field

Hi there! Essentially, I'm triggering content to "publish" to a site 1 hour after it's been approved. How do I write a formula for 1 hour AFTER the Accepted-time field?