Formulas

I have addresses with several fields, some fields are not always necessary.MemberOrganizationMail Address2nd Mail AddressCityStateZipo CodeTrying to get a multi line field that has no blank lines.Here's the formula I am using. I cannot find a way to get rid of the blank lines. What am I doing wrong?I also included a screen shot of the existing multiline field.Thanks in advance for any help, Scott TRIM( IF( AND( Organization, {2nd Mail Address} ), Member & "\n" & Organization & "\n" & {Mail Address} & "\n" & {2nd Mail Address} & "\n" & City & ", " & State & " " & {Zip Code}, IF( AND( Organization, {2nd Mail Address} = BLANK() ), Member & "\n" & Organization & "\n" & {Mail Address} & "\n" & City & ", " & State & " " & {Zip Code}, IF( Organization = BLANK(), Member & "\n" & {Mail Address} & "\n" & {2nd Mail Address} & "\n"

Hi! I am struggling to come up with a formula for this problem I am having:I need to create a formula on Airtable that will list all the Sundays that fall between a field called Created Time (date), and another called Start Date (date that is after the Created Time).For example, if the Created Time was today: 03/19/24And the Start Date was a month from now: 04/19/24I would like this formula field to display the following: 03/24/24, 03/31/24, 04/07/24, 04/14/24If any of those 2 dates are a Sunday, they should be excluded from the formula.Can this be done? Anyone have any tips on how to do this? I am trying to find a workaround as Date fields aren't very dynamic when used in Automations, and would like an automation to auto-populate a linked record field (within my "Events" table) with all the Newsletter emails I have on a separate table's calendar for Sundays.

Hello, I am new to formulas so I was hoping someone can help me with the formula below. I am trying to create two new formula fields.In the first new formula field, I am trying to do the following: If the 'Category' field is Finance and the 'Owner' field is Jackie, Anton, or Jalen, then have the new field return "Assigned" If the 'Category' field is Finance and the 'Owner' field is not Anton, Jackie or Jalen, then have the new field return "Not Assigned"In the second new formula field, If the 'Asset Type' field is Marketing and 'Attachments Resource' field is empty, have the new field return Awaiting AssetsIf the 'Asset Type' field is Marketing and 'Attachments Resource' field is not Anton, Jackie or Jalen, have the new field return Agency OwnedAppreciate the help in advance!

Hey! I'm trying to figure out how to add a portion to a formula that would ensure that it's set for 90 days after a particular field, or before June 1st. The current formula pulls from a date field for when they registered (Date Registered), and adds 90 days. How would I add a restriction onto this formula so it's 90 days, or until June 1st?  DATETIME_FORMAT(DATEADD({Date Registered}, 90, 'days'), 'MM/DD/YYYY')

I'm using this formula in a Deadline Alert column: IF({Deadline} < NOW(), "Late", IF(IS_SAME({Deadline}, TODAY(), 'day'), "At Risk", "On Time")) I have a separate Status Column to mark tasks Done. Is there a formula to show "Done" (instead of "Late") in the Deadline Alert column when "Done" is selected in Status? 

Hi, I have 3 Fields “Name” “Results” “Position” I will like to compare the values in “Results” Field and position it from ‘1st - last row’ (Field “Position”) If “Field Results” Row 1 or Record 1 is > Row 2, then Row 1 = “1st” else If “Results” Record 2 > Record 3, then Row 2 = “2nd” else if “Results” Record 2 = Record 1, then Row 2 = “1st” What I want the “Position” Field to do is, compare if Results Field Row 1 is Greater than Row 2 then Position Field Row 1 is equal to “1st” And if Results Field Row 2 is equal to Row 1 then Position Row 2 will be “1st” Or if Row 2 > row 3 then Position row 3 = “3rd” Finally if “Results” Row 1 = Row 2 then both "Position Row 1 and Row 2 will be “1st” But “Position” Row 3 will be “3rd” What formula do I use to achieve this? Help me people This is what I want to Achieve

Hey Airtable! I'm looking for some complex scheduling help. I'm using Airtable to schedule a multi stage music festival, and we're hoping to have a formula that will look for scheduling conflicts for individual performers at specific times. I have an example of how we have the scheduling started attached. The main record name is the act, and then we've created different set times for different stages across the three days. There are also individual performers listed for each set, since some performers will be playing in multiple bands. So basically, I want the formula to be looking for scheduling conflicts for individual performers during specific windows of time. Is this possible? Do I need to be building the worlds longest IF/THEN formula? Appreciate any advice!

Hey guys, I have a button in my Airtable that triggers a Zap in Zapier. I'm using this code: { "https://hooks.zapier.com/hooks/catch/14888220/30r6035/?recordID=" & RECORD_ID() }, but it's triggering the Zap twice. I'm also using multiple buttons, and it's not working as expected. Can you help me troubleshoot this?

I have 2 tables with a many-to-many relationship...Companies companyName, companyCountries (linked to Countries table)Countries countryName, projectPhase, countryCompanies (linked to Companies table)The projectPhase is country level, for example...GB = "go live"JP = "phase 2"MY = "go live"US = "phase 2"1 company can have multiple countries, and so on the Companies level I've created a rollup using ARRAYUNIQUE(values) but I've run into a problem with a scenario like this...CompanyA is live in GB and JP and shows "go live, phase 2" CompanyB is live in JP and MY and shows "phase 2, go live"I've read a bunch of posts on this behaviour and how the rollup retains the sort order of the original linked source (in this case Countries), but my Countries table needs to be ordered as it is, and there's so much variation in my companies data that regardless of the ordering, I'm going to get these unsorted rollup values.  So when I group by my roll-up I'm getting a bunch of diffe

Hi,We have a scoring system that rates functionality in an interface. So as not to bias the total score, if the functionality isn't present we ask evaluators to give it a -1.In Excel, the final score is determined by a formula that:Counts the number of criteria where the score is >= 0Determines a max possible score based on the number of criteria scored (e.g., there are 10 criteria in a category but only 9 of them got scored the max possible score is 9*3=27 (9 criteria scored * highest possible score))Adds the values for each of the criteria scored to get the actual totalI'm able to use sum to get the total score but I'm not sure how to get the number of criteria scored without using countif which Airtable doesn't seem to have.Is there a way in AT to do what I'm trying to do? Any help is most welcomed. 

I’m trying to figure out if AirTable can solve equations with unknown variables. For instance, a break-even analysis based on data in the table. (Revenue per unit ⨉ Units sold) − (Fixed costs + Cost per unit ⨉ Units sold) = 0 (200 ⨉ Units Sold) − (1000 + 100 ⨉ Units sold) = 0 Where Units sold is the unknown variable (“x”), and I would like to know the value of “x” where the equation returns 0 (break-even). A normal equation solver would give: (200x)−(1000+100x) = 0 x = 10 So, could I get “10” to display using an AirTable Formula? Thanks!!

Is it possible using a formula or automation to compare two multi-select fields and show which items are missing from the 2nd? I'd like to have a 3rd field that would show just what was missing from the completed list. 

Hello! I'm creating an Airtable base to manage book club meetings. I have a column for the meeting dates (DD/MM/YYYY) which take place once per month. I'd like to have a "Reading Status" column that uses a formula to create the following labels:"All Done" for meetings where the date has already passed"Up Next" for the upcoming meeting"Not Yet Read" for future meetings that are not the next closest meetingI am new to formulas and struggling with how to set this up. Any help would be much appreciated. Thank you!

Hi All, I want to create a bibliography. I have all the pieces in different columns and want to use the concatenate formula to pull them all together. However the Title of the book needs to be in Italics, how do I carry over the italics from the cells, or adjust the formula to make only the title Italic? Author Surname, Initial(s). (Year) Title. Place of publication: Publisher. This is my formula: CONCATENATE({Author(s) Max 3},"(",Year,")","."," “,Title,” “, City,”:"," ",Publisher) Any ideas?

How do I add a column of numbers quickly without creating a formula?

Hello,I'm pulling in purchase order data for my company, and have a "Vendor Management Team Partner" associated with each purchase order record.I'm also reconfiguring these POs in a separate table, such that the email address of the PO requester is the primary key.If a requester has multiple POs, they'll have multiple values in the VM Team Partner column, so I'm also adding a Rollup for unique values.I'm then automating the copying and pasting of the unique values in the Rollup field into a Linked field for the VM Team Partner's email info.However, in only some of records, the Link field will display a comma at the end of the values, whereas the Rollup field will not.How is this happening when there is no comma in the Lookup field? I now have to add values in my "VM Email" table for both "John Smith" and "John Smith," which is an unexpected nuisance.Can someone help me understand how this is happening and how to remedy?

Hi,I hope you can help me, I've searched everywhere and I can't find an answer to my problem.I work for a training organization. I have a table with students. A table with training session dates. And a table with speakers.In the dates table, I make a link with the speakers (each speaker can participate on one or more dates)In the students table, I want to get the names of the speakers of each session, but in a deduplicated way (I want to use these names to print a certificate)But no matter what I do, I can't remove the duplicates in the names of the speakers.Airtable considers that the 2 or 3 speakers on that each training session date are different arrays.I tried the « arrayflatten » and « arrayjoin » fonctions (and a combination of these) but nothing works.Do you have any idea to ge me solve this ?Thanks in advance

Hi, I’m trying to add a formula where I divide one column by a number. Like: Column A: 5000 Column B: result Formula: Column A(5000) / 365 Any idea? Been trying to find a solution to make this work…really don’t want to go back to excel… :sob: :frowning: :sob:

Hi everyone, I am new to the Airtable and I'm excited to be part of this community. May I apologise in advance if I don't use the correct technical terminology.  I design surveys, impact assessments and evaluation tools for a criminal justice charity. I have created a survey using 'Forms' and in one section I have 10 questions that require word responses. For example one of my questions is 'When I am confronted with a problem i can usually find a solution'The multiple choice response can be 'Not at all true' [=1]. Barely true [=2]. Moderately True [=3]. Exactly True [=4]I need to create a formula for a separate column that will tally up all the numerical values and give me the overall score. Any advice on how I write this formula will be greatly appreciated. Cheers Justin    

Hello,Struggling to work out where I'm going wrong. I'm trying to use a formula to auto update the "next check date" of equipment based on the date it was last checked and a single selection box.Eg. Harness checked 1/1/24Check frequency MonthlyNext check date 1/2/24My current formula is as follows: IF(  {Check Frequency} = "Annualy",  DATEADD(    {Check date}, 1, "year"    ),    IF(      {Check Frequency} = "6Monthly",      DATEADD(        {Check date}, 6, "months"        ),        IF(          {Check Frequency} = "Quareterly",          DATEADD(            {Check date}, 3, "months"        &nbs

Hi! I have data coming in from a webhook to Make. I am trying to create a formula in Make to search Airtable for number of times an email appears in a field on a table and limit the number of submissions to Airtable. For example, I want to limit the rows to 5 for this user based on email. When the user submits data via the webhook, I want Make to do a search on Airtable and count the number of time the email appears in the table, if email appears less than 5, allow data to be submitted. If more than 5, give an error or prevent data submission. Here is the formula I used. 8.FIELD__KPZIT488X is the value from the webhook for email."{{8.FIELD__KPZIT488X}}"={Email}I have tried to do this with forms, but no luck. Trying to see if it can be done with formulas on the backend.

Hello, I am setting a legal case tracking base and I am trying to do something where:1: When I input the date of incident, it automatically calculates two years from that date2: I would like to have a table that also has a countdown until we reach the Statute of Limitations. Ideally, it would look something like: Date of Incident (03-11-24), Statute of Limitations (03-11-2026), Days remaining: 365I attempted the Datetime_Diff between the two, but it just gives me the number of days between the Date of injury and SOL, so I think I can omit the Date of Injury and just have a running countdown so that every day when I log in, the day is one less remaining in the column that is counting down. 

Is there a way to append data in a field? Let's say I have a long text field called "Campaigns" which will contain the different campaigns that a customer have agreed to through our emails. Is there a way to preserve the previous text and basically add the new text to the field? 

I want to create a "sheet" timetable that creates new dates using a formula based on one date that is inputted. I know there's probably a better way to say that so please disregard my ignorance. Here's what I want to happen...I input the "Class Start" date and all other dates are created using formulas. Here is our current sheet.Is this possible in airtable? I imagine there is a work around. 

Hello! I have a large set of dates that are a partial year and month, and would like to convert this to a final date of the month format. Examples:Data I HaveDesired Format24-Apr2024-04-3024-Aug2024-08-3023-Dec2023-12-31I've tried concat using LEFT( & RIGHT(I'd like to try SUBSTITUTE, but not sure of the formatting. I'm also open to any suggestionsThanks for your time!