Formulas

Hello, I am trying to do a rollup that will only show unique values that appear more than 2 times. In essence, ARRAYUNIQUE(if value appears more than twice). Anybody have any suggestions?

Hi! I’m at a loss for how to write this formula correctly, even after referencing multiple other topics on here. What I’m working with: TABLE 1: “Original Release Date” (formula field calculating from multiple Date fields in Table 1). TABLE 2: “Deadline Date Add” (currently manual number field, but I’ve tried it as a text string with '- and ‘days’ incorporated to the field as well) TABLE 3: “Master Due Date” I need a formula to subtract the “Deadline Date Add” number from the “Original Release Date” and populate a new date in “Master Due Date.” So if “Original Release Date” = 10/12/20, and “Deadline DateAdd” = 10, then I want “Master Due Date” to return 10/2/20 Currently I’m referencing all fields from Table 3 (Table 3 looking up “Original Release Date” from Table 1, and “Deadline Date Add” from Table 2. All date fields are date only, and don’t incorporate time of day/zones. Any help here would be greatly appreciated! Thanks!

I’m having trouble understanding why this isn’t working. Essentially what I’m trying to do is find out how many authors were published each calendar year and each fiscal year. There are multiple issues published each year and each fiscal year, and sometimes they don’t line up. Each issue links to a record for the year and FY, like so: (this is my issues table) I have a second table for the year and fiscal year records: stats by year & fy. What I’m trying to do is a rollup that will count how many author records are connected to each year and FY. There are some authors that will occur over multiple magazine issues, so those duplicates need to be weeded out from the total number of unique authors for each year/FY. However, when I use the COUNTALL(VALUES) rollup, it seems to only be calculating how many issues ar

Hello, I have two fields Phone and Phone Extension. I need a formula that evaluates the following conditions: if Both are present, show Phone if exist, otherwise show Phone Extension if exist if only Phone is present, show Phone if only Phone Extension is present, show Phone Extension if both are blank, show it as blank I’ve been racking my brains out trying to figure this out and I know it’s probably stupid simple, but I spent far to long working on this. I appreciate any help anyone can provide.

Hi everyone I have a habit/task table, the time period is on a multiple choice column (daily, weekly, bi-weekly monthly, etc.). I keep track of the last time it’s done (the date). Is there a formula that can take both into consideration and tell me if something is overdue? for example if it has been more then a week since i did a weekly task.

Hello, I need to separate a complete mailing address into 3 columns. Currently the entire Mailing address is on column, per this picture. In this case I would like to spilt the cell after Department and after 9701 so (Experian Dispute Department in is on Column) P.O. Box 9701 is in a another column and then Allen, TX 75013 is in another column.

I keep trying to google, search and i am not landing on what i need. My coding understanding, as minimal as it is, is only enough to get me into trouble. As a general expression of what i wish to achieve; i want to make a view, that shows items:: are over 28 days or more beyond creation without being completed. that don’t have a specific due date that has passed (If it has passed due date then report as normal from created date). I need a formula that: Looks at the field “Created Date” field and compares to todays date. Looks at the Field “Work Completed” (If checked or not) returning “nil”? if it is complete. Looks at the field “Due Date” field and if (“today”) is not past that date, returns “nil”. Outputs “4 weeks+” for any period 28-55 days Outputs “8 weeks+” for any period 56-83 days Outputs "12 weeks+ for any period 84 days Thanks in advance!

Hi all, I’m trying to make it easier for my teammates to create records, and would love it if I could format the text in a formula field so that its items separated by a comma are displayed in vertical list format. This is the formula I’m using right now, trimmed so no one has to wade through ALL the categories and tags: IF(Category=“Community”,“Art & Music, Books & Literacy, Children & Youth, Community Development, Education, Guns & #NeverAgain, Inclusive Language”) I’d like everything within the second set of quotation marks to display in list format. Is this possible?

Hi, I have a table were I need to figure out how to say if the status is closed. To then move to another table titled closed.

Hello! I’ve been using Airtable for a couple years. I don’t know much about formulas, but I can usually figure out what’s going on. In one table, I have this formula to calculate ages: DATETIME_DIFF(TODAY(), Birthdate, ‘years’). Up until today, it’s worked perfectly. However, for some reason, now it’s giving me an error message. I have a different table with the same formula - it’s also giving me a brand new error message. I’ve tried switching the order DATETIME_DIFF(Birthday, TODAY(), ‘years’), and that works fine too (the age is just a negative number, not ideal). The kicker is, if I change the unit to ‘weeks’ it works no problem - I just don’t have a use for knowing ages in weeks. Any ideas?

Hello, community. Imm attempting to create a résumé table. I want to be able to add jobs that have the following fields. ‘Title’ ‘Start’:formula ‘End’:formula ‘months worked’:numeral. The first entry the job start is hardcoded and the job ended is calculated by adding the months worked to the job start. The second entry job start is a reference to job end from the previous entry. Hope that makes sense.

I am trying to create automatic pricing in Table 1 using pricing from Table 2 I.E. Table 1 Formula (OTY*{Table2Price}) but I can’t figure out how to find the pricing table items in my formula Help?

Hey All, I hope everyone is keeping well given the current pandemic. I have recently started to use Airtable Sync and since then, noticed a couple of my formulas are now showing up with #ERROR (see pics attached). My database is a paid plan, but is syncing to a free database. The DOB is being synced to the new page, but the age is not. I would love some help on how to get the age to show again! Thank you so so much!

Hellloo! Costings within my business have now changed slightly and have added in a new column but I only want the records after 1st October to calculate the 10%. Please help, thank you. IF({Date Purchased}IS_AFTER(01/10/2020),SUM({Net Revenue}*0.1))

Hi, I am using Zapier to pull booking information into Airtable. The information comes in like this: 100-007 Storage boxes x 4.0 @ £4.17 = £16.68 I am wondering if I can split this information up into separate columns (column titles are in italics). Location code = 100 Sales code = 007 Item = Storage Boxes Quantity = 4 Price Per Item = £4.17 Total Price = £16.68 Any help would be much appreciated. Thanks Gal

Hi, I’m a bit new to this and not sure if it’s even possible. I have a table of ‘scores’ with multiple ‘players’. Each player has a score, up to 30 scores each. In another table I have a rollup which displays all the players scores. What I’m trying to do is find the 5 best scores for each player, add as a total, then divide by 5 to give an average score for each player. This should then give a leaderboard of the best players based on their 5 best scores out of X scores. I’ve managed to do this manually in Notion by adding a checkbox formula which when I enter the player score, I look at their top scores and check/uncheck as required. This is fine and works but I’m now looking to make it more automated with Airtable. Is this dooable as a formula or better as a script on a front end with js? So with airtable it works like this ‘Overall Score’ = All Scores + Checkbox = Total Score Average Score = Total Score / 5 Thanks in advance.

Hello Airtable community! I am struggling with a formula and thought I would put it here since you all never let me down :slightly_smiling_face: I want the formula to produce the result overdue IF the contact’s sum of (“level of connection” and “Level of importance”) is greater than 5 and “Last Contact” is more than 180 Days The contact’s sum of (“level of connection” and “Level of importance”) is greater than 7 and “Last Contact” is more than 90 Days -The contact’s sum of (“level of connection” and “Level of importance”) is greater than 8.9 and “Last Contact” is more than 30 Days For reference here is what I currently have (Very far off) IF(DATETIME_DIFF(TODAY(),{Last contact},‘days’)>90,“Overdue”) THANK YOU !!!

Hey guys, Which field type should I use to enter page numbers for each of my table listings? I want to be able to do some calculations on them later e.g. average page number for 10 rows of book listings. I tried using the number (#) field type but it adds an unnecessary decimal place. So for example a page number entered as 350 changes to 350.0. I was going to use the single line text field but wondered if that would prevent me running calculations later on. Thanks in advance if anyone can help.

Hello, Here is a screen shot: What I am trying to do is create a formula for In Stock so that when Condition is New, Quantity decreases by 1 (and if the Condition is not new, it stays with what is in stock). However, as you can tell with Row 3, that is not the case. My formula thus far is = Quantity & IF({Condition (from Maintenance)} = ‘New’) - 1 What am I missing? Furthermore, if Condition has ‘New’ twice in the column (or however many is in the Quantity), such as Row 1, in stock should be blank or 0, or if there is nothing in Condition, In stock should equal Quantity. I have it so when stock gets to 1, the status changes to Order More, and would like it to change to Out of Stock when it is 0 IF({In Stock} = 1, ‘Order More’) & IF({In Stock} = BLANK(), ‘Out of Stock’) Is this doable or do I need many more columns and

Hi, How can I change time format 8:00 into 0800? Thanks Erwin

I have a table with a lot of webshop orders in it. how can I make a view with all orders made to-date a year ago? So I can compare Christmas sale from last year with the sale this year to date. is there a function that checks for order date one year ago and back for example

Hello - I’m trying to combine and format data in order to use in a report…would like to separate with tabs if possible to get the spacing and format to look nice and consistent. (Spaces are not working out correctly if record data doesn’t have the exact number of characters.) I’m trying to doing something like the following, but can’t seem to find anything that produces a tab. I’ve tried “\t” and “ALT+09”. Any suggestions? {Data 1} + TAB + {Data2}

I feel like this question has been asked before, but I cannot find an answer or post that really applies. I have a form where users can input/sort options from one table (a hacked ranking system). Once they the form is submitted, I then want to figure out an automattion using basic text fields, or have multiple formula fields pull the records 1 by 1 into the separate fields in the order they’ve been input by the user’s form submission. A, B, C record order Field 1 = A Field 2 = B Field 3 = C I’ve looked at arrays, converting to strings, tried the string lengths by using the shorthand info, this seems like something I used to do all the time in others, but it’s very clear arrays of data are not well managed in this?

I’m trying to further customize a formula (that’s already working) with additional functionality to detect when text is missing a certain element and, in such cases, to then add that element to the text in a different column, but can’t seem to get anything to work. Specifically, I have a formula that, among other things, takes a URL from, say, Column A, and then customizes/edits that URL in Column B based on criteria from data in other columns. In this instance, Column A is a URL field and Column B is the formula field, and Column B displays updated versions of URLs from Column A. So far the formula is working fine except I also need it to detect when a URL in Column A is missing its trailing slash ("/") and to subsequently add in that slash where it would otherwise go when the URL appears in Column B. I also don’t think it will be as simple as merely finding/replacing instances of <.com> with <.com/>, <.org> with <.org/>, <.co> wit

Building a payroll system and need formula help with splitting up total hours between Daily Hrs, time and a half pay, and doubletime. Here’s my setup: My “Total Hrs/day” is the actual total of all hours worked, calculated with: (OUT-IN)-BREAK Then, I want to limit the amount of hours from the “Total Hrs/Day” that can go into “Daily Hrs” to 8 hours of regular pay. I do this with: IF((OUT-IN)-BREAK<=8, (OUT-IN)-BREAK, 8) This means that if 10 hours were worked, “Daily Hrs” is capped at 8. I want the remaining two hours to populate into the “time and a half” to be multiplied by that 1.5x rate. The caveat is that I need this to be capped at 4 hours of overtime, before spilling any remaining hours from the “Total Hrs/day” into “doubletime”. IF( (OUT-IN)-BREAK>8, ((OUT-IN)-BREAK)-8, BLANK() ) I can