Formulas

Hi, I’m trying to use SWITCH as a way to clean and standardise some data. Here’s what I’m trying: SWITCH({Company},FIND("Amazon",{Company}),"AMZN",{Company}) So basically if the text entered into {Company} contains the word “Amazon”, the field should switch to “AMZN”. Unfortunately this formula is not working. Any thoughts besides using a nested IF? Thanks!

Im running a stock return spreadsheet and have zapier updating the current price on one column. I then calculate the difference that I bought the stock at vs the current price. I want to add another column that watches for the highest return I have had and the percent return. Is there an if statement that can be watching the price difference and only change the cell if its higher than the previous high I’ve had? Example. I buy a stock for $10 its now at $15. The net profit is $5. My highest return column shows $5. Tomorrow the stock goes to $20, the highest return column now shows $10 because its higher than the $5.

Is there any way to do A Counter that will increment only if the status is changed to a certain “text”. will remain its count and just keeps on incrementing We would like to count the all the links that has gone through a certain status.

Hello! I am venturing into some of the more robust AirTable features and am stuck on this formula. I need to create a formula where a task ‘Start Date’ is extrapolated from the ‘Project Due Date’, based on the ‘Project Status’. This is what I am attempting: The project due date is 5/30/19 Task 1 is in the ‘Project Kickoff’ stage Task 2 is in the ‘Week 1 - Prototyping’ stage Task 3 is in the ‘Week 2 - Design & Development’ stage Task 4 is in the ‘Week 3 - Design & Development’ stage I want the due date for task 1 to be 4 weeks BEFORE the project due date. (5/2/19) I want the due date for task 2 to be 3 weeks BEFORE the project due date. (5/9/19) I want the due date for task 3 to be 2 weeks BEFORE the project due date. (5/16/19) I want the due date for task 4 to be 1 week BEFORE the project due date. (5/23/19) I’ve been trying to use nested IF formulas, combined with the DATEADD formula, but

Can Airtable calculate a date for a record automatically, based on a date for a different record? Example: on my todolist, I have a field for “send newsletter” with a date of 12/1/2019. I want Airtable to calculate the deadline for writing content (14 days before 12/1/) and the deadline for proofreading content (7 days before 12/1). The WORKDAY function seems close, but it looks like that yields one value based on another value in the same field. A slightly different situation. Thanks.

IF( AND( {Date target goals to be published*} <= NOW(), {Link Building Status} = “Posted/Scheduled” ), “Live”, IF(AND({Date target goals to be published*} < NOW(), {Link Building Status} != “Posted/Scheduled”), “Past Due”, IF(AND({Date target goals to be published*} > NOW(), {Link Building Status} = “Posted/Scheduled”), “Scheduled”) ) ) Hi I am using this formula. And it works well except for 1 thing. It is returning “past due” on the field 12 on the same day it is due. Is there anyway to make it return a result of past due after the actual day of due.

Hi, I have a task list and want to capture at which stage something gets canceled (not moving forward). So if a record says “Not Started”, then it goes into “Canceled”. Then I want in another field for it to say “Not Started” so I know it was not ever started. If I had a record that said “In Progress” then it goes into “Canceled”, I want the other field to record “In Progress” to know that this task was being worked but now is canceled. Furthermore, if there is a way to capture how long a task was in stage “Not Started”, “In Progress” Ect so I can go back and measure how long things are taking in each stage. The durations should not go away. I want to see on one record, the time in each stage which aggregates to a total time since inception. I have zapier and can build what I need there, just need to know how. Thanks!

I’m going to try and explain this the best I can, and have provided a screenshot to help make sense out of my need for help. I’m probably in way over my head, as I am learning formulas but I’m hoping for some help. Here is what I’d like to happen: (No zapier please…still learning) Automatically add a check mark in followup box (hidden on example) if the ‘Status Change Date’ is over 14 days Remove the #ERROR from the formula for those without dates. in the ‘Re’vd’ field. Any help is greatly appreciated.

Hello i linked my app with Airtable but i want something which i cant find in that can we set a limit on the numbers of the row ? like in my app i just want to add 100 rows per table is it possible ? if yes than please tell me how to do this

Hi, I’m stuck on an IF-formula and hoping someone can help me out. I have two fields; A Lookup from another table A Multiple select IF the Multiple select field is showing the same as the Lookup, I want an IF-formula to output “something”. E.g.: Lookup: Done, Done, Working, Working Multiple select: Done, Done, Working, Working IF-formula output: “Identlical” OR Lookup: Working, Working, Working, Working Multiple select: Done, Done, Done, Working IF-formula output: “Not identlical” Very simple IF-formula: IF(Lookup=Multiple select,“Identical”,“Not identical”) My problem is that the IF formula always shows the two fields as “Not identical”, no matter what’s the input in the Multiple select. I have a theory that although the two fields looks to show the same, Airtable reads them differently (commas, spaces etc). With this in mind I have also tried to “translate” the two fields with a con

Hello all :slightly_smiling_face: I’m trying to work out a formula that establishes whether to charge VAT on a product or not but can’t seem to get the IF/AND/OR statement correct. The conditions that I require are as follows: If Gender is equal to “Boy’s” or “Girl’s” then 0% or If Gender is equal to “Men’s” and Size is less than 6.5 then 0% or If Gender is equal to “Women’s” and Size is less than 5.5 and Heel Height is less than 4 then 0% Else 20% I’m stuck in a bit of a black hole trying to figure this one out - any suggestions/examples on how to get this going or where to get started would be most appreciated!

Will a duplicated base be updated as new records are added to the original base? Thank you for your assistance!

Hi everyone, I have a Table that has two Email columns. One which the Email provided by the person at signup (Email), and one that tracks every Email provided at a signup for an Event (Email copy). The Email copy column is a rollup with arrayunique and gives back every unique Email. Now since most give the same Email at general signup and at Event signup, I’d like to have an Arrayunique on both columns, yet when I do this it gives me: The Formula in the Final Email column is ARRAYUNIQUE(Email & ", " & {Email copy}) Is there any way to make this work? Any Idea appreciated. Thank You

Hi everyone ! Here’s a very simple formula : IF( {Edition}=1, {Genuine Console Family}, IF( NOT( AND( {Accessories list}, {Games} ) ), {Genuine Console Family} ) ) I don’t know why, the first IF doesn’t work at all. I check the checkbox and nothing happens. But, when Accessories list and Games have values, I can check and the field is fill with the right value (Genuine Console Family). Thanks a lot for any help.

I have a lookup field. Sometimes it has one name in it: John Doe Sometimes it has two names in it: John Doe Jane Smith It will never have more than four words. I want to use a formula to output their first names only, so that the new field reads: John (if only one person) or John & Jane (if two people). Is this at all possible? I know you can do LEFT({Names},SEARCH(" ",{Names})-1) to get John, but I’m not sure how to tweak it to get John & Jane.

I’m trying to create a date column that includes the day of the week. I used the DATETIME_FORMAT formula, but it only correctly translates the date some of the time. In a number of cases it subtracts a week or more from the date column I’m referencing, which I hope to eventually hide. Any idea what’s going wrong? Here’s the formula I used: DATETIME_FORMAT({Go Live Date}, ‘dddd, MMMM do, YYYY’)

Hey! I´m new in this and also not much of a nerd :slightly_smiling_face: I’m trying to create a database for recording athletes test results. I have one table for athletes and another for results. I want to determine the age of Athlete at the time of the test. In “results” table I have a testing day field The “age at testing day” field should show the age in years or if possible the years + months (example 13 years 4 months) I´m grateful for all the help

Hi, I have had a lot of success using formulas in the past, but I am stumped on how to properly format the following: I have three columns: Email, Phone, and Wants Follow Up? I have been stumped on making a formula where IF the Email cell OR the Phone number cell is filled out (AKA not blank), then the output will be a YES in the Wants Follow Up? cell. I know I must be making an simple mistake, because the formulas I have tried only give a YES output if both the Phone and Email cells are filled out, not either-or. The actual OR formula as shown on the Airtable help section just outputs a 1, but I want YES text to be shown, not a 1. What am I not doing correctly? Thanks!

Hi everyone, I’ve been trying and searching for hours, but can’t seem to find a solution. My Events Database should be giving out the information of which Member of my group has attended which Event and in return also the information which Event was attended by which Member. So far so easy, but I get the attendance lists in a very large complicated table and it is a hustle to put the names in manually for every new event. Is there any way, that I can link an event to a new table which contains all the names attending and make it pull them so they can be compared with the third Members table? Any help appreciated! Thank You Thomas

I’m fairly new to Airtable and very new to formulas in Airtable. I have a table to track leads. In that table, I have a field for status titled “Converted Lead?”. The converted lead field contains the drop down choices yes, no and not yet. Is there a way to see lead conversion percentage - a formula that will add the No’s and the Not yet’s and then divide them by the yes’s to get our conversion percentage?

Hello I’ve four columns in a single one table, date de l’échange date de relance date manuelle prévue relance I would find a formula who fill my “date de relance” field with : if “relance” is = “pas d’action” , fields in “date de relance” will be filled by “ok” if “date manuelle prévue” is not empty, fields in “date de relance” must returns “date manuelle prévue”, with format ‘D/M/YYYY’ if not one of the 2 previous, fields in “date de relance” must returns “date de l’échange + 12 days” with format ‘D/M/YYYY’. I’ve tried this formula : IF(relance=“pas d’action”,“ok”,IF({date manuelle prévue}!=BLANK(),DATETIME_FORMAT({date manuelle prévue},‘D/M/YYYY’), DATEADD({date de l’échange},8,‘days’))) But i’ve a #Error ! “ok” is well returned , it’s working “date manuelle prévue”, is well returned with the good format ‘D/M/YYYY’

Hi, Newbie here and not good at code, hope you can help. I have 2 tables, there is a lot more to the tables that is not relevant so I have presented a simplified version just to explain what i need. 1st table has 2 columns a list of containers in the 1st and how much is in each container in the 2nd 2nd table has 3 columns, in the 2nd column i can select multiple containers from the 1st table in a single box, 3rd column i want it to display the total contents of the selected containers in the previous box. example Table 1 1 cont1 50L 2 cont2 175L 3 cont3 25L 4 cont4 50L example Table 2 1,3 75L 2,4 125L Hope this makes sense Cheers Vince

Hello, I am trying to create a table for our sales employees rolling bonus program. I have the main bones built, but I am struggling in one area. I need a way to add values in a compound manner. Example: January they reach bonus and earn $500 February they reach the bonus. They would earn $500 from January + their February bonus (Example $500 + $500) March they reach the bonus. They would earn $500 from January + February bonus + March bonus I need to use the rolling values of these for their commission statements. Does anyone have any suggestions for a formula? Thank you kindly :slightly_smiling_face:

hello everyone! i have this table that clocks in and clocks out people at work, but i would like that it would subtract 30 minutes(lunchbreak) so the person would clock-in then later clock-out and at clockout would subtract -30mins this is what i currently have: Přihlášen = clockin in Odhlásit = clock out . IF( AND(NOT({Přihlášen}), NOT({Odhlásit})), “0”, IF( {Odhlásit}, ROUND( DATETIME_DIFF({Odhlásit}, {Přihlášen}, ‘hours’), 60 ) & " hrs " & ROUND( MOD( DATETIME_DIFF({Odhlásit}, {Přihlášen}, ‘minutes’), 60 ), 0 ) & " min" , ROUND( DATETIME_DIFF( NOW(), {Přihlášen}, ‘hours’), 0) & " hrs " & ROUND( MOD( DATETIME_DIFF( NOW(), {Přihlášen}, ‘minutes’), 60), 0) & " min" ) ) any help would be apreciated, thank you.

Hi all, I’m trying to create a formula in a field that will take the value from one of two fields, whichever is not empty. One field is a multi-select, and the other a lookup. In essence this is quite simple for the multi-select field and the rollup field records (that are standalone strings), given the following formula: IF({Multi-select field}, {Multi-select field}) & IF({Rollup field},{Rollup field}) However, if a primary key in this table is linked to more than one linked record that the rollup field is referencing, then the rollup field outputs each of their associated rollup records separated by a comma. Thus, if the same above formula applies, I get an output this removes the comma and essentially combines all these rollup values into one long string. To address this, and to copy exactly how the values in the rollup field look (no comma removal), I nested a substitute formula to add any commas back in for when my second IF s