Formulas

Hi everyone, I’ve got a table that I’m having trouble with. In the “Act” column, I have a single-select with Act 1, Act 2, Act 3, and Act 4 as options. When one of those Acts is selected, I want the next column (“Act Dynamics”) to return a string of text. I’ve gone through similar questions from other users and have tried a few different possibilities, but each time, Airtable says my formula is invalid. Can anyone see what the issue is? Here are the 3 best guesses I have: Possibility 1: IF( FIND( 'Act 1', {Act} ), IF( FIND( 'Act 2', {Act}, ), IF( FIND( 'Act 3', {Act}, ), IF( FIND( 'Act 4', {Act}, ), 'Denial to anger;Rejecting opportunity;Ordinary world', ) 'Bargaining and losing;Wanderer' ), 'Bargaining and winning;Warrior' ) 'Martyr;' ) Possibility 2: IF(MID({Act},12,3)=“Act 1”,“Denial to anger;Rejecting opportunity;Ordinary world”, IF(MID({Act},12,3)=“Act 2”,“Bargaining and losing;Wanderer”, IF(MID({Act},12,3)=“Act 3”,“Bargaining and winning;Warrior”,"", IF(MID({Act},12,3)=“Act

Hi there, I am using the following formula to calculate total donations received within a period of time: IF( AND( YEAR({Received Date}) = YEAR(TODAY()) ), {Amount} ) However, I would like to modify this, so that instead of “TODAY()” - which gives the current year (2020) , I can set a time period like March 1, 2020 to February 28, 2021 (which matches our financial year). How do I do this in existing formula?

I am concatenating multiple fields into one comma separated field, and even though the formula format is 2 decimals, it’s showing as a whole bunch of them in the concatenated field. I’ve tried rounding but I can’t seem to get it right. Included pic.

When a partial record is submitted but there are other associated records still pending I want to avoid processing the submitted until the entire group is submitted. I would love to have a 1 of 5, 2 of 3, 5 of 5, etc. I don’t think word count will work as these records are referenced with a generated number (9 digits long) and currently have 2000 records and every day more records are inputted.

I’ve been trying to make new linked records from a list of data using the method described here: By having a long list or records and copying them into a linked field. I’ve done this a few months back where it worked perfectly, however now it does not work at all. If I open the linking field and click ‘Create new record’ I can make one at a time, but not too sustainable with a-lot of records. I’ve also tried to use both desktop and browser version but still the same. Is there some setting that does not allow me to create records from a linked field?

Hello, newbie to Airtable. I am trying to build a CRM tool that also forecasts future period recurring revenues. We have projects that often span 2-3 years. I would like to allocate the future revenue (equal monthly amounts is fine) across 12/31/XX fiscal year time periods. My inputs are: Start date End date Monthly revenue I’d like my outputs to be: Prior Period Revenue (any revenue prior to this fiscal year) Current Fiscal Year (e.g. 12/31/2020) Revenue Next FY Revenue Backlog Revenue (anything beyond next fiscal year) Let’s assume a hypothetical project as follows: Start date: July 1, 2019 End Date: March 31, 2022 Monthly revenue: $10,000 I would expect the following results: Prior Period Revenue: $60,000 Current FY Revenue: $120,000 Next FY Revenue: $120,000 Backlog Revenue (anything beyond next fiscal year): $30,000 I have been able to build this in excel but would really like to manage this process in Airtable. But I am stumped with Airtable date formulae. I think it boils do

Hey Everyone, I am trying to write a formula - if a certain type of encounter is successful then a specific cell (we call the anchor date) will enter that encounter “time/date” - we cannot get this to work?

How can I achieve the FLOOR.MATH function that exists in Excel into Airtable? FLOOR and ROUNDDOWN are not taking the precision as I expect. I would like floor with a 0.25 precision: FLOOR.MATH(11.78, 0.25) is returning 11.75 whereas in Airtable: FLOOR (11.78, 0.25) is returning 11.80 ROUNDDOWN(11.78, 0.25) is returning 11.25

I’m trying to automate going through and tagging customer responses (long text) I have a list of words or phrases I want it automatically tag with another phrase eg. Find and tag the following words “Specials” or “bargains” or “price” = Price, “menu” or “search” or “find” “navigate” =Navigation Done manually it looks like this I think the code would be something like this IF it contains “X” or “Y” or “Z” =Z, IF it contains “A” or “B” or “C” =A I’m not sure if I can set this up using the IF formula because it may not contain any of the words or it may contain more than one. Any advice? Should I be using a script instead?

I’m hoping someone can assist me with creating a formula. The formula should look at the field Article Title. We have thousands of articles. If any article contains a particular phrase, the field should return a specified URL. For example, we may have 10 articles with the word Movies in the title. For those ten articles, the formula should output https://www.imdb.com. More specifically, the article with the title Top 10 Movies of All Time would result in https://www.imdb.com in the field. This is what I have thus far and it’s not working, at all. IF( FIND( “Movies”, {Article Title}, ‘https://www.imdb.com’, ) ) Thanks in advance.

Hi everyone. I have a base where there are some status to define where are the content that are being created. But, I have to know the date and time when the status “Completed” was added. Do you know how to do this?

Hello! I’m doing my nut in trying to collect info from 2 (formula) columns into one, with the right (date) formatting. Here’s the sitch: I have subscribers who can register for 1 month, or 3 month blocks. I want to show their renewal date in one column. I’ve started with creating a “Month Start” field (the date their month-long registration started) and a “3 Month Start” field (the date their 3-month registration started). Next I’ve made 2 DATEADD columns: “Month End” adds 1 month to the “Month Start” date. “3 Month End” adds 3 months to the “3 Month Start” date. So far so good. Then, I’ve created a new column called “Renew Date”. I want it to pull the dates from “Month End” and “3 Month End” (where they exist; some cells in each column are blank). This works, to an extent, using CONCATENATE… {Month End}&{3 Month End} This pulls the info in, but it’s using that bastard format “2020-07-08T00:00:00.000Z”. So I’ve tried to throw in the DATETIME_FORMAT: DATETIME_FORMAT({Month End},‘DD

I’m trying to create a formula that looks for either of 2 fields to be checked. Can someone tell me what I’m doing wrong? I can get it to work with either/or but trying to look up both, I’m stumped. Thank you! IF((Agreement =1) OR(IF{Umbrella Agreement}=1)),“ :smiling_face_with_sunglasses: ”

Hey everyone, I am having trouble creating a formula that would help me with product listings with different expiry dates. My products come in packages of sessions with differing expiry dates. How do I get a formula that would help me get the expiration date for different products from the date of sale and respective validity period? For e.g. Product Listing: 6 sessions training package Date of Sale: 30/04/2020 Validity period: 30 days Product Listing: 12 sessions training package Date of Sale: 12/04/2020 Validity period: 90 days Product Listing: 6 group training package Date of Sale: 30/04/2020 Validity period: 30 days Expiry Column : ? Regards, Jeremy

Hi, I’m using a Rollup column with the formula ARRAYUNIQUE(value) pulling records from a Categories table. I want to be able to add an extra value to this rollup manually. How can I add another value to this array? For example, the ARRAYUNIQUE(value) is returning: value1, value2, value3 I want to change the formula so it returns:: value1, value2, value3, custom_value I’ve already tried the following without success: ARRAYUNIQUE(value) & "custom_value" ARRAYUNIQUE(value & "custom_value") Both options return => value1value2value3custom_value (it removes the comma separator) Any insights?

Hello guys! I need help on this: I have several tables (5) the main one (PAIN EVENTS) and another 2 of them are MEDICATION AND RESCUE MEDICATION. In the main one I list the other two in columns. In the medication tables, in both, I have added a rollup column that makes me the sum of the total pills that you have entered … QUESTION: I would like to have another TABLE that will add both columns of the MEDICATION table AND the RESCUE MEDICATION table. (From the rollup of each one of them …) But it tells me … “you need a link field to create a rollup” AND I’M ALREADY LOST. I have also tried in formulas: in formula … SUM () how do I enter the values ​​that the rollup columns of other tables give me? In parentheses. Can be done? Anyway that’s my problem now. Thanks.

Hello, everyone! Could you guys lend me a hand, please? I am using this formula to calculate the number of delayed or advanced days for a step in a project development: IF(AND({01 Limite}, {01 Entrega}), WORKDAY_DIFF({01 Limite}, {01 Entrega}, ‘2020-05-01, 2020-06-11, 2020-09-07’), 0) But for some reason it is miss counting. For example: right now 01 LIMITE is 11/05/2020 and 01 ENTREGA is 11/05/2020, and the answer is ‘1’. Should be 0, right? Thanks in advance!

This seems like ridiculously easy formula to write, but I’m stymied. IF({Paid} ≥ {120%}, ({Paid} - FMV),0) If the person Paid more than 120% (120% is a column calculating 120% of the Fair Market Value), then the value of their tax receipt is the amount the Paid - FMV. Otherwise, their receipt is 0. It seems so easy! But none of my attempts work. What am I missing? Thanks.

I need to add a filter where “Date Arrive is on or before 4/30/2020” AND “Date Arrive is not empty” AND “Departure Date is empty” OR “Departure Date is on or after 4/1/2020” I can’t do filters with AND plus ORs… Can someone tell me another option? For context, I have people storing stuff with me. I’m trying to build a view that shows what they have stored with me for the month of April. They don’t want to see stuff that is no longer in storage (denoted as “Departure Date”)

So I finally imported all of my finances into Airtable. I tried to make a net worth graph but the issue is that I do not have an updated balance each day - only when I make a transaction. Is there any formula I can create where it keeps the value the same until the next time it is changed? Also, I need to add up the values from each account but again, they have transactions on different days. Thanks!

Hi Guys, I need help in developing a formula to calculate days between two dates that’s falling in a particular month. For Example - I am working on task for 3 hrs a day that started on 8th Mar 2020 and continued till 12th Jun 2020. I want to calculate no. of hrs in Mar, April, May and Jun in separate columns. In excel, I can directly derive these value through this formula: =if(max(min(< Task End Date>,< Month End Date>)-Max(< Task Start Date>,< Month Start Date>,0))>0,NETWORKDAYS.intl(max(< Month Start Date>,< Task Start Date>),min(< Task End Date>,< Month End Date>),1,< List of Holidays>),0)<No. of Hours

Hi everyone. I am trying to make a formula that will take two time-only fiends (formatted as) HH:00aa and give me the total time in hours rounded to the nearest quarter. (e.g. 2.75, 1.25). This has been very difficult since all of the help I could find seemed to be geared toward Date/Time fields. Thank you ahead of time for any help you can give me!

Hi All, Im trying to create a formula field that sums all number fields or all fields that start with the letter M. I have nearly 1200 number fields and add more daily. At the moment I am adding in the field name to my formula every time i create a new one. Other collaborators don’t know how to do this and i worry it maybe missed. Any help would be very appreciated.

Hi people :grinning_face_with_big_eyes: I am trying to create a concatenate formula including a date-field. The date i the field is set to ‘european format’. But when I use it in the concatenate-formula, it gives me a different format and the hours as well… Can somebody help?

Is there any way to use the FIND function to search a field backwards (in reverse order)? In other words, I want to find the LAST occurrence of a certain letter/symbol in a field. So, let’s say I had this text in a field: cats_and_dogs_in_playground_part2_lg600125X7aspUT I want to find the position number of the FINAL underscore character so I could then use the LEFT or RIGHT functions to strip out all of the text before or after the position number of the final underscore character. The problem is that all of the fields will have a varying number of underscores, and all of the fields will have a varying number of characters both before & after the final underscore If the FIND formula enabled us to search BACKWARDS in a field, I could simply tell it to stop at the very first underscore character that it finds when searching in reverse. Or — if Airtable gave me some way to COUNT the number of underscores within the field (which would result in 6, based on the example above), and the