Formulas

Hi all, Semi-new user to the tool here, and still learning the formulas. I have the following issue: I am using the switch function to categorize certain single select options into different categories (in this case, which statuses in the HR systems relate to an overall designation of accepted, dispositioned, interviewing, etc)My full formula is below. The issue is the very last line item: 'Doesn't Meet Min Qual', 'Dispositioned'This line has the apostrophe in the word "Doesn't", and that seems to be invalidating the formula. Everything worked great until I added that line, and then it breaks. I unfortunately can't change the wording of the option, since it comes directly from our system. Is there any way to get the formula to work, and ignore that specific apostrophe? SWITCH({Current HR Status}, 'Prepare Offer - Pro', 'Accept', 'Send Offer for Approval - Pro', 'Accept','Hold&n

I have three training sessions, and participants need to have completed all three in order to proceed. I'd like to have a formula that shows 'completed' when all three = completed and 'incomplete' when none/not all are complete.Thank you!

I want to link my URL column with my title column so that the Title is linked to the URL. Instead of having the URL, I have the Title but it is link to the URL, creating an hypertext link. 

I'm looking to auto-populate some date fields. Right now, you add the date and the day of the week auto populates but I'd like to be able to enter 2/14/23 in the "EST Release Date" field and have the rest of the dates in the column auto-populate. Is this possible.I'd also be copying and pasting multiple sections into one grid so each chunk would have to be independent of another chunk...if that makes any sense. I know how to figure this out in excel but I'm hoping this is possible in airtable, too! Thanks!   

I'm looking for a workaround to populate the distance between two zip codes. I need to identify which office is closer for each employee. I have input all the zipcode coordinates with a linking structure in the base. https://airtable.com/shrwDRIKVyoBphDBk/tblNsf1iP5agssIZS/viw5teuUhoedEPQA6?blocks=hide The excel formulas I would use don't work in Airtable. See the formulas in the base under the "Miles to office ONE" and "Miles to office TWO"I'm a beginner when it comes to formulas in Airtable so I'm hoping someone can help. I don't have any experience with JavaScript or coding. 

I'm puzzled by an error I am coming up against ... We pay our service providers' invoices (received by Monday) ten working days later (so, typically two weeks later). Using this formula, I get the result I want for a date of Monday 20/2/2023 being Monday 6/3/2023:  WORKDAY({Send your invoice by}, 10, '01-01-2023, 02-01-2023, 07-04-2023, 10-04-2023, 01-05-2023, 08-05-2023')  When I add the next public holiday date into the formula, I get an ERROR: WORKDAY({Send your invoice by}, 10, '01-01-2023, 02-01-2023, 07-04-2023, 10-04-2023, 01-05-2023, 08-05-2023, 29-05-2023')​ I don't understand why adding that date breaks the formula, can anyone advise please?

In jira we have an Acceptance Criteria field with LOTS of text and we sync this to airtable because airtable offers more fields necessary for the work we do.I want to be able to create a formula that finds and returns the free form text after a specific string of text in the long text field.For example from the long text example further below I want the formula to SEARCH the {Acceptance Criteria} field for  “Creative_Type: " and return the text after it (maybe use “RIGHT”?) but before a hard return and the result in this case would be SIG I’ve created rules where the options are a static list (for example “Email Type: “can only have a certain selection so my formulas account for that but when the text is free from I just want that field to populate with the free form text that shows after “Creative_Type: “  I assume it would have to be a RIGHT and calculating the length or isolating anything after the "Creative_Type: and before the hard return or do i have to create a combo f

I have a list of unique ID+count information in a rollup field. See sample data below.16389-0*4, 70895-0*2, 53429-0*2, .....Everything before the * is an ID, and the integer following the * is a count for that ID. I would like to strip everything prior to the star, leaving:4,2,2,....Then I'd like to sum those values.Is that possible?

How to create formula that filter the rows by last month?The filter function doesn't allow a date range (which is strange). e.g. If I want to filter rows with dates fall within February 2023, how do I do that? Thanks a lot!

I have column A with the breakdown option and have columns B and C, what is the formula if someone chooses A1,A2 it will link to column B and if choose A3,A4 it will link with column C?

hello, I need help, I have my airtable automate with make for my bnb reservations. my lock generates a temporary code then add it in airtable. msis if the code starts with 0 , the 0 disappears this is very problematic for customers. how to do ? the code always has 10 numbers 

Good day team,  I would like to ask let's say for example I have these email addresses and I want to use a formula to get "ABC" to "ABC Company Philippines." What would be the correct formula?For your reference, this is the formula that I am using right now. IF(RIGHT({Email},LEN({Email})-FIND(".",{Email},FIND("@",{Email})))="abc", "abc company Philippines",IF(RIGHT({Email},LEN({Email})-FIND(".",{Email},FIND("@",{Email})))="bcd","bcd",IF(RIGHT({Email},LEN({Email})-FIND(".",{Email},FIND("@",{Email})))="cde","cde", RIGHT({Email},LEN({Email})-FIND(".",{Email},FIND("@",{Email}))))))

I am trying to use the new list view to create a system for putting together quotes for events. I have a list of event enquiries in table 1, and in table 2, have created a list view, where I am adding line items to events using levels. I would like to have an auto calculated field in the event enquiry item that gives me the total cost for all the line items I've allocated to that event. I've attached a screenshot of an example. I assume the "Total event cost" field needs to be a formula - can anyone help with a formula that will do this calculation?

Hello!  I have text of the form:10 x 8 inches (image)25.4 x 20.3 cm16 1/8 x 13 5/8 inches (frame)41 x 34.6 cmin a field called Image Size.  This is how the text came in from an Excel spreadsheet.  There did not seem to be a Delimiter I could successfully use to separate the text previous to my importing it to Airtable.  I want to take the string on the line that precedes "(frame)" and that follows "(frame)" (basically the inch and cm dimensions of the frame as opposed to the image size) and extract it to a new field (the formula field), while, hopefully also deleting it and the cm dimensions following "(frame)" on the next line from the original Image Size field.  Sometimes "(frame)" shows as  "(frame size)".  Thank you very much in advance for any assistance!

Hello Everyone,Is there a formula for multiple values I set to equal "Success"? ( put several values like "1234" which would be equal to "Success") please thank you in advance,Best regards,

Hello, I would like create a table with summary results of all the base. For exemple, for a base with different tables like : 1 - Suppliers2 - Products3 - Customers 4 - SalesI would like have in a table (to use in a app built with softr) global datas like : - the number of records in the supplier's table or of customers- the average turnover - the total average ...How can i do that ? Thanks 😃 (I know it is possible with blocks "extensions" but it is not possible to have datas blocks in softr ... ) 

I'm pretty sure I'm over thinking this process so I apologize in advance if this is obvious to most.I have a field in Table 1 which when data is entered, I'd like that data to automatically create a new record in the primary column of Table 2.Table 1 is my ideas bank and when I have created a  "Working Title" (field name in Table 1) for that idea, I'd like the title to be automatically added to Primary Column in Table 2, thus creating a new record.Really hope this makes sense.Any help appreciated.

Hello, maybe this is not even possible, but if it is, what would be the proper syntax for this ?IF(VALUE({Total Rate 6})) >0, VALUE({Total Rate 6}), Blank() This field is showing the data of another column called "Total Rate 6", but i want it to be blank if the amount is 0(zero)

Hi everyone, I'm trying to get this formular to work, and although it parses there's no output.   ARRAYJOIN(ARRAYCOMPACT(Field1,Field2,Field3,Field4), ", ")  The fields are a mix of and link-to-another-record fields lookups (of link-to-another-record fields) where the outputs all are fine. The reason why I'm using an ARRAYCOMPACT as well is because half of the fields are usually empty.Thank you very much ❤️

Hi guys, I need help building formula automation and seem to have trouble with the Find function.So we have an outreach database for our customers and for each outreach, we track a response as "yes" or "no," Ideally after two constant rejections we would mark the customer inactive. If later on they respond or reach out then we will mark them active again. With this in context, I'm trying to build an automatic status update function in Airtable. If one customer's status is "No, No" or "Yes, No, No," the customer's status shall automatically change to "inactive." If a new yes is recorded, like "No, No, Yes" then it shall change to "active,"I tried a few different ways but none seem to work perfectly. The closest I could think about right now is to use the "Find()" function which in my understanding returns the last position of the string being looked up. However, when I tried it, this is what I got:only one string "yes" or "no" is in the cell, return 1";Two "no" in the cell, Find("n

I know this is super simple, but I am new to formulas. Here's my problem:I have volunteers signing up for a rotation and I'm trying to make a view that will capture their hotel needs, which are generally: Check In one day BEFORE their First Day BOG and Check Out one day AFTER their Last Day BOG (BOG = Boots on the Ground).This is the formula I am using to get their Check In date: DATEADD({First Day BOG},-1,'days')It is not calculating correctly.This is the formula I am using to get their Check Out date: DATEADD({Last Day BOG},1,'days')Also not calculating correctly. Help & TIA

Hi everyone,An application is sending me a date in ISO format (2022-02-13) and I want to convert it in the european date format without dropping the day and month leading zeros ( = 13/02/2022). I managed to convert from ISO to European but the leading zeros are dropped.If I use the DATETIME_FORMAT with the 'L' specifier, the leading zeros are back but the format reverse to the american format ( = MM/DD/YYYY).Is there a way to keep both the european format and the leading month and day zeros ?Thanks in advance for your help, Gilles

The bottom one should be a count of 2. Team A and Team B are two fields. I tried COUNT, COUNTA, COUNTALLHere's the formulaI can't even add a Count to that specific fieldThanks

Need the 'Test Formula' field to format the 'Trip Total:' as currency.  Any ideas anyone?

Hello, I'm stuck trying to figure out how many workdays exist in a given month. It is easy enough to get the number of workdays between a start and end date using the WORKDAY_DIFF() function, and it also seems possible to collect the total number of days per month. However, I have not come across a solution to determine the number of workdays per month. Any help would be appreciated!