Formulas

I’m not sure what the best way to describe this would be, but I hope this example table will get my question across. I am looking for a way to count up the number of Hat Tricks a Player gets automatically based on them having 3 or more Goals in a single Game. Each field of the same name is link across tables. The trouble I’m running into is that I’m limited to one field of reference (I need both Game and Player to be considered) when trying to use a Count function. Any help would be appreciated. !

Hi, I have a formula I am trying to get to work that I took from another forum post and it’s not working, not sure where the syntax is broken. I am trying to mark a single select status field good or bad if the date of a last modified field is great than a certain number of days. IE. If the date of the field “Date of last check” is older than 7 days, change the status field to Bad Can someone tell me what I’m missing? IF( AND( DATETIME_DIFF( TODAY(), {{Date of last check}}, ‘days’ ) >= 7, {Status} = “Bad” ), “Good” )

I have a table called Events and in it is a record called Monopoly Tournament I have a table called Users and in it is a bunch of people that come to events I have a table called Registrants and in it is a field for 2 things … 1) single linked event 2) single linked user (thus, there are multiple records for users who are coming to the Monopoly Tournament) I have lots of registrants already coming and they want to have the same event the next week. Is there a way to … Easily duplicate the “Monopoly Tournament” record in the Event Table Automatically duplicate the registrants for the original event but change the event field value to the newly created event ??? Thanks

Hi all I need some help with formatting a nested IF statement as I can’t understand why it’s not working. Here’s the background: I have a {Start Date} field and an {End Date} field - both are formatted as “Date fields”. I want to check the current day - TODAY() against these dates and print out an emoticon depending on the result. I can get this single IF statement to work: (If the END DATE is AFTER today, then print “ :horse_racing: ” (running!), if not, then print “ :heavy_check_mark: ” (completed!) IF(IS_AFTER({End Date}, TODAY())=1,“ :horse_racing: ”, “ :heavy_check_mark: ”) However I want to add a third condition for the future: (If the END DATE is AFTER today, then print “ :horse_racing: ” (running!), If the START DATE is AFTER today, then print “ :crystal_ball: ” (future!), if not, then print “ :heavy_check_mark: ” (completed!) So I have this which doesn’t work and throws up an error: IF( IS_AFTER({End Date},TODAY())=1, “ :horse_racing: ”, IF (IS_AFTER({Start Date}, TODAY())=1

Hi all. Still learning the in’s and out’s of scripting, so please bare with me. I have a linked field, called {project name} that produces a name based on a formula. The name has the following format:" text1 * text2 " - so two words separated by an " * ". I’d like to append the text and remove everything from the " * ". So the new text would just be “text1” I can get this far: SUBSTITUTE({Project Name}, “*”, “”) - but this just removes the *, and not all the letters after. I think I’d need to use LEFT or RIGHT in some capacity, but that only looks at letter position? Could someone please point me in the right direction? Thanks

Hey friends, is there any formula I can use to replace HTML with regular text? I’m zapping in excerpts that contain ’ where a single apostrophe ( ’ ) should be.

Apologies for the unclear topic. I have a recipe database, where I have tables for • recipes • ingredients • recipe-ingredient junction, where I store data of e.g. amounts in recipes I just realised i will need to easily make different versions of basically the same recipe where the new recipe version has slightly varying ingredients. Say a “Cheeseburger with fries v1” record where the bread ingredient is white bread of brand A, then a “Cheeseburger with fries v2” with white bread brand B as one ingredients. Otherwise same instructions and ingredients. Perfect workflow for my users here would be to take the “Cheeseburger with fries v1” record in the recipe table, right-click -> duplicate record, get a new record with all the ingredients ready there, which they can start editing. BUT! When simply duplicating the recipe record, the duplicate record, of course, has links to the same ingredients in the junction table as v1 has (the same ingredients now link to both v1 and v2). Editing t

Hi! I have been using Airtable for a bit now, but just upgraded to Pro plan and am new to trying to ask for help on here. I just found the fantastic Fancy Finances base on Universe and have been going through trying to update it with all my info so that I can use it to track my $$. I have read all the informative instructions, but am a bit confused because when I am inputting income as negative values in the Deposits table, as per the instructions, it doesn’t seem that they are going anywhere? From what I gather in the other tables, the Deposits, when added as a “negative” value, should increase the running balance under Accounts; entering “positive” values under the Expenses table should and is decreasing the running balance under Accounts. I don’t see any way to contact the creator, so I’m asking for help here. Accounts table: I updated the Starting Balance and it reflected the accurate value. When I added transactions under the Expenses table, the Running Balance and Sum o

Hi, I’m new to the airtable community, and slowly understanding formulas, but I can’t figure out how to write a script to do the following. This is my base: https://airtable.com/shrVDFgXXjqANyMWd When I new record comes into this base, I want to set a trigger that checks the bid amount against the current “MaxBid,” and if it is ≤ to it, I want to then make changes to another field in that record. In this case, if the “Bid amount” of the new record is ≤ the “MaxBid,” I want to unlink that record from the “Item bidding on” so that it doesn’t pass unto the other table. How do I write and IF() script for this? Any help would be greatly appreciated. Thank you, Christhian

My formula for DATETIME_DIFF is not returning expected results but I can’t figure out what it’s calculating. The first should return a difference of 1:48, the second of 0:20. When I set the function without any specifiers, I get 4:12 and 5:40, and when I specify minutes, I get 0:04 and 0:06. Any help would be appreciated.

Use case: At the end of my estimates (page designer), I want to put in an inspiring or humourous quote… but I don’t want to type it myself, just collect them in a field somewhere and have them appear with a formula. What kind of formatting/formula would I need to have one long-not used quote selected from the list and popped on the paper? Is there some kind of CHOOSE RANDOM FROM LIST function? Thanks! Insert inspiring quote here

Hi! I’m looking to see if I can modify a WORKDAY_DIFF formula I have: WORKDAY_DIFF({Date JIRA submitted},TODAY()) Ultimately, I’d like the formula to look at if the “Due Date” column is filled in, and if it is, then return the days between “Date JIRA submitted” and “Due Date”. If “Due Date” is empty, then return the days to TODAY(). Guessing this is an IF statement? Is this possible? Thanks! Jeannie

Has anyone come across a way to use ARRAYUNIQUE(values) in a ROLLUP so that it is case insensitive? E.g. a rollup of “Demo, DEMO, DeMo, demO” should return one value.

Hi! I have a project management base with a series of dates for different stages of the production process. I want to have a series of corresponding “Function” fields that would flip to “Past Due” when a deadline passes. I am very new to Airtable and have had difficulty with writing date-based formulas. Thanks in advance!

Hello everyone, I have a cell that contains a comma separated list of images, examples below, I’d like to separate each value into a new cell, however I’d like this value to be strictly the url. Does anyone know how I can get this solution? Thanks in advance EX: LIST: 2020-10-09 08.59.08.jpg (https://dl.airtable.com/.attachments/0394c79d/2020-10-0908.59.08.jpg),2020-10-09 08.59.37.jpg (https://dl.airtable.com/.attachments/c9a6eabc/2020-10-0908.59.37.jpg),2020-10-09 09.00.14.jpg (https://dl.airtable.com/.attachments/e15451a9/2020-10-0909.00.14.jpg),2020-10-09 09.00.26.jpg (https://dl.airtable.com/.attachments/cd631799/2020-10-0909.00.26.jpg),2020-10-09 09.00.41.jpg (https://dl.airtable.com/.attachments/6cd5bde0/2020-10-0909.00.41.jpg),2020-10-09 09.01.03.jpg (https://dl.airtable.com/.attachments/fc16571d/2020-10-0909.01.03.jpg) DESIRED END RESULT Cell 1 https://dl.airtable.com/.attachments/0394c79d/2020-10-0908.59.08.jpg Cell 2 (Right of cell 1 on the same row) https://dl.airtable.com/.

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 are connected to the record, rather than authors: Here’s what I’m trying: I’m not sure why this isn’t totaling “All authors”, which is the table field I’ve selected. I’ve tri

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?