Formulas

I am soooo excited that this function is available! But now that it’s here, I’m having a hard time thinking through how to set this up for my workflow. Looking for some formula help here… My scenario: I need track when someone submits a project to a specific status. Currently, I have it set up so I have a view in Airtable that filters out everything except projects in that particular status and then have a Zapier integration to send me an email when there’s something in that view. I then go in to that view and mark whether that project is late, on time, early, etc. based on the due date for that project. I want to be able to set up a formula to compare the last modified time (only for that one status though) and compare it against the due date for the project and return a result of early, on time, late, etc. Help?

I have 3 Checkbox Fields : Workout Meditate Eat Healthy If all 3 Checkbox Fields are checked (True), I want a Formula Field to equal True so I can set the color for that row as Green Here’s my broken formula : IF(AND(Workout, Meditate, Eat Healthy) True, False) Thank you in advance

Hi all, Looking for some assistance with a formula I’m working on. Columns A and B are checkboxes and Column C is a multiple select field with one of the options to select being “Prioritized”. How would I write out a formula for "if A and B are checked AND Column C = “Prioritized” return = “Ready” If anything else (neither are checked, only one is checked, or the project is not prioritized) then return “Not Ready”. Does that make sense? I think I’m struggling with the “AND” portion since this isn’t excel. Help!

Hi - I’m creating a purchasing database for a furniture install project and would like to make various calculations to the summary of a field. For example - multiplying the summary/subtotal by 8.25% for tax - then multiplying it again by 10% for warehousing and install. Finally - adding up the subtotal, tax and warehousing and install numbers to get a final total. Thank you!

Hello fellow Airtablers! I’m in a bit of a bind trying to put together a formula for what I’m trying to do here. I have 3 different single select fields, which based on if they meet certain criteria, will populate another formula field that’ll show “Complete” or “Incomplete”. That formula is working wonderfully. However, I’m trying to come up with a formula that’ll state the reason as to Why it’s showing as incomplete. I can sort of get it to work for one field, but not multiple. Here is the formula I have for the field that populates with “Complete” or “Incomplete”: IF(AND(Status = "Done", {Billing Status} = "Payment Received", {E-file Status} = "Accepted", {Billed}!=BLANK()), "✔ Complete", "❌ Incomplete") Here’s an image of what it looks like: As you can see, it’s marked as Incomplete, because the billing status is not marked as “Payment Received”. I’m attempting to create a column after the {Final Status} column that will show why it’s marked “Incomplete”. Is this possible? Any hel

Hey Airtable friends, I was wondering if my problem had a solution so here it is. I ask questions to users who can answer them or decline (blank field if they do so) and I would like to know if I could create an IF formula that tells me if their profile is complete (all questions answered, thus fields are filled) or not (one or several values in selected field missing). There would be around 10 fields/questions (one in each column). Any solution for this? Thank you in advance

I’m wondering how I can transform the formula to date format. I have the date 15/04/2019 20:46, but I got it using formulas. Now I need to add some days, thats why I’m going to use DATEADD. But there is some problem. How I know we can use DATEADD just with date format.

Hi there, How do I link Table A in spreadsheet A to Table B in Spreadsheet B? Many thanks!

Is there a way to duplicate single line text into a multiple select column automatically. My end goal is to be able to add additional item descriptions in my form while on the fly (Cell Phone Form). IE: Add Flamethrower to Item Description while on the go. Later I can filter based on the Item description.

Hello, looking for help on how to to create a formula that references text in another “cell” on a different table to complete the formula. Here’s what I’m looking to do: Create an “IF(logical, value1, value2)” statement formula for a column in table 1 in a base, that references a single cell from table 2 (within the same base) that contains text to be included as a one of the values? Can someone advise if this is possible, and if so, how to go about doing it? Thanks, Cristy

I want a formula to look at a string of 10 digits and only write the last 4. How would I do that?

Hi there! I am organizing seating arrangements for a gala. Each table number I assign to a group corresponds with the floor their table will be located on e.g. (Table 1-19 = Floor One, table 2-29 = Floor 2, etc.) Right now, I am entering the table numbers for each group in a Number field and want to create a Formula field that automatically outputs what the corresponding floor is. (e.g. If their table number is “43” then the formula outputs “Floor 4”). I am doing this so I can group the view by the Floor # formula output. Is a combination of Nested IF & AND statements the way to go? Here is the code I tried (it doesn’t work because it only returns “true/false” or “Floor One”). Open to any suggestions and thanks for the help! IF( {Table Number} > 0, AND({Table Number} < 20), "Floor One", IF( {Table Number} => 20, AND({Table Number} < 30), "Floor 2" ) )

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 the problem is

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