Formulas

I have a table like this: | level | exp | |1|0| |2|500| |3|1,500| |4|3,750| |5|7,875| |6|14,175| |7|22,680| |8|32,886| |9|44,396| |10|57,715| |11|72,144| |12|90,180| |13|112,725| I want a formula that outputs the right level when an exp number that is within a range is given. For example: With an input of 700, the formula should display 2 because the closest available exp value (rounded down) to 700 is 500 and the corresponding level is 2. With an input of 10,000, the formula should display 5 because the closest available exp value (rounded down) is 7,875 which corresponds to level 5. With an input of 33,886, it should output 8. The solution in sheets would be something like (with E2 being the input): =IF(ISNUMBER(E2),INDEX(SORT(A:A,B:B,0),MATCH(E2,SORT(B:B,B:B,0),-1)),) or =QUERY(B:A,"select A where B >= "&E2&" order by B limit 1",0) How is it po

I’m try to create a new field (Event End Date) based on the frequency of the Event (Event Day) and when the Event Day Started (Event Start Date). I started using the formula below but it doesn’t work. In addition, I need to code for Event Days where it’s a single day (i.e., “Thursdays”, “Fridays”, etc.) in which case the Event End Date = Event Start Date. IF( FIND ( "Tue - Fri", {Event Day}), DATEADD({Event Start Date},4, ‘days’)), IF( FIND ( "Tue-Fri", {Event Day}), DATEADD({Event Start Date},4, ‘days’)), IF( FIND ( "Wed - Sat", {Event Day}), DATEADD({Event Start Date},4, ‘days’)), IF( FIND "Tue - Thur”, {Event Day}), DATEADD({Event Start Date},3 , ‘days’)), IF( FIND "Tue - Thu”, {Event Day}), DATEADD({Event Start Date},3 , ‘days’)), IF( FIND "Wed-Fr”, {Event Day}), DATEADD({Event Start Date},3 , ‘days’), IF( {Event Day} = "Thur - Fri", DATEADD({Event Start Date},2, ‘d

I am sending URL’s from Webflow which does not have URL validation easily integrated. I want to check and add the https:// to the beginning of the URL if it is not present when the field is created or updated. I have been looking through to forums but can’t seem to find an exact match. Would love some help with this please.

Hi everyone - I want to create a formula that will return a date value only if the value in a separate field {campaign duration (days)} is greater than 10. I also want the field to stay “clean” even if the bounding dates (Live and End) aren’t filled in. Please find below the formula I’ve created: IF({Campaign Duration (Days)} < 10,"",IF(AND({Campaign Live Date},{Campaign Duration (Days)}),WORKDAY({Campaign Live Date},{Campaign Duration (Days)}))) Unfortunately the formula returns the following: 2021-08-19T00:00:000Z. Can anyone tell me what I’m missing? The date itself is correct but I’m not even sure what the rest of the value is. I thought at first that this was a formatting issue but according to Airtable the result isn’t a number or a date so it can’t be reformatted.

Hi Airtable folks. I’m wondering if anyone has run across this message when writing Formulas in Airtable: “Can’t save field because it exceeds the limit of formula dependencies” This happened when I was splitting-out 100 image URL’s into individual fields. Everything worked fine until I tried to submit the 99th image Formula. Here are screengrabs of my Formula, in case anyone sees an inconsistency. I highlighted in RED where I made additions/changes. But again, images 1 through 98 worked just fine:

I need to generate a lead time result based on the contents of a product description {Item Name}. Any help here would be much appreciated. If find “Spider” in {Item Name} result = 30 If find “Raw” in {Item Name} result = 3 If do NOT find “Raw” or “Spider” in {Item Name} result = 7 I tried the formula below, using all the colors in the descriptions to try to isolate the result, but it sums up the results. ie. “730”. IF(FIND(“Raw”, {Item Name}), 3) & IF(FIND(“Black”, {Item Name}), 7) & IF(FIND(“White”, {Item Name}), 7) & IF(FIND(“Clear”, {Item Name}), 7) & IF(FIND(“Turquoise”, {Item Name}), 7) & IF(FIND(“Spider”, {Item Name}), 30)

Hello Awesome AIrtablers people. I am trying to transfer a formula from our Budget tracker Excel to an Airtable new base, It’s a bit tricky. We are a VOD online service and they are trying to see how the content is being consumed and amortized through the years, this has some rules 1- So they have set some rules when it comes to amortization percentage through a logic they already have, This might change according to some research, that’s why they have it in a separate tab inside excel and it’s synced inside the excel through a lookup formula, so if they change the percentage values will change in the main table: look at this image: 2- so according to these percentage: they follow and calculate the amount to be amortized each year according to each percentage so let’s say the total cost of Title (A): is 5000,000M, the release date is : 1/1/2021 1st Yea

I am using a base to track orders. I have 3 tables: Orders, Orderlines, and Assets. The Orderlines table has an Assets column that links to the Assets table, with the option to allow linking to multiple records. The Orders table has a rollup column Assets that refers to the Assets column in the orderlines table, and applies the ARRAYUNIQUE(values) function. Given these rows in the Orderlines table, the corresponding row in the Orders table does NOT display unique assets: Asset A; iPhone 8; USD 400 Asset B; iPhone Case; USD 30 Asset A, Asset B; Shipping; USD 12 The rollup column Assets in the Orders table has this value: Asset A, Asset B, Asset A, Asset B Using ARRAYJOIN(ARRAYUNIQUE(values)) illustrates the problem: Asset A; Asset B; Asset A, Asset B Notice that there THREE values that Airtable considers unique:

Thank you @ScottWorld for your assistance with the first part of the formula While that fixed one issue - it introduced another issue I know that the results are showing in my time zone, but I don’t want my time zone to be used - I want the client’s timezone (Pacific for the particular request)

I have followed this Video Structuring Line Item Detail - YouTube in 8:30 But i cannot get the PO details as he got (multiple rows) My formula SUBSTITUTE({Rollup to Text},“Item”,"\nItem") does not return the correct output. Please help

Hello Everyone, I’m using a library called “Airtable Python Wrapper” which can be found here: Airtable Python Wrapper Documentation — Airtable Python Wrapper documentation It has a method, “get_all” that will take an Airtable formula to filter records and return the ones that make it through the formula. filterByFormula = 'IS_AFTER(LAST_MODIFIED_TIME()+\\"2021-01-25T14:23:41.000Z\\")' def get_date_changes(): query = AT.get_all(formula=filterByFormula) However, that formula returns an empty list. I know that there are records in that table with modification dates later than that as new records are added almost every day. Can anyone tell me what might be wrong with that formula? Thanks! -Dan

Hi Everyone, I have a base for auditing which asks if certain documents are present by linking to document records in another table. I’d like to then be able to collate a list of the missing documents (those not selected) for part of the feedback. Is there any way to do this? I’ve been going mad trying to figure it out!

What I’m looking for is to be able to send out a URL to collaborators that will bring them to the attachment view window inside Airtable so that they can view my attachment and leave comments. I have tried assigning a trigger & action in automations like this: (“Drafts” is what I named the attachment field") but all this does is open an image of the attachment in your browser. It does not open Airtable. PLEASE HELP!

Hello! This is similar to another post: Consolidated Count from another table by date field, however I am trying to group by 2 fields. Table 1 has 3 fields (Date, Shift, Count) Table 2 has 3 Fields (Date, Shift, Total Count) Table 1 has 6 records: 1/1/2020, Morning, 2 1/1/2020, Morning, 1 1/1/2020, Midday, 1 1/5/2020, Midday, 3 1/5/2020, Afternoon, 4 1/5/2020, Afternoon, 3 I want table 2 to sum up all of the counts by date and shift. So I would want table 2 to show: 1/1/2020, Morning, 3 1/1/2020, Midday, 1 1/5/2020, Midday, 3 1/5/2020, Afternoon, 7 How can I do this? I have Table 1 grouped by Date&Shift, but I was hoping to show this in a cleaner way in Table 2. Similar to

Hi I want build attendance system tracking time to work and out of work I want to use FORM where you can pick Name and slide checkbox IN. After work pick Name again and slide checkbox OUT. The problem I have is how to get the OUT record in some line where the IN record is so I can make formula and calculated the time?

A base used to inventory products. Current relevant columns are: Product Category (single select with option of Painting, Banner, Description (Formula) Product Name (text) Substrate (single select) Size These products are categorized Categories in this Column include: Banners Frames The Description Column is a formula that creates a meaning full sentenced based product description on the columns. So currently I have: {Product Category} & " " & “of” & " " & {Product Name} & " " & “printed on a” & " " & {Size} & " " & {Substrate} In this example it will output: Picture of Glacier printed on a 16 x 20 hardboard gatorfoam What I need is an IF statement in which if the product category is “Painting” the output will have the words “Printed On” as shown above otherwise this word is

Hi everyone! I have a work week that operates from Monday-Friday (24 hours). I am trying to develop a formula that would show the total hours remaining from the current time until the week closes each Friday at 11:59PM. This would have to reset each week. So far I have FORMULA: DATETIME_DIFF(NOW(), {FORMULA NEEDED}, “hours”) This is a part of a much bigger formula. Thanks for your help!

Hi, I would like to use the column number in a view in a formula. Example : If a record is listed as 5th is a view, I would like to use the number 5 in a formula. Is there an internal field giving me this number ? PS If this exists, is there a list of internal fields that can be used ? like RECORD_ID, …

I have three tables, Properties, Contacts, and Companies. Each table has a Last Unsuccessful and Last Successful contact field. Sometimes I have contact information on one or multiple of these tables (i.e. if a company owns multiple properties then I’ll create the linked relationship). I need a way to find the maximum date of when I last spoke to someone, whether I’m looking at either of the tables. On the table below I have two lookup fields and a natural date field for both successful and unsuccessful contacts. I referred to the thread below but not sure why I am getting an error.

I would like to create a formulas to add the specific school year if the date in the column “DATE” is between DATE1 and DATE2 For example: IF date is 5/20/2021 THEN in the column school_year I will have 2020-21 IF date is 9/20/2020 THEN in the column school_year I will have 2020-21 IF date is 9/20/2021 THEN in the column school_year I will have 2021-22 What formulas should I use ?

Hi everyone - I’m still very new to Airtable and ran into a formula error that I’m trying to solve. Ideally, we would like to group tasks by the week they are due. I was able to find the formula to output the week number but can’t seem to find one that works that will produce the following output: “Week of 06/21”. I did find the following two formulas from a previous post: Display the "week of" for a given date starting with Monday CONCATENATE(“Week of”," ",DATETIME_FORMAT(DATEADD({Date},-DATETIME_FORMAT({Date},‘e’),‘days’),‘M/D’)) DATETIME_FORMAT({Date},‘e’)+1,‘days’),‘M/D’)) However, when I go to add the formula to my calculated field it returns an error. Could it be because my {Due Date} field is calculated as well? This is the formula that I am using: CONCATENATE(“Wee

Hi Airtable Community! In my Airtable base, I’ve got two main tables: one for stories written by kids, and another for notes that volunteers write to the authors. These tables are related through a one-to-many linked record setup: there can be multiple notes for a story, but not multiple stories for a note. I’m trying to make two rollup fields: one that displays the text of the first two notes a story receives and one that displays the text of every note after the first two. I’d like to email notes back to authors after they have two notes and then again when they’ve accumulated several more, and would use each rollup field as part of the automation for the email body. The additional wrinkle is that the rollup should only count notes that haven’t been flagged (and therefore shouldn’t be returned), and there are handwritten notes too. My thought was to add an Autonumber field in the notes table–each note will have to have a higher number than the last. Then, the

Hi Community, I have a cruddy report that cannot give me the data i need which forces me to calculate the number of days a person has been helping our organization and I was hoping there might be a formula that can help automate the calculation a bit, but I cannot wrap my head around it (terrible with IF formulas). The report is run once per month and will produce 4 different combinations of dates for the in and out field. Combination 1, OUT date is prior to the start of the month, and IN date is after the last date of the reporting month OR blank . This calculation would then be the same as the number of days in the month. Example for May (OUT is 4/20/21 and IN is 6/5/21 or blank, would = 31 days) Combination 2, OUT date is prior to the start of the month, and IN date is within the month of reporting. This calculation would

Hi everybody, I’d like to check if an attachement field is empty or not. Someone knows how to do this ? Tanks a lot. Fred

Hi there, Can anyone please help me convert the date in this format, Fri Jan 13 2017 00:00:00 GMT+0000 (Coordinated Universal Time) to this 01/13/2017 format? Thank you