Formulas

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

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

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" &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.