Formulas

Hi everyone! I have a table with several courses, and event dates for each course. I’m trying to assign the week of the course each event falls in. So, if Course A starts 11/1/20200 and ends 12/31/2020, all events between 11/1 and 11/7 would be assigned Week 1; all events between 11/8 and 11/14 would be assigned Week 2 (and so on) until 12/31/2020. Is there something in Airtable that can do that? Currently I’m writing a formula (If course = X and date is after 11/1/2020 and before 11/8/2020, then Week 1; if course = X and date is after 11/8/2020 and before 11/15/2020, then Week 2…etc, etc) Any help on how to make this simpler would be appreciated! Thanks! Jeffrey

Hello! I’m new on airtable and would love some help creating an IF formula. Context: I’m trying to create a unique vendor code for SKUs. Ive added an autogenerate number field called “Vendor Number” and added a “vendor code” field where I would like to create the IF formula. I want to to add “00” at the beginning of the vendor number if the vendor number is less than 10, add “0” if the vendor number is less than 100 and show only the “vendor number” if it is greater than 100. Example: Vendor Number: 7 Vendor Code: 007 Vendor Number: 19 Vendor Code: 019 Vendor Number: 122 Vendor Code: 122 Any help would be greatly appreciated! Thanks

Hello, I am trying to identify clashes when scheduling shows in Airtable. I am dealing with performance groups some of which share the same members I am working with 4 tables one containing all of the ‘ensembles’ a second with all the individual members of each ensemble (Contacts) which are linked to all their ensembles. Thirdly a schedule which links each ensemble to a venue, start time and finish time. I would like to find a formula which will flag a show as a clash if contacts are linked to multiple ensembles with the same start time? Any help please, I am banging my head at the table…

Hi! I have a followup question to Format Instagram URL filed and I apologize for the beginner question, but I’ve been fighting with this! I am inputting Instagram links like this one: https://www.instagram.com/mhfirstoak/ Once I’ve removed the https://www.instagram.com/, I’d like to add @ and then link the whole thing back to the original URL https://www.instagram.com/mhfirstoak/ so it looks like @mhfirstoak Any help appreciated!

I have a question about how to build rollup formula. My database is tracks members for my organization and which programming events they attend in each program year. I have a member table linked to an events table. I want to build a rollup formula that will count how many unique members attend each event. That is to say, I want to know how many people who have attended each event are people who have not yet attended any other events in the program year. I have built a column in the members sheet that pulls out the earliest event date that members have attended. In the events sheet for each event (each entry row), I want the rollup to count how many records in the continuum file have the earliest date that matches the event date in that row. How can I do this?

I feel like I’m close with this one, but it’s not quite working. I want to show quarters of the year, so if a column equals “January 2021”,“February 2021”, “March 2021”, I want it to show “January - April 2021”, and so on for each quarter. I’ve attempted it with IF(OR), but it’s not working. Appreciate any tips here. IF(OR({Result Month/Year}="January 2021","February 2021", "March 2021"),"January-April 2021",IF(OR({Result Month/Year}="April 2021","May 2021", "June 2021"),"April-June 2021"))

Hi guys, I’m trying to create a unique alphanumeric code(length 4) out of Record ID and Autonumber. But I am getting an error while doing so. Is there any way to find it? Thanks, Chintan

Hello, I’m exporting data from Airtable into PDFFiller and prices are coming through without decimal places if they are 0. eg $165.00 is coming through as 165. I’d like them to have the decimal places on the invoices. PDFFiller doesn’t seem to have any way to force the formatting to be currency with 2 decimal places. I thought this was an easy fix using CONCATENATE() (or just &) and add a $ at the start and a .00 at the end. Then I came across prices that already had decimal places so to work out if it was an integer I used ROUNDUP()=ROUNDDOWN() and if that came out true then it was an integer. I was using rounding rather than FIND(’.’) as it wasn’t finding it when formatted as a currency. Then there are the prices that have one decimal place (eg $165.50) which would still come through as $165.5. I then used a whole combination of CONCATENATEs to get it just as plain text LEN - FIND to see how far the decimal was from the end of the price IFs to add “.00” or “0”, or nothing if alre

Someone else asked about adding carriage returns to formulas (it’s done like this “\n”). I’m trying to find a similar command for tab or indentation characters. That way I can generate text formatted like this: Product: Orange Quantity of Product: 5 Instead of this: Product: Orange Quantity of Product: 5 @W_Vann_Hall I bet you’ll know

Hello everyone! I’ve got several cols (days of the week) that have a time that I wasted in doing a task in minutes (ex: 120). All that I’m trying to do is a SUM of this minutes but in a h:mm format. Example: The task is “Book Design” and the minutes I’ve spent in doing that task are (120, 120, 60, 60, 30). The SUM of that minutes is 390… that’s 6:30 hours. All I want to have is this number 6 and I don’t know how to get it. Thanks in advance!

Hey there! Let me give you some context: I am creating a packing note for a shoe manufacturer. Here’s an example: Say we have four product batches, three of which are identical, one of which is different… I’m using the ARRAYUNIQUE rollup formula to condense the three identical product batches into one, which is fine, but I also want to add up the total pairs by size of that unique batch, rather than, say, add up all the size 8s in the total shipment. Here’s a screenshot… You can see i’ve managed to use ARRAYUNIQUE to only show the product reference and description once, but if I do the same with the size 8 column, for example, it’ll add them all up, won’t it i.e. 1 + 1 + 1 + 2 = 5, whereas I need it to be 3 + 2 I’m guessing I need a way of making the size 8 of one style unique to the size 8 of the other style??! Thanks for your help in advance!

Hi! :wave: I am having some difficulty with creating a formula in my database. I have one table - ‘Feedback’ - where each row is a bit of customer feedback, and each bit of feedback has a column for ‘Insight’ (i.e. what type of problem it is) and another column for ‘CSAT score’ (ranging from 1-5). I have another table - ‘Insights’ - which lists all the insights from the feedback table in the first column (these are linked). What I want to do is calculate the average CSAT score for each insight. I have managed to create a new column in the insights table entitled ‘CSAT Score (from Feedback)’ which looks up the CSAT scores from the other table and presents them for each insight, separated by a comma. What I then want to do is create another column which averages these scores. However, when I used the formula average({CSAT Score (from Feedback)}, I get the error message shown below. Do you know how I can fix this?? The CSAT scores in the Feedback table are formatted as numbers, so I th

Hi! I’m new on using airtable. I’m trying to count specific text values in a row (1 Used) and (1 Credit). In excel, I would just use countif, but I can’t figure out how to do it on airtable, can anyone help me? Thank you! Pammy

For anyone that has been trying to get a formula field to ACTUALLY substitute line feeds, carriage returns, and paragraphs here is a solution I (slowly) figured out. Use the following formula to URL encode the text, and then substitute the other stuff you do want back in. Despite all of the suggestions I saw in here, you can’t substitute the \n with anything because airtable just adds it back in. SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ENCODE_URL_COMPONENT({Your Field}),"%20"," “),”%3F","?"),"%0D"," “),”%0A"," “),”%3A",":"),"%2F","/"),"%21","!"),"%2C",","),"%3B",";"),"%27","’") If there are still some encoded characters showing up, just go here to grab it and wrap it again with another substitution. If anyone has a better solution, lemme know. DON’T SAY I NEVER DID ANYTHING FOR YOU!

Hey guys, so im working on an invoicing system. I have three columns: Item Price, custom price, and applied price. I’m in need of a formula for the applied price column that says "default to item price, but if custom price is > 0 use that custom instead. any takers??

Is there any 3rd party formula editor that would show the parts of Airtable formula in different font? e.g. IF formula IF - would be one color formula conditions - would be another color field names - field names in the formulas would be different color “text” - text between “” would be in different color brackets - bracket pairs would be in different color and when I touch the bracket, the paired bracket would glow. Formula editor should give me basic error correction e.g. show missing brackets. If miniextensions had a better formula editor for Airtable with error checking, I would be willing to shell out the $39 per month for it.

Hi Everyone, hoping for some help…I’ve browsed several topics/posts and have not found the solution I’m looking for yet… Say the project event date is July 25th…and it has 16 tasks that are each dated 1 week prior. Example TASK1 = WK01 which is 16 weeks PRIOR to July 25th. TASK2 = WK02 which is 15 weeks PRIOR to July 25th …and so on…I tried a ‘switch’ formula which was promising but still didn’t give me the effect I wanted. Thank you in advance, Mel

Hello, Not sure what formulas/table configuration I should be using here. I have a table of responses to pre and post-program surveys and want to be able to compare the responses to the pre to those of the post (See image below). For each question in the survey, I would like to know the following: how many people responded with each answer for each survey (ie: 8 people said they weren’t at all excited in the pre-survey but only two said they weren’t at all excited in the post-survey). The percent of people who responded with each answer only out of those who answered that particular question. I know this will most likely involve linking records and rollup fields in a separate table but I have been trying to figure this out and nothing has been working. Any and all suggestions would be so helpful.

I have an if statement that adds 2 different types of fields together (Lookup and Currency). I cannot format the lookup field to become a currency field and I think that because of that, I cannot do any mathematical operations between the 2 fields. Is there a work around to this? I’ve tried concat-ing a “$” in to the Lookup but no such luck. Thanks

Hello there I have a list of shortened Product numbers in my table per cell. The content of a cell looks like this: Example one 1234567/8/9/6345/6 (01) What it means is: “there are unique Product numbers 1234567, 1234568, 1234569, 1236345, 1236346 and they belong to group 01” Example two 1234567/2434567 (12) What it means is: “there are unique Product numbers 1234567, 2434567 and they belong to group 12” Logic is as follows: if the preceding number contains all the values from the start, insert slash and display only deviating numbers afterwards. Repeat from next predecessor left. Print any number after the slash that is not contained (in order) in predecessor. Product Number length is always 7 digits there will be between one and 6 unique numbers displayed (example above shows 5, to be clear what I mean). Never less, never more. Group will always be 2 digits in brackets What I would like to achieve: add 6 Columns: “1st Number”, “2nd” Number" etc… each displaying the full number to

Hi, I created an “adjuster value” column to tweak the amount of stock to bring it to what I need it to be as different types of consumption columns are subtracting from it. For this particular record, when I tried to use a negative number in the ‘adjuster column’ to bring the available qty to zero, I get a negative zero?!? Either this is a bug in Airtable or there’s something wrong with my formula. My formula is simply Produced - A - B - Adjuster Value = Available Any help is much appreciated. Thanks.

I want to be able to split a url address into its component and use them as I may need. What I have: “Airtable Universe” What I’d like to achieve “https://” “airtable.com” universe how would you handle this use-case? can you provide any smart solutions? thanks in advance

Hey Everyone! I need a little assistance with my formula here. Basically, I’m trying to pack alot of information into my primary field so that it appears on the calendar blocks. I have a few fields of information here: Clinic Name Clinic Date Type of Vaccine Offered Number of Doses Offered Primary Team working Supporting Team working (if any) My question is this: Sometimes there won’t be a supporting team. Is there a way to build in an “if” statement to my formula to indicate “If supporting team isn’t empty, add this clause”, essentially? Here’s my current formula: DATETIME_FORMAT({Clinic Date},‘MM-DD-YY’)&" “&{Clinic Name + Activity}&” - “&{Lead Team}&” Supported By “&{Supporting Team}&” - “&{# Doses Scheduled}&” "&{Vaccine Dose Type}

Hello wonderful community! I am still having trouble wrapping my brain around IF/THEN situations and thought I would just ask :slightly_smiling_face: I have 2 date fields in my record: Join Date Cancel Date I have a formula field that calculates months committed based on todays date that is working great: DATETIME_DIFF( NOW(), {Patronage Since Date}, ‘months’ ) But I want to have the formula look for a Cancel date and use that first…and if there is no cancel date to go ahead and use todays date. How to I amend my formula? Thank you so much!

I wrote the codes as below: IF({Priority of task} = OR("Emergency","Important","Urgent"), SWITCH({Days until deadline},3,"3️⃣",2,"2️⃣",1,"1️⃣",0,"‼️"),"This is not an important task") But the result is that all tasks shows “This is not an important task” even the priority is emergency. I wonder is it due to the misuse of OR() in the formula? Can OR() be used like IF(… = OR(…, …, …) , … , … ) this way? Thanks.