Formulas

I am trying to do a formula where I have a row with numbers (=calculation field/column) and depending on the number a certain text value (Tier 0 / Tier 1 / Tier 2 / Tier 3) should be the result in my formula fieldTier 0 : 500,000 - UnlimitedTier 1: 100,000 - 500,000Tier 2: 40,000 - 100,000Tier 3: 500 - 40,000I tried several approaches but none of them worked, I always get an error message.IF( {Calculation} < 40000, "Tier 3", IF( {Calculation} < 100000, "Tier 2", IF( {Calculation} < 500000, "Tier 1" , IF( {Calculation} < 100000000, "Tier 0"))))ORIF({Calculation} < 40000, “Tier 3”, IF({Calculation} < 100000, “Tier 2”, IF({Calculation} < 500000, “Tier 1”, “Tier 0”)))ORIF({Calculation} < 40

Hi, i have a formula field named "Jenis Parts Regex" where its value is "15.9mm Karet stabil FR RH/LH ES1|05".I want to extract anything that matches "end_of_name" field, in this case "ES1|05", and the result shall be in "regex replace" field.But what i got there is "15.9mm Karet stabil FR RH/LH |". Why am i getting "|" ?Here is my formula in "regex replace" field:REGEX_REPLACE({Jenis Parts Regex},end_of_name,"")Please helpThanks 

Hi there!I am trying to create a formula that looks at an entry (single select) and if that entry is = A, B or C say "Blabla" and if it says anything else say "Haha".I have hence tried to nest OR() into IF() as followed:IF(entry=OR("A", "B","C"), "Blabla", "Haha")I am not shown any error but the formula result is wrong (It says "Haha" when it should say "Blabla').Any thoughts?

I am trying to build an automation or formula for this scenario:Student #1 has been enrolled in school from the dates 7/1/2018-6/30/2023. I have a field in this table (Students table) for Enrollment date (7/1/2018) and graduation date (6/30/2023). I need the field " School Years" to populate with the respective school years this student was in school for. Ex: SY 2018-2019, 2019-2020, 2020-2021, 2021-2022, 2022-2023 - this is a multiselect field. This will be unique for every student, as their enrollment and graduation dates are varying...I have build a table named School Years and have the start and end date for each school year as a record. I would love this to be automated so that every time we have a new student and their graduation date changes, it will update what school years they were here for.This seemed so easy to do in my head and I have tried every way to get this to work, but I cannot see

Hey all - I'm running a formula column with a lot of IFs in there. Only 1 out of the 6 possible responses returns a text field, the rest are date fields.The dates come out in a very ugly way, this is how it looks: 2023-07-12T23:06:37.000ZIt basically makes it impossible for me to use / property view.After some playing around with it, I've understood that the issue here is with the fact that one of the values returned is a text field.However, the way I have the formula and the intended use. Unfortunately, I can't do anything else but have a text in one of the IF's (since it's the if true, not false, that I could just leave empty).I've tried using the time parse formula like this: DATETIME_PARSE(the if formula), but the result is an error.How do I make this work?

Hi I want to extract a portion of a text from a google drive share link.The format of the drive link is like this:https://drive.google.com/file/d/1IklI1OAW8XLtTKDfwW75BNcve8z8xQ57/view I want to extract the text after "d/" and will end at before "/view". So the intended result should be "1IklI1OAW8XLtTKDfwW75BNcve8z8xQ57 "Any ideas how?Thanks a lot!

I’m new to the airtable community and wasnt sure how to reply to an original post that has been closed. But I found a solution to this #ERROR! when using ENCODE_URL_COMPONENT() on lookup? post. You can use ENCODE_URL_COMPONENT({lookupFieldThatYouAreTryingToEncode}&""). This adding (&"") solution is a lot neater than adding another already encoded field to the parent table.

Hello! I am currently trying to extract information from an inconsistent string of text and having a tough time.Ideally, I would be able to drop the MONTH and DATE from the String of text and KEEP the all the text along with just theYEAR. The trouble is, both the intro text (represented as X's) and the dates are not consistent lengths. EXAMPLES OF TEXT:  DESIRED OUTPUT: XXXXX.RTW.Men.7.5.2023XXXXX.RTW.Men.2023XXX.RTW.Women.12.10.2022XXX.RTW.Women.2022XXXX.RTW.11.6.2022XXXX.RTW.2022

Hi community, I'm French and when an order is generated on my Shopify, i receive the shipping adress in this format : "7 rue notre Dame" (On shopify and Airtable)For another application, the house number (7 in my exemple) need to be at the end and not the beggining.Do you have a formula or a solution to take the number at the beginning while whe can have 1, 2 or 3 numbers or nothing at the beggining of the shipping adress ? Thank's a lot for your help, Matthieu

I am building an implementation plan for my projects split by a 2-week sprint cycle. While I was able to get Week number from date of delivery, I am stuck trying to covert it into a Sprint number.

I want to use an airtable search formula, I already have one working, but I need to upgrade it a bit and make it a little more complicated. Current: FIND("*{{1.postcodeZone}}*", {Postcode Zone})I want to add 2 more requirements, so the first is a particular checkbox field must be set to yes/true.IE "Field 1 = true"AND then additionally, I want another requirement to be that a longer field, contains certain text, or the phrase "all"IE "Field 2 CONTAINS abc or 'all'" So I want all these 3 above parts to be required for anything to be returned

This might be the most random formula ever, but having a hard time figuring it out/ wondering if it is even an option.I have a list of records that represent tasks to do.I want to have these tasks in sets of 3 records.I dont want the tasks grouped by airtable where you see all of the various groups.I want to have the table literally only show the current set of 3 tasks and then they have a checkbox field next to them and as you check them off they are filtered out until all 3 are filtered out and the next 3 tasks come into view and it repeats like that.Totally not sure if this is even something that can be done, I was thinking of manually adding in set #s and filtering for that, but then I dont know how you filter just the set that is between the one that is checked off and the rest of them.This might be a too complicated one to create, but appreciate any help, thank you!

Hello!How I can join two columns what I need Join with IF - example:AreaCityLocationHaljalaHaljalaHaljalaHiiumaaPaladeHiiumaa | PaladeMuhuMuhuMuhuKullamaaKullamaaKullamaaSaaremaaAsteSaaremaa | AsteIf the City and Area name are same then formula shows that name only one time

Hi all. I'm trying to create a formula field that will calculate if the date should be the 1st or the 15th of the month based on another date and time field that can be any day of the year.If the date field is any date between and including the 1st and 14th of the month, the formula needs to show the 1st of the previous month.If the date field is any date between and including the 15th and 31st of the month, the formula needs to show the 15th of the previous month.For example, if the date is July 13th, the formula should show June 1st, but if it's October 17th, it should show September 15th.Any ideas on how to accomplish this would be much appreciated. Thanks.

Hoping for some help with how to setup for a formula to calculate the "Stabilized Occupancy" as shown below (% shown is wrong and is a placeholder in my table design).  On a table, I have "Leased" "KV" and "Committed".  I need to calculate the following(Leased - KV + Committed) / 1,000,000 (633470 - 45472 + 290) / 1,000,000 What would be my best solution to pull this formula in the "number" Element in my Interface?  Thanks!  

Hi all, I'm looking at creating a Button on a base which creates a draft email to send to a client. Within the body of the drafted email, I want to include a unique Calendly Link hidden beneath a "click here" button on the drafted email. I've got so far with it and the formula is correctly drafting the email, and body, but the link stops at the 'clickable' link part. Here's the part of the formula that works;"mailto:" &{Email} &"?subject="&ENCODE_URL_COMPONENT("Book a Meeting")&"&body=" & ENCODE_URL_COMPONENT("Hi "& {First Name}&",\n\n I hope you're keeping well...")&  The next part is the bit that stops working;ENCODE_URL_COMPONENT("<a href="&{Calendly URL}&">click here </a> to book a meeting\n")

I received some contact info (Excel format) from another source that I'd love to incorporate into my Airtable. It's over 1000 records. There are two columns. The first column is fine as is.The 2nd column lists a person's name and email in this format: First name Last name (email address)It's complicated by the fact that many of the fields in that column have 2 or sometimes 3 people, so it's listed like this within the field:  First name Last Name (Email), First name Last name (Email)And sometimes even 3 people, like this: First name Last Name (Email), First name Last name (Email), First name Last name (Email)Help!!! Thank you!!!!-Amy  

I am trying to figure out how to create a field that will update every time the "CLIENT STATUS" field is changed and maybe another field that says how many days that record has been in that status. Any ideas? Thanks!

Hello all, I'm working with formulas, but got stuck on something. What I'm trying to accomplish here is simple, but not sure how to formulate it. I have a linking record for an invoice for NET Terms: NET15, 30 etc. I have the formula set up, but I want that converted into a date format rather than what I have.  IF({TERM}="NET15", "15 Days from 'Due Date'", IF( {TERM}="NET30", "30 days from 'Due Date'" ) )  Simple, but this is the output:Pretty simple, however I was testing the formula, and for invoice structure - I wanted a date format approach. So If NET15, then have the "Due Date" 15 days BASED off of the "Date" field. I don't know how I'd structure that, but that's the primary goal. Instead of showcasing "15 days from 'Due Date'", I'd rather it show a date format. EX: D

Good afternoon! There are several product sections, each product has its own identification number. How to make the id is filled in automatically in a row (example P00005) and you need to save the number of characters (example, P00009. P00010)

Hi everyone! Excel offers this ability so I'm wondering if there's a similar formula I can use to achieve this here? I want the "Link" column to be populated with a URL but the ID at the end of the URL needs to be populated with the "Number" column.Bonus points if you can show me how to hide the URL string behind the same name of the number column as well.

I'm ok at writing formulas, but I always need to look up stuff, and it takes me a while to do basic things as it doesn't come naturally. Which means when I go back to an old formula, it takes me quite a while to understand how it works. I know in other forms of coding, you can add notes that don't affect the code - i.e. to help another programmer understand what something is doing. Is there a way to do that in the formula fields?

Hi I'm trying to use a nested formula to calculate a risk matrix if an entry has different conditions: F({ Platform product data sources (from Submissions)} =Platform Product/Data, OR ({Subproccessor (from Submissions)}=Subprocessor), ‘CRITICAL’,IF ({ Platform product data sources (from Submissions)} =HRIS (People data), "MEDIUM",IF({Platform product data sources (from Submissions)} =Commercial and Financial platforms/data, 'HIGH','low’)))But I'm getting a formula error, I'd appreciate any help on where I'm going wrong! Thanks! 

I use the primary field to display the content of another field (single select)In the primary field, I have a formula that is the other field name (ML ID)When I add content to ML ID  it saves and displays ok. However, it doesn't show up in the primary field. The only way I can get it to display in the primary field is to delete it in ML ID and then select it again, whereupon the primary field displays it.I attach screenshots14.51.48 shows the field ML ID in which I've added abc14.52.59 I've removed the abc (by selecting the blank or empty) shows the primary field which displays (for no apparent reason) --14.53.20 shows the primary field and ML ID field as it should beAny suggestions, please? 

I have a multi-select field of colors.  I have some records that have the same color listed mulitple times (i.e., yellow).  I would like to remove the duplicates.  I have tried the work-around in "Rollup Array Unique?" (make the field a lookup field and then use arrayunique(lookup field)), but I still get duplicates.  Any other suggestions of how to get rid of the dupes?