Formulas

Hi All, In the formula: IF(AND({Approved} = 1, OR({Project Manager} = “John Doe”, {Assigned Staff} = “John Doe”)), “true”) I’m trying to figure out how to change the = to contains. For example, Assigned Staff is a Collaborator Field and might contain multiple collaborators. In the formula above, it only works if “John Doe” is the only name in the field. I’m trying to figure out how to write the formula so that it looks for any name in the Assigned Staff field with “John Doe”. Thank you!

Hi AirTable Peeps. I basically have 3 tabs on my AirTable base. And an ‘autonumber’ set up on each tab. Using API I will be sending the information to local database. But each record needs a unique ID number. I was thinking I could add a letter in front of the autonumber. So Tab 1 - has letter ‘A’ infront, the second tab can have B in front Etc. This will avoid duplicate ID numbers. Any idea whats the best way to do this? I have browsed community Q and A and tried a few things but not been able to do this. To avoid confusion (for me) the name of the field/column is IDNumber. Thanks!

How can i make a formula for a single cell? and how can i show data from cell to cell? for example in google sheet i will right - =B2

I’m trying to use this formula: IF({Release Date}=BLANK(),“ :white_check_mark: ”,“ :no_entry: ”) but I’m getting the error message: Sorry, there was a problem saving this field. Invalid formula. Please check your formula text. I just want it to show the :no_entry: emoji if the the Release Date field is blank and the :white_check_mark: if not. I have tried IF({Release Date}=BLANK(),“ :no_entry: ”) as well as IF({Release Date}=BLANK(),“yes”,“no”) Sorry if this is a dumb question, I tried looking online and the solutions I found gave me the same error message

JonathanBowen just gave me a great answer to me last question so I thought I’d ask another. As described in my last question, we’re using AirTable to track investments by our Invest Group members. Lots of members. Lots of deals. Each member with their own investments into various deals. Each investment a member makes is captured in a record that’s the intersection of a member and a deal. Ideally, we’d like to show this data to our members online. But they can only see their own investments. I’ve (sort of) figured out how to use Page Designer to generate a PDF report for each member that shows their individual investments but we’re looking for something that can live online. Is there any way – possibly through a Zapier integration I haven’t thought of – to publish a webpage for each unique member? Ideally we’d be able to password protect - with a unique password – each page but if they were randomly generated, impossible to guess URLs we could distribute those and that would be good eno

I would like to change the assignee of a project (assignee field) based on the current status (single-select field). Is it possible to do this, and I am just missing it? My only current thought was to write a python function that runs every second to check for this and change the assignee accordingly. If not, where is the best place to request such a feature?

I have people submitting records through a form. Records have {Date Ordered} field with creation date. The records are ads that go into publications so there’s a {Print Date} field. I want to flag all orders that come in the day before or the day of print date with “Late Ad” how do I do this?

I am looking for a formular/function which lets me automatically copy/retrieve a value/text from the referece Field. Regards, Kai

For whatever reason I cannot get this formula to work. I am looking to sum the hours in a total hours column if the project name is equal to “Sick Day” and the date column is greater than 12/31/18. I am using this formula but its returning all sick day hours regardless of the date. IF(AND({Date}>“12/31/18”,{Project Name}=“Sick Day”),{Total Hrs},0) Any help would be much appreciated. Thanks

Is it possible to have a formula that will calculate difference from one cell to another cell in the same column? Basically I’m just subtracting A1-a30, A2-A30, A3-A30, etc. and putting the value in B1, B2, B3, etc.)

Hello, I want to create a navigation pane on the left side of a form which will allow users to quickly click to another category/table? Any suggestions? Thanks!

I have a formula that works most times but other times it doesn’t work. The idea is I have a table where I’m recording purchase orders and I have a formula that verifies the amount on the invoice matches the amount on the purchase order. So I have quantity ordered (Qty), unit cost (Unit$), a formula field to show the extended amount which is Qty*{Unit $}. I have the invoice amount and then I have another formula that let’s me know if the amount is verified or not which is IF(Invoice=Extended,“ :heavy_check_mark: ”,“ :no_entry: ”). I show two examples of where one works and one does not. Should my formula be something different? Thanks in advance!

I would like to insert a formula referencing a date field. Datefield MINUS 6 weeks . How do I write that?

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 not having any luck. Help!

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. 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.

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 concatenate formula, but with no luck. Any ideas out there?

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!