Formulas

Hi there, I am working on a membership base and trying to create a formula field that will generate one of the following statuses depending on the membership expiration date {Expires}: Active (Any time 31-365 days before Expires) Time to Renew (30 days before Expires) About to Expire (15 days before Expires) Expired (up to 90 days after Expires) Lapsed (anything after 90 days after Expires) A couple of things to know: I have two tables in my base, a Transactions table and a Members & Donors table. The ‘Status’ field will live in the Members & Donors table and uses the ‘Expires’ field, which is a formula field that adds one year to the ‘Last Donation Date’ field, which in turn is a MAX(values) Rollup field to get the most recent transaction for each donor from the Transactions table. I have tried my hand at a formula following the most similar circumstances I could find in the Airtable community to get the different statuses with no luck yet. Any help on this is greatly apprec

Hey Guys I’m try to do the math on this. if shoot days is greater than 4 and less than 8 return 4 but if its greater than 8 and less than 15 return 8. IF({Shoot days} > 4,IF({Shoot days}<8,4),IF({Shoot days} > 8,IF({Shoot days}<15,8),{Shoot days})) thanks

I am trying to create a formula that will result in an updated balance. I have a balance amount from the previous record. I either add an amount in or take an amount out in the following record. I need to have it calculate the new balance. So basically I am using an amount in a cell above the current record to help calculate a result in the current record. Does anyone know how this can be done. It is simple in Excel, but I don’t see how in Airtable. Thanks!

Hi there, I’m new to Airtable formulas, and I’m having a difficult time constructing a nested if formula that automates labeling events as open, upcoming, and ended. All items have an Opening and Closing date entered in their respective fields, and I’m hoping to have them but I can’t seem to compare them to today’s date accurately - does anyone have any suggestions?

I have two tables. One is Equipment. There are fields: Barcode, Name of Equipment, Where borrowed (Linked). Other is Lending Equipment. There are fields: Lender, Barcode, Created Date, Where borrowed. How do I use a barcode to identify a device? How do I use formulas in clever way?

dear all this is (should be) so simple, but yet cant get it to work New Law regulations in Austria (01.01.2019) :confused: Gig worker MUST be calculated per day If someone workes the nightshift this woud have to be divided onto the 2 days, according to the worked hours, this all per month, of course If “Max” workes on 12.02 18:00 - 13.02 04:00 what would be the solution to this calculation? how many hours “Max” worked on 12.02 and how many on 13.02.? hier is a link to a test base, if any one wants to play with calc und formulas, i have found no solution. yet. Airtable Airtable: Organize anything you can imagine Airtable works like a spreadsheet but gives you the power of a database to organize anything. Sign up for free. BR :m:

Hi there I have one field with value subdomain(.)domain(.)com, domain(.)com and etc. I need to get N fields separate source value by (.) The result will be for subdomain(.)domain(.)com field 1 subdomain field 2 domain field 3 com for domain(.)com field 1 (empty) field 2 domain field 3 com Thank you

Hello! Trying to create a formula to notate when 1 of 4 columns has text in it. For a project to be “complete” there needs to be date in either column A, B, C or D. I would love a column that would give me a check mark for it. Thanks, Eileen

Perhaps I’m not searching correctly, but I’m hoping the community can help. I’m looking to have a formula that concatenates multiple columns based on whether or not one cell contains a specific character, such as a question mark ?, but also contains other text. My formula already has a nested IF that is based on whether or not another cell is blank; see below: IF({UTM Content}="","",CONCATENATE({Link To},“string”,{UTM Content})) So, I want 2 results: If cell {Link To} contains a ? somewhere in it, then I want “string” If cell {Link To} does not contain a ? somewhere in it, then I want “string2” Appreciate any help!

Hi Airtable guru’s! Here’s what I’d like to do that I haven’t figured out. I’m teaching a series of classes that will have tests that are being held in Google Forms/Quizzes. I’d like to have these scores come into AirTable in one sheet to summarize all of these scores. Like a grade book. Email address | Test 1 | Test 2 | Test 3 I’ve stumped my self. HELP!

Hi - super basic formula question. Much forgiveness please :pray: So I have a bunch of tasks listed in a base as rows, with one column being “status” that has various dropdown options, including one called “Done”. I’d like the formula to return a 1 when the status “Done” is true and a 0 when it’s false. I have this: IF(Status=Done,1,0) But I get angry red text telling me to try again. I’ve tried with spacing, quotation marks, tried to look up more on IF statements, but can’t see what is probably a very simple mistake. Might be the late hour. Lil’ help? Thank you! C

This formula from the blog produces the result with at time: 9/30/19 12;00am What part of the formula needs to change to eliminate the time? DATETIME_PARSE(DATETIME_FORMAT({Period},‘MM/DD’)&"/"&YEAR(NOW())) Thank You

Hi – I’m building a Street Team in one table in Airtable. One view shows “assignments” which plots out what concert each street teamer is working on. Once they are done, they submit an Airtable form with their name, photo proof & the concert they worked on. This goes into a “review” view, which allows us to review to make sure they did the work. Since “Assignments” (generated by us) and “Review” (generated by the worker) need to match, I’m trying to figure out a way to match them. So if we assign Street Teamer A to work Concert X, then we need to know when Street Teamer A submits their proof for Concert X. So I created a match code field with the formula {name}&" - "&{concert}. In the example above, we’d know it’s a match because both OUR assignment AND their submission have the same match code (StreetTeamerA - ConcertX, in this example). But how do I write a formula that then can determine whether that Match Code is unique? Meaning, if there is more than 1 of the sam

Hi! I’m hoping to create a ranking relative to the category an item is in and can’t figure out the best way. My dataset includes items in multiple categories, ranked 1-1000 overall based on sales. I also have a “Rank in Category” field that ranks the items in each category, and category totals are anywhere from 37 to 242. Is there an easy way to make a fractional calculation relative to each category? For example, item 4 overall is actually 1 for its category of 92 items. The output can be 1/92 as a decimal, I’m creating a scoring system from outputs and that will help normalize it. Thanks!

Hi, I have used ARRAYJOIN in rollups many times, but never tried it in a regular formula field, and I’m having some trouble. I was trying to use ARRAYJOIN to create a string I can parse with DATETIME_PARSE. I’ll simplify the formula I wrote here to get rid of some complicating factors. The formula I tried was: ARRAYJOIN([“2019”,“01”,“01”],"-") Airtable didn’t seem to understand square braces, curly braces or brackets, but I also couldn’t figure out any other wrapper for the array that would get it to work. All of the help articles I found are intended for rollups, where Airtable generates the array automatically. Anyone know how to do this? Thanks!

Question: I have a collaborator field with numerous collaborators per record, and I was hoping to add a field that would add up how many collaborators there are listed in that record. How would I do this? Thank you!

This formula is not working to pull data from the two formula columns (Days Actually Out and Backdata days out) I want. It seems so simple. If the DATE OUT is before or is today, pull this number. If the date is AFTER today, pull another. IF({Date Out} <= TODAY(), {Days Actually Out}, IF({Date Out} > TODAY(), {Backdata days out})) It currently only pulls the ‘Backdata days out’ data for this column. . . Can anyone tell me if this is a no-no in AirTable or if I’m doing something wrong?

Hello, I would like to send daily recurring reminder emails to my team members. I would like the email to be formatted to include the task due and also include specific fields that I have created including multiple select fields and due date. For example, within a table, “tasks”, I have a view which is filtered to include a single team members tasks grouped by due date and a multiple select field identifying the “importance” of a task, either “low, moderate, and high”. I have a formula column which prints the text “send reminder” if the due date field is today’s date: “IF({Due Date}=TODAY(), “send reminder”, “”)” I have set up a Zapier trigger using the “scheduler” zap, which is scheduled to trigger every morning at 5am. Next, I set up a Airtable action, “find record”, to search for the value of “send reminder” in the table. I then set up an action for gmail to send an email to the team member. I want to format the contents of the e-mail to contain: The name of the tasks due today, alo

Hello, my first post to this community. This formula was entered and accepted with no errors. The issue is the second IF (in this case {Tax Type}='Sales Tax) does not produce a result. Here is the formula: IF({Tax Type}=‘Bank Rec’,IF({Qtr}=1,‘1/31’,IF({Tax Type}=‘Sales Tax’,IF({Qtr}=2,‘3/20’)))) My statement is: Statement 1: if the tax type is ‘Bank Rec’ and the Qtr=1 then the date 1/31 should display. Statement 2: if the tax type is ‘Sales Tax’ and the Qtr=2 then the date 3/20 should display. Here is what I have tried: If I change the tax types for e.g. put sales tax first and bank rec second then bank rec will be blank I have hand typed the formula in the field, I did not copy and paste I have tried changing the single apostrophe’s to double For some reason the second IF does not produce a result even though the formula does NOT error out.I have uploaded the image of the result. Thank you in advance. I hope i posted this correctly.

I can’t find a similar question so I’m reaching out. My placeholder shows Project - “our tracking number” but I need to have “project” changed to Recall ONLY IF a dropdown menu has the status of Recall. Suggestions on the OR formula to use?

I have a download column with 1 number each day. I want to have the total number of download from the beginning from each day that is incremented. Anyone can help ?

I’m looking to estimate a person’s retirement date based on their career start date, this would be using the assumption that they would work 35 years following their first job.

I have two worksheets where I need to compare numerous data fields. Each table has the same common denominator in column A (invoice number) I want to compare numerous fields in the primary worksheet pulling from worksheet 2. For example: I want to pull the record data from worksheet 2 into worksheet 1 to compare them. Tax amount, total invoice, ship-to address, ship to city, ship-to state. I then need to see via a formula if one doesn’t match the other. So if the ship-to city doesn’t match it returns an “X” I also need to pull in data (ship via) from worksheet 1 into worksheet 2 (where it doesn’t exist I currently do all of this in Excel with vlookup. Is this possible to do in Airtable?

I’d like to show N/A when the field is blank, at the moment I get #Error. Can anyone help with where would I add it into my formula? Here’s what we have so far - IF( (IS_AFTER({Hose Inspection Expiry Date}, DATEADD(TODAY(),14,‘days’))=1), “ :thumbs_up: ”, IF( AND({Hose Inspection Expiry Date}>TODAY(),{Hose Inspection Expiry Date}<(DATEADD(TODAY(),14,‘days’))), “ :rotating_light: ”, IF( {Hose Inspection Expiry Date}<TODAY(), “ :skull_and_crossbones: ”, IF( {Hose Inspection Expiry Date}=BLANK(), “NA”, “NA” ) ) ) )

I am trying to write a function that allows me to add a set of numbers if a specific criteria has been met. EX: SUM (IF {Category 1} = “widget”, value {Cost 1})+(IF{Category 2} = “widget”, value {Cost 2})+(IF{Category 3} = “widget”, value {Cost 3})+(IF{Category 4} = “widget”, value {Cost 4}) (I’m not even sure if an IF function can be used this way.) I also don’t know if I should be using a rollup table or something else, given that this data would be on a separate sheet. Any help is appreciated. Thank you.