Formulas

Hi, I work for a public television station and I’m creating a content database to store records of our productions, improve workflow and communication, etc. We have to submit descriptions for tv listings and the character limits have to fall in the range of 101-250 characters. I’m trying to create a formula that displays the number of characters and let’s the user know if the characters are below 101, between 101-250, or above 250. I find people need the visual warning since I can’t set a X amount of characters or character cut off. This is the best I can figure out so far, but it’s a little odd: LEN({Episodic Description}) & IF(LEN({Episodic Description})>250, " ❌ Too Long", " ✅ Good") & IF(LEN({Episodic Description})<101, " ❌ Too Short", " ✅ Good") For example, if some is under the 250 limit the Good pops up. If there isn’t a better formula I can definitely change the tag from " :white_check_mark: Good" to “Over 101” & “Under 250”. Any ideas? Thanks!

I want to find if an item exists in a certain column in my table. Is there a way to implement it?

Hi all, I have been searching for this answer and haven’t came up with anything that is clear enough thus guides me in writing this formula. Let me try to set it up… I have a multi-select column (call it column A), which is a list of months. I have another column (call it column B) that uses a datetime_format formula to show the current month. I want to write a formula in a third column that returns a value (nothing specific, whether it is yes/no, a number, etc.) if the current month (column B) is selected in column A. Is this possible?

Hi there, I am using a look up field in a formula (on another field). The formula is just CONCATENATING the look up field, but I receive an #ERROR! when I try this. Is there a way around this. Cheers! Sam

IF(Paid>0,({Profit}/{Paid}*100),"") This is my current formula after playing with it for some time. I am calculating ROI, with many of my records containing no initial investment, which left the result of {profit/paid} as infinity on those lines. I want to easily calculate my average ROI, so I can’t have the infinity records. Initially, I thought I could add to the formula IF({profit/paid}=“infinity”,""), but that formula failed, so I ended up with the above. The problem is, because of that formatting, I now cannot format the formula to show results as a %. Any ideas?

Hi There. I’d appreciate getting my Emailed Prefilled Airtable Forms Link to sync up with my AirTable Data. I have 4 Fields being prefilled into an Airtable Form. First Name Last Name Email Address Mobile Phone # I wrote a formula (with the help of the amazing Airtable Community) to pre-populate filled fields into an Airtable form. The link for the form with the pre-populated section works perfectly in my AirTable Base. The problem arises when I use Airtable Automation to email the link. The emailed link is different from the link that was created in the Airtable Base. I’d appreciate any help to get the emailed link to match the formulaic link shown in my Airtable. Thank you. Result in AirTable: https://airtable.com/XXXX?prefill_First%20Name=Mickey&prefill_Last%20Name=Mouse&prefill_Email%20Address=111%40111.com&prefill_Mobile%20Phone%20%23=(111)%20111-1111 Result when Emailed: https://airtable.com/XXXX?prefillFirst%20Name=Mickey&prefillLast%20Name=Mouse&prefillEmail

I am using a database to keep track of clothing consigners. In my “seller” sheet, my first column is the ID column and I was using LEFT({First Name}, 1) & LEFT({Last Name}, 1) to assign IDs to the sellers. But now I have an issue where I have 2 sellers with the same initials - so I need the formula to count up as well (IF {ID} IS_SAME, COUNTA, LEFT({First Name}, 1) & LEFT({Last Name}, 1) So I should be getting this for IDs that continuously counts up - but it’s not working and tells me the text is wrong. HELP! AB AB2 CD CD2 CD3 FE GH GH1

Hey, we partly have a subscription model in our company and our deals are closed for a certain period of time. For example a deal is closed for 3 months and a new invoice is issued every month. How can this be easily displayed with airtable in terms of forecasting on monthly revenue bases?(Especially the automatic transfer to the next month) thanks in advance. best

Hello community I’m trying to add this function to the main column. This is the formula concatenate I use to show specific data. CONCATENATE({ :anger: Nombre Jugador},’ ‘,’|’,’ ‘, MID({ :anger: Año},3,2)) &’ '& { :star: :x: :heavy_dollar_sign: :older_man:t2: }& { :fire: }& ’ | '&{ :anger: Club 20/21} What I am looking to do is replace the star :star: emoji by :fire: when this column contains the emoji. And when doesn’t maintain the :star: emoji. Thanks in advance for your time and effort. Regards.

I am looking at attempting to combine the two functions into a single input. The second is to catch jobs that go overdue earlier. Original Question: Formula to track days past a set date, multiple outputs Justin Barret provided this code: IF( AND( NOT({Work Completed}), TODAY() >= {Due Date} ), IF( {Days Elapsed} >= 84, "12", IF( {Days Elapsed} >= 56, "8", IF( {Days Elapsed} >= 28, "4" ) ) ) & " weeks+" ) and I was able to use that to create this: IF( AND( TRUE({maintenance job}), TODAY() >= {Due Date} ), IF( {Days Elapsed} >= 14, "2 weeks+" ) ) )

Hi all! I am trying to convert a current set of data w/ formulas from Excel to Airtable. I am running into a roadblock when looking for an equivalent to Excel’s = Rank and =Rank.EQ. Any suggestions on formulas or alternative solutions?

Hello, I’m trying to organize some data from a csv upload. In the csv upload, there is a “Date” field, and I want to create a daily summary of all records per day. For ARRAYUNIQUE(values) to work, I’d have to link all of the records with that date. However there are many days from one single upload. Is there a way to get all unique days from the whole table, or a way to link all records per unique date? Thanks!

I had a group of students fill out a form in which they gave star ratings to a group of submissions. So the result is a table in which each record/row includes one student’s ratings. The column headers are the submitters’ names. What I want to do is calculate the average rating for each submission and then sort the results by that number (highest- to lowest-rated submissions). In my table, I can get the average to show in the summary bar. But then I don’t know how to create a table or view that allows me to sort those averages of each column. I’ve read over a bunch of other posts that talk about rollups and formulas for calculating averages, but I have not been able to figure out how to make it work in my case. Wondering if I need to have two linked tables somehow. Thanks for any suggestions.

Hi guys! I am looking to transform a lookup result into a Markdown format. Doing this will allow me -I think- to then export that field via Integromat into Webflow and displaying it in a bulleted format. In Webflow, I am using what is called a “Rich Text field”. My guess is if I do a formula that takes the info I am looking up and then transforms into a Markdown or even HTML-kinda format this should be possible. Has anyone done this before? Any ideas? I am attaching a photo of the lookup field in Airtable, the rich field in Webflow, and how its supposed to display in the end. Thanks!

Hi all I’ve got a base that records the bookings of meeting rooms. It’ll be used to schedule bookings across all the rooms available. So I’ve got something like this: {Room} - e.g Room 1 (This is a linked field) {Start Date} {End Date} I need to be able to check whether a {Room} is available when making a booking. I don’t want a room to be available as an option if it’s been booked. If there’s a clash of dates, then a room can’t be selected. What would be the best way of achieving this?

Hi all! I’m setting up a Zap from Airtable to Webflow CMS, and it’s all going great (loving it!), but I can’t seem to import multiple images in Airtable into Zapier’s action then publish that into Webflow CMS. It only seems to attach one image at a time. Is there a way to zap multiple images into Webflow’s CMS image gallery ? Thank you all :slightly_smiling_face:

I am trying to find a formula to search records for the next 7 days (or 1 week). I tried the formula DATEADD(TONOW({Birthday}), 1, 'week') or even IS_BEFORE( DATEADD(TODAY(), 1, 'weeks'), {Birthday}) to no avail as my records came out from starting the beginning of the year. I want to find records that are within the next 7 days from now as shown on the image. Anyone has a formula to this?

Hi, I am trying to clean some of my data but I’m not sure which formula to use. I have three columns (URL, Date, Data). I need to extract the strings from the data column, the date and the URL (which are always different). In the Date column, I have LEFT(Data,10) which returns the date perfectly. In my URL column, I have MID(Data,11,256) which returns the URL. I’ve set up a count of 256 characters to be sure I get all of it but I’m sure there is a more elegant way to do it. Now the main problem is that sometimes I get 2 identical URL in the data column but I only need one of them. So how can I be sure to always get the first URL and discard the second one if I get a duplicate one. Many thanks in advance!

Hi all, I am struggling to find the error in my nested if() statement. I am trying to create a ‘status’ field for project outputs based on their due dates, using a check box ‘complete’ field, and two date fields. If checkbox complete is checked, show “Complete” If not complete and the original due date has not passed, show “On Track” If not complete but original due date has passed but not the tolerance (adjusted) due date has not passed, show “Overdue within tolerance” If not complete, but both original and tolerance (two seperate date fields) have passed, show “Overdue exceeding tolerance” Currently the formula I’ve used is this: IF({Complete?} = ‘1’, “Complete”,IF(IS_BEFORE({Baseline due date (DD,MM,YYYY)}, TODAY()), “Overdue within tolerance”, IF(IS_AFTER({Tolerance (no Covid)}, TODAY(), “Overdue exceeding tolerance”, “On Track”)))). I am not sure if I should be using an AND somewhere. Currently the formula is accepted but only ‘Complete’ and ‘Overdue within tolerance’ are returned

IF({amount of discount}=0," ",IF({amount of discount}<0,{amount of discount})) I got this to do what I want - only show a value on a sales receipt if the value is <0 The problem is that the answer is considered a string answer and I cannot format it to display as currency. All fields leading into this formula are numbers (formatted currency) I want the results to be displayed as currency. Suggestions?

Hi SQUAD, Maybe this is a stupid question, but I’ve been wracking my brains trying to figure it out and couldn’t find a solution. My Scenario: Table 1 - list of 300 users with assigned devices by serial number. Table 2 - list of 1000 devices with other specs, including serial number. What I am trying to do Create a third table with all Serial Number listed on Table 1 (I can copy manually) and display on Field 2 the name of the user (record from Table1) and display on Field 3 Other Spec (record from Table2). On excel, it’s something simple that I can resolve with VLOOKUP (value, table, col_index, [range_lookup]). Any solution/idea for Airtable? Thanks so much!

Hi, I am trying to get a field to show either Priority Normal, Priority high or Priority Critical depending on how far away from today the “manufactured by” date field is. ie. if 3 weeks away priority normal if 2 weeks away priority high If 1 week away priority critical Aby help would be greatly appreciated.

Hello, I’m new writting formulas, I’ve read all the guides on airtable’s website and other posts in the forum. However I can’t get my formula to work. I only need that when the value of a cell is equal to a number a specific name appears. This is what I’ve wrote; IF (Total = “6.99”, Hernia discal, BLANK()) But airtable won’t let me keep the formula because it’s misspelled. Any advises? Thanks.

Hi. I’m trying to use DATETIME_Format() inside of a formula field to display the day in which an order was placed. These are the dates of the orders. For reference, the 18th took place on a Sunday. The formula block is a very simple DATE_FORMAT() function, showing the day. However, it seems as if the date format is incorrect and is defaulting to GMT time (adding 4 hours to the time fields, causing the time to roll over to the next day). I don’t know how to fix this, and I can’t change it because under formatting it says: Can someone please help?! Thanks

I am building a database for a music education charity which has five bands, about one hundred members and over a hundred musical instruments which we loan to members. With different tutors issuing instruments at different venues, I want to keep track of which members have what instruments, which instruments are in the store cupboard and which ones are elsewhere eg repair/service. I have various locations on a single select field so an instrument can be allocated to a location. Then, if “Existing Member” is selected, another single select field of member’s names comes into play. Each instrument on the [Instrument Stock] table has a unique ID directing to a form and profiling the Instrument ID and a time stamp. To increase compliance with logging instruments into the database, I have made some QR code labels which stick to the instruments and their cases. These open form refilled with the instrument ID and a timestamp. After about two weeks building the system, I became aware that for