Formulas

Hello All,I am trying to figure out how to perform a calculation if possible.I have jobs that are sorted by date. Currently, data is grouped by date. I want to take the flat rate for example $500 and divide that by the number of jobs. So that I can have a column that tells me how much each job was worth at the flat rate. So if there were 14 jobs and the flat rate is $500 each job for that day will get its portion of the 500. The number of jobs per day can vary. For example, on some days, I will only have 6 jobs but the same flat rate. In addition, I would like to know how many jobs are urban and how many long distances. I already have the data using a lookup by zip code. Just need to know the percentage of revenue that is urban and long-distance. Hope this makes sense. Been trying different formulas but cannot figure how how to get the count working. I appreciate any help or advise you may have. Thank you so much

I imported a financial report from PayPal. All of the Negative amounts in the "Amount" field use a "-" in front of the value. I want to create a new field that removes the "-" and just lists the value of the Amount field.  If the amount doesn't have the "-" symbol, I just want it to leave the record blank.  I tried using a substitute formula in the new field, but not sure how to get a blank value in the new field if the original amount value does not have a "-" sign. SUBSTITUTE(Amount, "-", " ") What would be the best way to do this? Thanks in advance.

I'm trying to build a formula that returns "true" every 180 days (and subsequent 180 days) after a record was created.  I'm heading down a very complicated route involving dividing  {Days Since Created}/180 and checking the length of the return result.I'd like to be able to build a "divides exactly" check into this:DATETIME_DIFF(NOW(),{Date Created},'days')Any ideas would be gratefully received.

HII have  "Creation Time" column for my records. The records are created when user submits the form.Everyday at 9 am or 10 am in  morning meeting, i need to show all records created between  " Yesterday 8am to Today 7:59am".Is there a formula to achieve this?I am not  good at formulas to if any one create one for me please.Regards 

Hiya, Basically want to know if there is a formula where if the tickbox "marketing brief" column is filled in it will automatically then remove it will remove the marketing brief option from the multi-select "outstanding" column? I've been looking on the forum and can't quite find anything similar. Thanks!  

Hello everyone, I'd like to make a formula with an IF statement that shows "OK" if a date is in the last 6 complete months. For example, if today is the 17th of March, it would show "OK" for every record since the 1st of September.Is this something possible?Thanks 🙂

Is there any reason for -0 in the result of the simple formula as Paid-Shipped? Is it interesting, as it shows only in one row.  

Hi! I need help creating a formula and not sure if it is possible. I have a table that has several tasks with a single select option of completed, missing, or n/a. I want to get the average score of the tasks that are marked as completed. Example: Task 1 is completed, task 2 is missing, task 3 is completed, and task 4 is completed. The average would be 75%. Or if Task 1 is completed, task 2 is missing, task 3 is missing, and task 4 is N/A. The average would be 25% and so on.  

Hi I'm a Plus plan subscriber so starting to explore AT more in depth. I have a built a base with a JOBS table that lists jobs by job number, and using various currency, numerical and formula fields, delivers a profit per hour figure for each job. It does this by dividing the total job profit by the number of hours spent on the job. A second table called TIME LOGGING is updated with forms submitted by staff and tags their time to a job. These individual time logs are rolled up in the JOBS table to deliver the profit per hour figure. The next part is where I need help. How can I now deliver a PROFIT PER HOUR PER STAFF MEMBER figure which takes into account that time they have spent on each job? On some jobs, several members of staff log time, so the figure must be proportional to the time they spent on the job. E.g. If the staff member logged time to two jobs, one of which was just them, and one of which was part of a team, this would calculate as follows: Job 1: Profit per hour is $100

I am trying to build a base for a company that delivers natural food for pets. The clients tells us the date they want their first delivery made, how often they want to receive it (every week, every 2 weeks, etc), the preferred day of the week they want to receive the deliveries (monday, tuesday, etc) and also the number of deliveries that are already paid for. Based on that I would like the base to automatically fill the future delivery dates for each client record considering the informations we have. This will be importante so that I can set up a calendar view later that will allow me to know all the clients I have to make deliveries on any specific date. Thanks in advance for any help and I am sorry if this is not the right place to ask.

Subrecords is the one aspect I don't like in Air Table and I am struggling with a case right now. Maybe somebody has a smart and simple solution. I feel my design is flawed but I did not find any other practical way to work this.Let's say I haveTABLE AField 1: Location name (text)Field 2: Sub-location name (text)Field 3: [Field 1] & [Field 2] (calculation field)Field 4:: A chekmark field to filter out sub-records, so I can a list of records without seing all the time I repeat Field 1.TABLE BField 1: Area code (text)Field 2: [TABLE A Field 3] (linked) What I would like is a list of unique records of Table A Field 1, with all the linked records in Table B Field 1.And I can't seem to find a simple solution. 

I am trying to figure our how to use rollups. Let's assume I have a list of applicants and associated interviews. The interview has "interviewer name" field.Soapplicant1 -> (interview1; interview2)Interivew1: JohnInterview2: RebeccaI am creating a roll-up and trying to figure out how can I access interviewer names (John, Rebecca).Rollup references gives some formulas that can be used in rollups, but is there a way for me to access the values?Let's say I want to send an automated email and sign it with the name of one of the interviewers. 1. SQL allows me to do something like ANY() to get a random name from the list of interviewers. Is there something similar in Airtable?2. What if I want to SORT BY interview date and access the name of the FIRST interviewer? (so not just any, but also do some manipulation with the array) is it possible?Thanks!

Hello communityAgain faced with this, hope to get a simple workaround for this where I can get a formula that extracts URL from a text field. Thanks

Hi everyone,I need assistance with getting the proper year in an ISO week start date so that I can get a valid week start date. I have the following formula: DATETIME_PARSE(DATETIME_FORMAT({Date Field}, "W"), "W") Month and day outputs from this method are accurate, but a year is not. It is stuck in 2023, the current year. Can somebody assist me? It's acceptable if I need to use more than one field to accomplish this, for example, one field to obtain the proper year and one field to obtain the correct day.Sincerely appreciated! 

We have multiple dates that we need to have automatically update for us in table.Example:Move-in date (4/1/23) plus policy day (60) = Lease end dateLease end date - NTV date (30) + 5 days= NTV date needArrival Instructions date = 2 days prior to Move-in date (4/1/32)How would you go about doing this in a table? 

Hi, Using the formula below I have an ERROR value issue when there is no date value available for {last deal stage change}. Can anyone advise how I would alter this to just leave a blank space or "no data" where there is no date value available in the date field column?I need to use the datetime format to tidy up and remove the timestamp TRIM({Status symbol} & " | " & {Pipeline} & " | " & "Stage last changed: " & DATETIME_FORMAT({last deal stage change},'DD/MM/YY') & " | " & {Deal Stage})Thanks!

Hi Community, I have 5 fields (checkbox) that can be selected or not, I need to assign a status triggered by these fields, if any field of the 5 is selected then a status will be set and if no one is selected It will assign a different status. Thanks for the help.

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"   LEFT(ShortID, FIND(".", ShortID) -1) 

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 post code I'm matching it against a database to find the Suburb and State. I did this because I b

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øjet d.}) = 3, AND( {Betalingsmetode} =

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 🙂