Formulas

So I have a multi-select field that has about 50 or so options. I want to take those options and surround them with quotation marks, separated by a comma. I tried using the SUBSTITUTE, but I couldn’t get it to work the way I wanted without duplicating the field a few times. I did something similar to below, but now I have three extra columns in my table…heh. SUBSTITUTE({Calculation},", ",",\\"") I am wanting to take this: and turn it into this: [“Pet Doll”,“Chubby”,“Child”,“Wings”,“Horns”,“Round Face”,“Pointed Ears”] Complete with brackets on each end. How would I go about doing this? I don’t know if scripting will work because I’m trying to import my airtable into another service and in order for those choices to be recognized as tags, they have to be formatted as you see above. So the field has to have that data in it. I have hundreds and hundreds of rows, so doing it by hand is not an option.

I have the following functioning formula set up to generate a MM/DD/YYYY response based on the input from a number in the {Day of Month Due} column: IF( {Day of Month Due}, DATETIME_PARSE( DATETIME_FORMAT( TODAY(), ‘M’ ) & “/” & {Day of Month Due} & “/” & DATETIME_FORMAT( TODAY(), 'YYYY' ), ‘L’ ) ) Result: My goal is to make the formula generate the following month’s due date once the due date has passed. For example: If the {Day of Month Due} is 25, then the due date is 5/25/2022. Then, once the calendar date reaches 5/26/2022, I’d like for the formula to show 6/25/2022. If anyone can give me some guidance on how to create this, that’d be great! Thanks in advance.

I have a single select field, and a rollup field which contains a formula that references the single select field. Now I remove 1 option from the single select field. The formula of the rollup field is now broken because it references this deleted select option. Airtable did not stop me from removing that select option (although it warned me about the dangers of me deleting the option). Airtable does show that the rollup formula is now broken by adding a red indicator to the field. The problem I have is that the two fields where in different tables, so it took me quite some time before I noticed the problem. So here comes the question: is there a way to get a list of broken fields in a base? Either through the web UI or through the Airtable API? That way, I could query this once in a while to verify nothing is broken.

Hi Community, I’m having trouble creating a nested if statement, where the condition is to identify two options from the field “Financial End Year Date” (single select field), and return two different values (for ‘December 31’ data - return ‘8/15/2022’, and for ‘March 31’ data - return ‘11/15/2022’, and if neither, return ‘4/30/2022’. The nested if formula I created and tried is as below: IF({Financial End Year Date}=“December 31”,“8/15”&"/"&YEAR(NOW()),{Financial End Year Date}=“March 31”,“11/15”&"/"&YEAR(NOW()),“4/30”&"/"&YEAR(NOW())) When I run this formula, the field shows either “8/15/2022” or “true”, where the “true” should instead be “11/15/2022”. For the ‘neither’ options, it returns a blank cell. What’s more confusing is, after I save the formula and click the configuration again, the formula is automatically corrected to below: IF({Financial End Year Date}=“December 31”,“8/15”&"/"&YEAR(NOW()),{Financial End Year Date}=“March 31”) Could anyo

Hey there! I’m massively hitting a wall with something that might take a script? I’m creating a Production Planner/Tracker for a YT Channel and want to have a formula that spits out the current date & time of when the {Production Status} is changed to “Post-Production” so that I have a {Post-Prod. Start Date}. That worked fine with: IF({Project Status} = "Post-Production",LAST_MODIFIED_TIME(), BLANK()) The problem comes when I have to switch the {Production Status} to “On Hold” or “Complete”. It obviously removes the {Post Prod. Start Date} Value. And in the case of “On Hold” it will then go on to replace it with a new date and time if/when the project gets set back to “Post-Production”. So my question I guess is, how do I get the formula to only run the first time around - or what other work-around is there? I hope this makes sense :open_mouth: Thanks in advance! Sharon

hello, i am trying to do some basic count using formula (i think its basic). there are 5 columns (named “name”,“rozmer1_cm”,“rozmer2_cm”,rozmer3_cm" and “vaha_kg”) what i want to do is formula, which will get those results: if all “rozmerX” < 50 and vaha_kg * 1000 <250, than its “M1” have found it to make it this way: which is working fine. BUT than i need to do if all “rozmerX” < 50 and vaha_kg * 1000 >250 and <=500, than its “M2” there are many other conditions, but i cannot go over the condition “vaha_kg*1000 >250 and <=500” have read the basics of formula, but i cannot apply it to my case (i know, its stupid). Thanks for kicking in right direction :slightly_smiling_face:

Hello, I have a big favor to ask, which is basically, can someone help fix this formula? I am creating a Yes/No flag to display content using the TODAY() function, but I need it to determine TODAY() by Eastern Standard Time. This is what I have and its not working… IF(SET_TIMEZONE(TODAY(), ‘America/New_York’) = {Prayer Point Date}, “Yes”, “No”) The conditional piece works great, it’s the TIME_ZONE that’s a bust. All help is appreciated.

Hello! I have a Blogs with urls like: Some text (https://www.example.com Example Link) some text And i need extract all links from each blog Regex blowed my mind, so i here to ask help. Help me please

Greetings from a first time poster! First off, thank you to anyone who might be able to help. I’d provide examples, but I think the question itself is pretty straightforward. Thank you to anyone who can provide direction on this. In one table I have a few thousand records that represent segments of a pipeline. I have the latitude and longitude for both ends of the pipe (upstream “US” and downstream “DS”), as well as the average of both (6 columns). In another table I have a form setup that would represent crossings of said pipe, and it includes a latitude and longitude column as well. I want to setup a rollup or look-up column that provides the record (or records) from the first table that fall within a tolerance zone from the latitude/longitude entered from the form (0.5 x average difference between US & DS Lat/Long, respectively). This is what the source table (1st table) looks like This is what the Form table would look like, including what I’d like to see the outcome look

Hey everyone! I had help with a formula not too long ago, and I’ve simply tweaked it to apply to other tables… but as of now, I’m not getting the results I need. Here’s the formula: IF( {Date}, IF( Date > TODAY(), IF( DATETIME_DIFF(Date, TODAY(), "days") <= 30, "30 days", "1 month", IF( DATETIME_DIFF({Date}, TODAY(), "days") <= 180, "180 days", "6 months" ) ), "Send 6 month status check email" ), "Send 30 day status check email" ) Every status is showing as “Send 6 month status check email” even if it’s within 30-days. Help? And can anyone offer resources for the clueless when it comes to building formulas (outside of the Airtable cheat sheet)? I would definitely appreciate the help.

I have multiple Files in my Airtable. I want a Column for the Extension of Files. Extensions are .Docx, .Xlxs, .PDF, .AI and Multiple Other. I did use the Trim Formula but its not working too well for me. Please help.

Hi, thanks for any help anyone can give, I feel like my head is going to explode. Essentially what I’m attempting to do is have an autonumber generate based on the project it is linked to. I am tracking forms that come in from different projects within the same table. I need it to work so that If a record is associated with ‘Project 1’ it will generate the next number associated with ‘Project 1’ and if I then create a record associated with Project 2 it won’t carry on from the number generated from the ‘Project 1’ recorded created. I feel like im explaining this so badly, so if anyone needs further clarification I’m happy to give it. I’m an intermediate user concerning Airtable but this is just baffling me completely!

Dear Community! I’m fairly new to airtable, so please bear with me :slightly_smiling_face: I’m trying to pull some aggregated data into a new table. Receipts are tracked into a table “Expenses” with a field for the amount, a field for the name of the person who made the expense and a field for the payment method (cash, bank transfer, paypal, etc.) - amongst other fields not related to my problem. Now I would like to get the sums of amounts aggregated by person and payment method. With SQL I’d run “SELECT person, method, SUM(amount) FROM expenses GROUP BY person, method” Ideally the table with the aggregated data is updated dynamically when rows are appended to the expenses table. I’m aware of the possibility to create a view that does exactly what the SQL query does. However, I need to append the queried data with additional fields. Any help on this is greatly appreciated. Best regards Henry

Here is my example address: https://www.google. com/maps/place/42+Nayland+Street,+Sumner,+Christchurch+8081,+New+Zealand/@-43.570045,172.7595125,17z/data=!3m1!4b1!4m5!3m4!1s0x6d32282318638d65:0xdbd8ceb7acf3afba!8m2!3d-43.570045!4d172.7595125 What I need to extract from over 350 records is this: @-43.570045,172.7595125, I was thinking i could take out everything before and after this showing up in the address but all longitudes and latitudes are different so I thought I could say maybe (?) I want to keep everything between the @ and the second comma. thanks in advance!!!

Hi team, I’m attempting to prefill my email field question, and for some reason, the below formula returns #error when I include it, but when I remove the email field formula, all other prefilled data works. Is there a mistake in my formula? Is this due to the fact that the prefilled data is a lookup from another column tab rather than an actual field entry? My Formula: CONCATENATE({Form Response (GC’s Form Requests)},’?prefill_Customer=’,ENCODE_URL_COMPONENT({Customer}),’&prefill_ProjectID=’, ENCODE_URL_COMPONENT({ProjectID}),’&prefill_Bath+Type=’, ENCODE_URL_COMPONENT({Bath Type}),’&prefill_Your+Firm+Name=’, ENCODE_URL_COMPONENT({Assigned GC}),’&prefill_Primary+Email+Address=’, ENCODE_URL_COMPONENT({Email}),’&prefill_ParentRecordID=’,{Main Record ID}) Thank you in advance!

Hi Folks, I hope you guys are having a great time. I am working on a localization budget for a client , and they process payment per Episode not per title. a title could start in 2021 and end in 2022, so we should process this in 2 separate times. 1- from release date ( Available field ) let’s say 1/10/2021 till end year 31/12/2021 ( End of current Year 2- From Start of the new year till the Last episode release date ( available field ) let’s say 18/2/2022 ( End of New Year for last eps date ) We will be able to calculate No of days between the start and end of current year and calculate no of days between start of the new year till the last episode release date . I have added 2 new fields 1- Days before Year start ( should include No of days between the start and end of current year) 2- Days after Year Start ( Should include days between start of the new year till the last episode release date) I will then convert those days against the total days between release and last eps date a

I need to create an IF statement that works with the following logic: If the value of column X is less than the value of column Y, it needs to return the value of column X. If the value of column X is greater than the value of column Y, the IF statement needs to subtract the value of column Y by the value of column X. Can someone please help?

Hi there helpful community, I am currently facing the following issue: I have a list of requests from customers that I map to retailers in their area. I successfully calculate the distance between each customer and each retailer using the Google Maps Distance API. Now, I would like to automatically calculate the order of those distances, e.g. 1 = closest; 2 = 2nd closest, etc. I simulated this in the screenshot below. Instead of me assigning those numbers (1-3 in this example), I would like to calculate this automatically for all requests. Do you have any solutions in mind? In Excel this could be achieved by the rank formula I believe…

Greetings, I’ve created a simple base to calculate my stock (vapes) that i’ve ordered. However i am running into an issue. I have created three tabs (Current Stock, Sales (orders) and Orders from my Supplier). My issue is when i register a sale because when i put the items in product column (multiple records) the price at the “price column” shows only as 1$. If i only put one product then that is fine because the product is linked and the price shows okay but once i put multiple products on a single sale the price always remains the same. Hopefully i was clear enough and i can provide more context, info or pics if needed. Thank you,

I was wondering if anyone could help with my issue. I am trying to get a value in field W MIN from an array of numbers (field: W Unique). But I only need to return the smallest number larger than a given number (field: Thermal MIN). I am using this formula in the field I want to return that number: MIN(IF({W (from LED Performance Data) Rollup (from LED Type grouped by Output)} > {Thermal MIN (W)},{W (from LED Performance Data) Rollup (from LED Type grouped by Output)})) (the formula is entered in the field column (drop-down arrow and select customize field type) The same I need to achieve for the W MAX field comparing the values to the number in the Thermal MAX field. However, it comes back with an Error! and I am not sure why? Here is a screenshot of the columns and values. Any help would be highly appreciated.

I have included screenshots for context. I just want to get the total from the first screenshot to the spot in the second screenshot. Any and all help will be appreciated!

We have physical punch cards (time cards) that we are trying to review. I set up a base with a row for each employee’s card. Each card is for a pay period and the employee has multiple days worked per pay period. The base (redacting employee names and pictures of the punch cards) looks like the following: I want to find which employees worked together. I’d like to do this by choosing a particular employee and seeing who that person worked with and when they worked together. I would also like to see who worked together by choosing a particular day. I think this would have been easier had I built the base with each shift as a row, rather than each pay period. Nevertheless, is it possible to extract this information with a formula, lookup field, or rollup field? I feel like this might have been a job for an if-then formula along with index-match in excel, but I am new to Airtable and am not sure if a formula is where I should start. I am very grateful for your suggestions.

I want to get the unique value of the {Photos IDs} column, but ARRAYUNIQUE() doesn’t work, presumably because it has a newline symbol in it. What should I do

I am completely out of my depth here, so I apologize for dumbing what I’m doing down. Also, talk to me like I’m a child when explaining what’s going on :grinning_face_with_big_eyes: I have built a simple website with Webflow and use Wized as the backend. Wized uses Airtable as the database. One of the functions of my website is that it gives the user a unique email address where they can send messages. I want this unique address to be created by Airtable. I’m assuming i use the first and last name tables with this formula {first} & {last} to get the first and last name into a new cell. Now i have two questions: how would i add to this first and last name the “@mydomain.com” part of the email? i know this is something super simple, but just can’t seem to find the right one more importantly, how would i get airtable to check the same column for duplicate email addresses before creating this one? and if it encounters a duplicate, get it to ad a number (1, 2, 3…) to the end of th

Hi, IS it possible to sort this list of times, earliest to latest? I can only sort A-Z and this is what it returns: