Formulas

Not sure why the basic question hasn’t been asked or it’s hidden so much in the help section that I can’t find it but can someone please tell me how to subtract a start date and end date? No time. Just date. Like others, I don’t understand the AT explanations as they just give formula names and not examples. Again - just looking for the basic formula on subtracting 2 dates, without time. weekends/holidays - can be included. :slightly_smiling_face: Thank you for your help!

I’d like to create a column that displays the number of times a specific number (which is the name of an individual record) appears in a linked cell. For example: I have a single cell with linked records. Each linked record is named with a specific number ranging from 0-10, so it looks like this: [7] [0] [5] [9] [0] [8] [5] [7] [3] [7] [7] [3] [10] [7] [2] [7] [0] Looking at the single cell, there’s a bunch of of linked records with the name ‘7’. Is there a formula that can determine how many times ‘7’ appears in that cell? The ‘count’ option only counts the total number of linked records, but not how many times a record with the same name appears. Thanks in advance

I have 3 separate fields and I want the 4th field to show a result based on the 3. The responses can be yes, no, maybe If at least 2 of the 3 columns are YES, then result should be YES If at least 2 of the 3 columns are NO, then result should be NO If at least 2 of the 3 columns are MAYBE, then the result should be MAYBE If each column has one response of each, then the result should be MAYBE I am new to Airtable and am more familiar with Excel, so a simple formula or guidance would be helpful. My current Excel has a long formula of nested IF and COUNTIF. Are there equivalents of those on here?

I am looking for a way to make rows “point” to each other by linking them. Each row is a room and each room is either a classroom or a service room. I want to link service rooms to their associated classroom in one column and have another column that if the room is a main room, looks for service rooms linked to the main room and returns the list of associated service rooms in a list. I tried doing this with various combinations of lookup and linked fields as well as a nested IF formula and Array function with partial success. My backup plan is to just have two columns of linked fields and link the rooms to each other manually, but I was looking to automate this as much as possible and would like to have “N/A” or similar in the “Associated Main Room” column if the room is a classroom/main room or “N/A” in the “Associated Service Room” column if the room is a service room.

Hi there. I’m trying to calculate a percentage but only if it’s positive. As an example, one record might say 7000 for the total and 1000 for the cost and I want to know what percentage that is. But I have some records that are 0/0 which just gives me “NaN” and some that the total is 0 but the cost is negative by a large number and then it’s returning “-infinity” Ideally I’d like anything with NaN or a negative value to show up as 0 so that I can see the average for all the positive records. I was trying to use the IF function in the formula but I see nothing in the support section about using IFs with formulas besides just text labels. Here’s what I had as my formula but it won’t let me save it because it says it’s wrong so I’m coming here. IF({Cost} > 0), “({Total}/{cost})”, “0” I also tried: IF({Cost} > 0), “Value({Total}/{cost})”, “0” And: IF({Cost} > 0), “=({Total}/{cost})”, “0” But none of these work and I don’t understand what

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 distr

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

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!