Formulas

I am trying to automate the calculation of an award based on an age category (“PVSA Age Group”) and a number of hours volunteered (“Total PVSA Hrs.”) (see chart). I think I need nested IF statements with an AND, but not sure. It’s not working so far (because I have a day job hello.) I need an output based on these two variables. Here is the first part of my formula, attacking only the second row (we have no contenders for KID category in the first row, just the remaining 3 rows): IF( AND( {PVSA Age Group}=“Teens”, {Total PVSA Hrs}<50 ), “No award” ) & IF( AND( {PVSA Age Group}="Teens”, {Total PVSA Hrs}<=74 ), “Bronze” )& IF( AND( {PVSA Age Group}="Teens”, {Total PVSA Hrs}<= 99 ), “Silver” ), “Gold” That doesn’t wo

I have used this formula to separate data that is simple 4 letter abbreviations and it works perfectly. However, the data is also associated with dates and times that I need to separate and have readable and separate in an email automation. When I use the above method, the dates (which read fine in their native field) come out looking like this Has anyone run into this before or have a solution?

I am trying to create a quality score for a series of job applications. I have a series of fields returning a count of how many “excellent,” :“good” “poor” evaluations each application received, expressed as a numeric value. ie Count-Excellent Count-Good Count-Fair etc. First I want to multiply each field by a unique value in order to build up a score, ie if an application got 2 “Excellent” ratings (shown in Count-Excellent as “2”), give it 5 points for each Excellent rating and thus returning a value of 10. And 4 points for each Good, etc. Then I want to add all those values together, producing a combined score, and THEN… divide by the number of reviewers, which is stored in a field called ReviewerCount and is also a number. Basically I want to do something like ((Count-Excellent * 5) + (Count-Good * 4) + (Count-Fair * 2)) / ReviewerCount and have it produce a score, but I seem to be getting hung up on the parenthesis and brackets

I am trying to use REGEX_EXTRACT to convert a username into an Email address. For example: WSSUMITS\smithfred => smithfred, then I can add the @domain.com to the result. The regex formula I came up with, through trial and error, is: REGEX_EXTRACT({Asset Last Logged In User}, '^(?:WSSUMITS)?([^:\\/\n?])+\\') However, I’m getting ERROR in the field. I don’t have much experience with regex. Where am I going wrong? Thanks in advance.

hi, I’m new to airtable and not much of a techy person so even when I read some of the comments for how to do a running total, I’m still so lost… I’m trying to create a monthly roll up to see how much money I should have at the end of each month but rolling up from the last month total. I am able to get as far as getting the Income - Expenses = Total but I tried using Previous Row function to get last row total to try to add it to next month’s total but it tells me it causes a circular reference. Or is there a way to add last month’s total with this month’s income? I’m at my end on trying to get a running total. HELP anyone… here is the view of the table… I need the previous row to be the last column roll up. I can’t set it up since it gives me circular error so only been able to use Delta for previous row to test it but it’s not the right value I need for the running total. how can I get the last column to be the running total?

I am trying to solve the following problem. I have thousands of registration numbers that always start with a “T” and are followed by 10 digits. This is how they are delivered to me T3090728379. But they all need to look like this T-309072837-9 which translates to the following format T-[9 digits]-[1 digit] The last digit can be anything between 0-9. What formula could I use to reformat these fields in Airtable? I managed to substitute “T” with “T-” but I do not know how to get a hyphen in front of the last digit. Is this something one would use the LEN() function for? (I have never been able to fully understand how this works…) I’d appreciate any help. Thank you so much in advance. This is how far I got: IF(STRING,SUBSTITUTE(STRING

Okay, so I’m new to AirTable, trying to move away from Excel sheets :slightly_smiling_face: I’m creating a forecasting tool for my sales department. For each project we forecast the number of products that we will sell. For my purchasing department, I’m creating another tool that will use the summarized number of forecasted products in calculations. So for example Project 1: Product 1 - 30 pcs. Product 2 - 10 pcs. Project 2 Product 1 - 5 pcs. Product 2 - 10 pcs. I need the sum of Product 1 (35 pcs) and Product 2 (20 pcs) available for further use in another table. How can I do this? These qty I would like to summarize in the Product table.

I’m relatively new to airtable and i’m working on a tracking and costing system for the project I am working on. I am creating security schedules which are usually the same for monday through to friday. they change on saturdays, sundays and public holidays. I want to count the number of saturdays and sundays in a given period to set the schedule for those particular days but the starting point is knowing how many there are… can anyone help out with this? thanks

Hi, I need help to split this string into four pieces and put them in four new columns… Maybe split two times with different delimeters? {“email":"mail@gmail.com”,“firstname”:“John”,“lastname”:“Hong”,“phone”:“1234567”} Column 1: mail@gmail.com Column 2: John Column 3: Hong Column 4: 1234567 Thanks in advance!

Hello, I am looking for a solution/formula that will generate in a Multi-Select field all unique dates between ‘start date’ and ‘end date’. I need that solution to generate a report with rollup fields and calculate the number of inhouse guests on specific dates, for exemple in hotel room management, or vehicles car rental. Thanks

First of all, I am a beginner at creating formulas. I only have experience with with basic math formulas. I am trying to create a field that show how long a product was on the market. I have two fields, purchase date and sales date. Purchase is the day we bought the items, Sales date is the date we sold it. I used the datetime_diff formula but I am getting NaN on items that have not sold yet. How do I create a formula that would show days on market for items that have not sold yet. In other words, the items that have been on market for 30 days would show 31 days tomorrow, 32 days the next day, and so on… Is a formula like that possible? If not, what are my options to remove the NaN appearing in the field?

Hi all, I’m trying to create a formula to help me calculate invoicing periods. If the payment date is before the 20th of the month the invoicing period is the current month, if the payment date is on or after, then the invoicing period will be the next month. Essentially, there should be two outputs, the first day of he current month or the first day of the next month. I’ve tried to build this with an IF formula, but I’m coming unstuck with the first half of the formula when testing as it doesn’t seem to be calculating correctly: IF({Date paid} < DATETIME_FORMAT({Date paid}, ‘20/MM/YYYY’), DATETIME_FORMAT({Date paid}, ‘01/MM/YYYY’), “test”) How anyone advise on where I’m going wrong on this one?

I have two fields full of product reference numbers that I want to compare for matches. The first is called Reference . This is a long list of stuff we make, some of which are out of date and out of stock. The second is called OldReference , which contains just the old items with remaining inventory. I want to search the two fields for instances where the reference numbers match, and then mark them somehow. I assume in a third field that returns 1 or 0, true or false, yes or no… Anybody know how to do this using a formula, lookup field, or some combination? Thanks in advance to anyone with a good idea. :grinning_face_with_big_eyes: -Frank

Hello, I’m trying to summarize information in a Linked Record field type collected through Forms in which respondents can select multiple linked records. We invite respondents to select their Subject Area Expertise, options provided are categories from a sector-wide set of standard taxonomy. The Subject Area taxonomy is organized in a hierarchical manner. For example: Level 1 (Category): Health (SE000000) Level 2: Nursing Care (SE060000) Level 3: Home Health Care (SE60100) Level 3: Hospice Care (SE60200) Respondents can select from the whole list without seeing the hierarchy. I would like to generate charts that summarize and collapse respondents’ answers. Using the example above, I would like the chart to collapse all individuals who selected “Home Health Care” and “Hospice Care” to fall under the bar/pie chart slice for "Health" I tried using a lookup field for each Subject Area’s c

I currently have the following IF nested statement. I don’t get an error when saving, but it only works when the first IF is found (13) and produces nothing for a result when the other two are in the identified field to check. Am I missing a key element to make the “false” portions of each IF statement work with a new nested IF statement? IF({⛔ Extender Weeks}=13, SUM({👌 WEEKS in Program (26 = 6mo)}+13, IF({⛔ Extender Weeks}=9, SUM({👌 WEEKS in Program (26 = 6mo)}+9, SUM({👌 WEEKS in Program (26 = 6mo)}+4)))))

Hello, I am in despair, I want to build a table, with UTM left. The UTM links are fixed, but still need to exchange names and discount code. In google Sheet this was totally easy, but in Airtable I just can’t get it right :frowning: Here are the fields, the function should take: https://www.website.com/?utm_source=instagram&utm_medium=influencer&utm_content=LINK_TO_FILED&utm_campaign=webseitecampain&utm_term=websiteterm&discount=LINK_TO_FIELD Thanks for your help

Hi there. I have a multiple select column that contains seven audience types, which have been added in a different order (no set pattern). I also know how big these audiences are (eg Generalists = 4406). I’m trying to write a formula that returns a total audience based on whatever has been selected in that multiple select box. EG if “Generalists” and “Digital” have been selected, in any order, it counts 4406 + 106 I can write an IF command to create a value for an exact match in the box (e.g. ONLY says “Generalists”), but stuck on how writing this more complete form. Any help gratefully received!

I’m making course codes (like ACT015) from numbers (like 15) and prefixes like “ACT”. The trick is - how to left pad the numbers with 0 when combining them? The part of the formula to add the right number of '0’s: REPT(“0”, 3 - LEN(Number&"")) and the whole formula: CONCATENATE(Prefix, REPT(“0”, 3 - LEN(Number&"")), Number) Hope this helps someone! I didn’t see any other posts on the first page of results when I googled “airtable formula leftpad”, so had to get creative.

I believe the “SWITCH” function was probably not the proper tool for what I am trying to do. Here is what I did: IF({Workflow}=“Manufacturing”, DATEADD( DATEADD({Start Date}, {Estimated Production Days}, ‘days’), SWITCH( WEEKDAY( DATEADD({Start Date}, {Estimated Production Days}, ‘days’) ), 0, -1, 1, -2, 0 ), ‘days’ ))

I wrote a very simple formula for my first field column to include fields from other columns in my table. I have only two fields in my formula with text strings to include spaces between them. My issue is that one of the field ends up between two brackets like this " ". As you can see, the field Prix Fab Gastier is = 12" BW STD in the table, but in my formula it returns “12"” BW STD" So why is there in total 3 additional " ? One before the 12 One after the 12" and one after the STD

Hello, I’m having troubles figuring away the proper way to make a formula that will: "if the status = canceled or replaced, the value of the field should be = quantity-quantity (making it a 0) " where the status is a single select field, how can I make this work? Many thanks in advance

I am running an event/webinar series, and I’m wanting to identify the total number of people attending the event for the first time (as to get an indication of growth). I have two linked bases The event series the attendees Key information I believe required (that I have): Date of each event Event name Attendee name Attendees assigned to events Total attendees at the event My thinking was to create some sort of formula as you see in this image below: (However, you cannot dynamically link a date from the base into the date field in this image. ~ Essentially my logic is: Count attendees if this is their first event. – Get dates of the events

I work for a small college, and we want to give Airtable a try for tracking our applicants and student financials. I’m running into problems when students are registered into more than one program, notably our “double diploma” program, which is actually two programs bundled together. (I also want to see if I can use it to make our scheduling simpler, so the Program Group is an important level for that) The problem comes when I’m trying to calculate the amount of tuition that a student owes at any specific point in time. If I lookup Tuition costs for a student, I get two results concatenated, and the formulas do not like this. Even if I can get the formula to calculate so that I get concatenated answers, I can work with that (ex. Tuition 1, tuition 2—formula magic—>due “now” (actually start+/-weeks) 1, due “now” 2 ) If I set up another table, from what I understand, I have to go through and manually enter the links. I’ve already made links between three differ

I’m looking to create a datestamp field based on if a Single Select field is either COMPLETE or CANCELED, otherwise it should remain empty. I’ve searched through this site and tried a variety of options but have had no success. IF(OR(Status=“COMPLETE”,“CANCELED”,LAST_MODIFIED_TIME())) **This returns all cells as blank Thanks in advance!

Hi, I am getting an Error back on the very basic SUBSTITUTE, which I have used numerous times in other tables of my base. SUBSTITUTE({Record Name}," “,”-") {Record Name} is a FORMULA FIELD that pulls the text from a LOOKUP FIELD (in the same table) that pulls the text from a FORMULA FIELD in another table. Any ideas? Thanks in advance. Tobias