Formulas

Hi guys, Anybody know a way to create proper request codes / references for each row. For example, my current system is to concantenate a Letter (‘S’ for example) with the product name, then place the year of the request, then add the autonumber ID for reference. So it comes out looking like: S-MOT-22-1. However, I would like the last number in this field to reset and start counting again at every new year, and maybe even with every product type. Ex: S-MOT-22-1 for the first MOT request of Jan 1 2022 & S-MOT-23-1 for the first MOT request of Jan 1 2023

My question has 2 parts to it. I need to check for a missing date in 8 columns (4 sets of Start and End Date) that has a start date or end date, but not the other. I use these columns to calculate vacation days used. So if someone inputs a start date but forgets to put a end date in, I want to show “Start or End Date Missing” instead of the calculation. This works so far for one start/end column pair. IF(LEN({Vacation Start 1})>0, IF(LEN({Vacation End 1})=0),“Missing a Start or End Date”, BLANK()) But I am looking to group all 8 columns to one answer like this IF(LEN({Vacation Start 1})>0, IF(LEN({Vacation End 1})=0,“Missing a Start or End Date”, BLANK()), IF(LEN({Vacation Start 2})>0, IF(LEN({Vacation End 2})=0,“Missing a Start or End Date”, BLANK()), IF(LEN({Vacation Start 3})>0, IF(LEN({Vacation End 3})=0,“Missing a Start or End Date”, BLANK()), IF(LEN({Vacation Start 4})>0, IF(LEN({Vacation End 4})=0,“Missing a Start or End Date”, BLANK()),BLANK())))) But this just l

Hey all! I’ve asked the community in my recent post whether or not it is possible to “score” records based on the value in a multi-select field, and I also needed my formula to calculate the total for all values when the multi-select has 2 or more values. Here’s what @Justin_Barrett came up with and helped me a lot: IF(FIND("Global", Locale), 1) + IF(FIND("US", Locale), 0.9) + IF(FIND("UK", Locale), 0.9) + IF(FIND("DE", Locale), 0.7) + IF(FIND("CA", Locale), 0.8) + IF(FIND("FR", Locale), 0.7) + IF(FIND("ES", Locale), 0.6) + IF(FIND("MX", Locale), 0.6) + IF(FIND("BR", Locale), 0.6) + IF(FIND("AU", Locale), 0.6) + IF(FIND("IT", Locale), 0.6) The way that I expect my records to be scored has slightly changed as so I need to refine the formula - I don’t need it to put the values together, instead, I want it to return the biggest value from the list alone. Here’s an example: The score per locale is as before: US - 0.9 UK - 0.9 DE - 0.7 Global - 1 CA - 0.8 FR - 0.7 ES - 0.6 MX - 0.6 BR - 0.

Hey people! I’m trying to “score” some of the records in my table, one of the columns represents a localization information about each record, e.g.: US UK DE Global CA FR ES MX BR AU IT Each localization adds up points to the final score: US - 0.9 UK - 0.9 DE - 0.7 Global - 1 CA - 0.8 FR - 0.7 ES - 0.6 MX - 0.6 BR - 0.6 AU - 0.6 IT - 0.6 The column which represents locales is actually a multi-select column, as my “records” can be present in multiple locations. My formula can already read the value in the Locale cell and add up the corresponding value to the final score, but how can I program it to see, that there are several values, and calculate them all together? Current formula: IF(Locale=“US”,0.9,(IF(Locale=“UK”,0.9,(IF(Locale=“DE”,0.7,…etc. Thank you!

My Current format is like this I want the format to be Shrink-General | 5 x RM-SHRINK 1 x RM-SLIP 1 x RM-BAREFOOT What is the formula for that?

I hope use this function in airtable… please help me out

I would like the formula to be able to identify the client by field {Rodzaj usługi}. If the client would be (in the {Rodzaj usługi}) “Fotografia ślubna” then I would like {Status} to show “ :heavy_check_mark: ” only if the {Odbitki}, {Album}, {Galeria internetowa}, {Selekcja zdjęć}, {Zwiastun}, {Obróbka zdjęć} fields will be filled with the word “Oddane”. Otherwise, the {Status} field should show “ :x: ”. When the client would be (in the {Rodzaj usługi}) “Film ślubny” then I would like {Status} to show “ :heavy_check_mark: ” only if the {Film wrzucony na dysk}, {Teledysk ślubny}, fields will be filled with the word “Oddane”. Otherwise, the {Status} field should show “ :x: ”.

For whatever reason, I cannot add text to a formula, even using the same formula I have running in other bases. Basically, I want the formula to return to values with a hyphen or colon between the two. I have two variables, Color (single-line text) and Brand (single select), and I want it to return like “Color - Brand” or Color: Brand.” I have tried the following formulas: Color & “ - “ & Brand Color & ‘ - ‘ & Brand Concatenate(Color, " - " , Brand) Concatenate(Color, ’ - ’ , Brand" Each of these returns an error for an invalid formula. And, as I said, I have another base with an effectively identical formula and it works with no problems. Further, I have tried moving the hyphen elsewhere and I’ve tried using different characters, none of which works. For example, if I just try to add a random word to the end of the formula, like the one below, I get the same error: Color & Brand & “randomword” Concatenate(Color, Brand, “randomword”) These, too, return the s

Hey Everyone: I have a table that links items to form submissions. I already have a link to see each item and all the form submissions that include that item. I also have a lookup that looks up the dates of the form submissions in the link to forms. I want to take that lookup and run a formula to count all the form submissions from today’s date. Any ideas?

I was able to do a simple subtracting formula to show amount owed and amount paid and balance remaining. Is there a way to expand on that formula to include when interest is charged so that it can be added to the balance? I want to have a more accurate picture of my financial spending. Any help or advice would be appreciated. Thank you.

Hello! I have a Jotform survey automatically going into Airtable. Some of the questions have “Check all that apply” with an “Other” response that prompts a free form answer. These free form responses are being added as multi-select option in Airtable (rightfully so). I’m trying to do an Airtable Interface on the survey responses but, obviously, those free form responses are being considered. In a column, I want the options and replacing the free form response with “Other”. Then in a separate column track the free form response (just so that data isn’t lost). Any ideas? This is essentially what I’m trying to do: The “Name” and “Q1 Answers” field is the only thing imported with survey responses.

Hello, I am new to this software but have written a pretty functional Airtable to keep track of the UK 50p coins I am collecting. I’m able to add costs and dates and photos and descriptions and have even been able to add columns up at the bottom using the Filled option. I have three columns (16, 33, 7) which I would now like to add together to give a total of coins. I would then like to multiply that number by .50 to give me the bare minimum value of my collection. I think it ought to be simple but I am held back by lack of knowledge of basic formulas and would really appreciate any help the community could give me.

Background: I have a table that includes a list of activities each customer has done. So there unique value is a random “Event ID”, another column is “Customer ID” and a third column is called “Activity Type.” I need to find a way to count how many times a Customer has done a certain Activity Type and then be able to use that formula to create a notification when a customer has their FIRST activity (first appears in the table.) The table is populated via API, so it’s not as simple as just knowing I did it. I found a Pivot table that can just give me the counts of Activity Type by Customer, but you can’t act on any of the information in that table. So essentially I need a way to find any time a new record is created that contains a unique Customer ID that does not exist yet in the table. TL,DR: I need a way to answer the question “Tell me when a new client has their first activity”.

Hello, I am horrible at formulas and it’s really becoming a frustration as I continue to learn Airtable. I am wanting to create a status in the formula field to then automate an email trigger. The formula needs to update to either 60-days or 30-days based on the contract end date. I already have fields for the start date and end date so the formula can work with either. Seems simple enough but I am a struggle bus. What formula do I need to enter? If we are 60-days out from the contract ending, update the status to 60-days, and if we a 30-days from the contract ending, update the status to 30-days. Start with IF(AND(, correct? IF(AND({Contract End Date} …blank stare…, “60-days”), repeat blank stare, “30-days”) Help :weary:

Hello Community! I need to calculate the score of a record based on that record’s numeric value. My records’ numeric values can be anywhere between 1 and 100, and I need to set a score between 1 and 10 to each value based on how high that value is, like so: 1-3 => 1 3-5 => 2 5-10 => 3 10-20 =>4… you get the idea My problem is with setting up a formula - whet I try to set it like this: IF(Value>1,1,(IF(Value>3,2,… etc. the result only reads the first parameter (value>1 => returns 1) and it doesn’t compute when the value of the record is more than 3, it still returns me 1. How can that be fixed?

Hey there…look for assistance with formula that uses the TODAY() function with a set time. I.e. I want the field to have the current date but with a set time so that it would show for example the current date that updates each day but always has 12:00 PM. Thanks in advance for help

Hi there! Looking for assistance on a project management table. Each project will have a Project Type, Review Date and Delivery Date. There are three potential Project Types (Lead/Partner/Advise), the Delivery Date is a manual entry, and the Review Date needs to change based on the Project Type. Problem: 1) I keep getting an invalid formula notification, 2) I’d really like the review date to only land on weekdays/workdays but the formula needs to account for weeks out, not days out. Additionally, I’ll be duplicating this formula for various types of Review Dates, and sometimes based on Project Type, I’ll need the answer returned to simply be “Blank”. Here is the nested IF statement I am attempting. IF({PROJECT APPROACH} = “LEAD”, DATEADD({DELIVERY DATE},-15,‘WEEK’), IF({PROJECT APPROACH} = “PARTNER”, DATEADD({DELIVERY DATE},-14,‘WEEK’), IF({PROJECT APPROACH} = “ADVISE”, DATEADD({DELIVERY DATE},-13,‘WEEK’),’ ))) Would really appreciate help on getting this sorted out. Thank you!!

So I’m not sure how to ask it differently, but if I were to try, here it goes. NEW to Airtable (so TY for being patient & detailed) So I have a base with three tables in it, Customers, Vehicles & Sales. I’m trying to collect all of the sales for each customer. All of the tables are linked, and I have been able to bring each of the sales into one cell (example: $0.00, $170.00, $125.00). But now I need to total those numbers! Any help would be greatly appreciated! DG

Hi all, Trying to make an interface with a “maximum possible spend” for each project launch day. Projects launch twice a week and run from 2 days to 15 days. I need to find a way to ask the question “Is this project still running next Tuesday” and “is the project still running next Friday”. Does anyone know where to start? I’ve tried a few things, I’ve tried lots of nested if statements, but I’m truly stumped. EDIT: A better way of formatting the question is “Will this product still be available for purchase next Tuesday/Friday” as two separate columns with a true/false value.

Please could you help me to understand how to make this work? Thank you! IF(NOW(IS_AFTER({Announcement Start Date}&IS_BEFORE({Announcement End Date}, ‘ACTIVE’, ‘INACTIVE’))))

Hi there, I want to create a field that gives a different message to the user, depending on whether they have filled in a particular field ‘on time’ or not. I’ve attempted to explain it in text. If anyone could suggest the functions I need to use, I would really appreciate it. The problem for me is a. how to say ‘IF time is less than 5 minutes towards the time in the field OR IF time is more than 3 hours after time in field’ and to link that to b. ‘… AND field x has not been filled in THEN ‘show message a’’ and c. linking several options together (x3 optional messages). IF time is less than 5 minutes before end of lesson AND there is nothing in lesson reports field THEN “Please enter your lesson report, concrete targets and homework now” OR (IF time more than 3 hours after the lesson end time AND there is nothing in the lesson report field THEN “Your lesson report is late. Please enter your lesson report, concrete targets and homework now”) OR ((IF the report field is filled in BEFO

hey community, I’m working on this formula to return a simple “Y” or “N” based on whether or not a date is past. The items in the grid could either be on a single date, or could have both a start date and an end date. I’ve tried a couple different ways of writing this formula but getting a lot of errors. What I’d like to do is: “if the end date field is empty, determine whether or not the event is in the past based on the start date field; otherwise [if the end date field has a date], determine whether or not the event is in the past based on the end date field.” Here’s what I’ve got: IF({End Date} = "", IF(IS_AFTER(TODAY(), {Start Date}), "Y", "N")), IF(IS_AFTER(TODAY(), {End Date}, "Y", "N"))) Could somebody help me figure out what I’m doing wrong? Thank you!

Hi All! I have a nested IF statement that is set up to find if the value from Column A, B, C, D, or E matches Column F. If one matches, it produces the name of the column. What I’m looking for is a way to produce more than one name if more than one column matches. Currently it looks like this: IF( OR( ColumnA, ColumnB, ColumnC, ColumnD, ColumnE ), IF( ColumnA=ColumnF,“ColumnA”, IF( ColumnB=ColumnF,“ColumnB”, IF( ColumnC=ColumnF,“ColumnC”, IF( ColumnD=ColumnF,“ColumnD”, IF( ColumnE=ColumnF,“ColumnE”, ) ) ) ) ) ) This only gives me one result. If more than one column matches, I’d like it to somehow give me both or all results Thanks!

I have a Fx field and for some reason, it’s showing " at the beginning and end of the words. Is there a way to remove it? Formula: {Contact} & “—” & {Type} How it’s showing: “John Smith, Acme Inc”—Email How I’d like it to show: John Smith, Acme Inc — Email

I am building a simple recruiting database in AirTable. Here are the tables that I have Candidates - (names, all information, name of recruiter linked to Recruiter table) Recruiters - Name, Email Position - Position, company, salary, etc. Submission - This is where a candidate is linked to a position. Link to Candidate Name, Link to Position. I also have the Recruiter Name here though Lookup. How can I display recruiter’s email so it can be included in email automation?