Formulas

Hello, I am trying to write an equation for total pallets used. If the total cartons is over 4it gets palletized. My equation is IF(Cartons > 4, ROUNDUP(Cartons/{Cartons per Pallet}), 0). but Airtable wont accept it. Any advice?

Trying to create a formula that adds x number of business days if priority is x. This is what I have so far: IF(Priority=“High”,WORKDAY({Writer Assignment Date},3, IF(Priority=“Normal”,WORKDAY({Writer Assignment Date},5, IF(Priority=“Low”,WORKDAY({Writer Assignment Date},10)))))) It accepts this formula, but populates with an “#ERROR” in some fields and blank in others. All fields have the required selections. Trying to create a due date field that is customized by priority. If it is high priority for instance, the person would need to complete the assignment within 3 business days by that date.

So I have a table with a field Client that references a record from another table. Client column as such can contain a value such as ['rec1234abc']. Now I want to do a filter to return all records that reference a specific record. I tried this (using the Python client): my_table.get_all(formula="ARRAYJOIN(Client, '')='rec1234abc'") But that returns me no results. How can I accomplish this?

(I’m using a very pared-down example here in hopes that I can convey what I’m trying to do, and then apply a solution to my actual base where I’m having this issue.) I paste data into my base from an internal report weekly. When I paste records from that report, some of the records already have numerical data in Column 2, and those records will be blank in Column 1. If a pasted record does NOT have numerical data in Column 2, that means it will have existing numerical data in Column 1 (and it’s data that is essentially related to Column 2). It is more important to me that every record has data in Column 2. For records with data in Column 1, I can calculate what belongs in Column 2 by using a simple formula - for the sake of example, let’s say the formula is {Column 1} * 2 . However, if there is existing data in Column 2 (i.e. Column 1 is blank), I need the existing data in Column 2 to stay put. I want to write an IF statement for Column 2 that essentially says: “IF Column 1 has a nu

Hello! My airtable base gets new data imported into it every week from a separate report that is run. One of the drawbacks of the report is that it calculates revenue for our contracted associates, but not for our direct hires. In order to find the revenue for our direct hires, we have to divide the direct hire fee (DP Fee) by 13. Since we have the data for the contracted associates, is there a way to manipulate this field to leave certain data alone, but also divide other data by a factor? Screenshot below of data set to give you a better idea! Note that the field in question is “PIT Spread”. We are currently calculating these values for direct hires prior to adding new data to our database.

Hi all, I have a field called CATEGORY with Single select values like "Alcohol, Snacks etc ". For each of these values I want to maintain a SUB_CATEGORY field for example : For Alcohol, sub categories may be Beer, Gin, Whiskey. For Snacks, Sub categories may be Chips, Pringles. Now my requirement is: I want to create a formula that reads the CATEGORY and only displays the relevant sub categories for that category in the drop down. WHat is the best practise to achieve this. Thanks a lot in advance. Regards Sushmit

Hi there, I want to add a formula to result in a date. I have a delivery date column. and I want formula to calculate a date 7weeks (49 days) before the delivery date. I have this working in Excel & numbers, but copying the formula into Airtable does not work - results in NaN. Please may you help. Thank you, Sally

I am currently working on a formula which is able to detect which 1 out of 3 date fields are filled. I have done this with a similar table which detects which 1 of 2 date fields are filled but cannot seems to make the formula work with 3 options. IF(OR({Date A}=BLANK(),{Date B})=BLANK(),{Date A},{Date B}) I have tried the following 2 options, could someone help me identify where I am going wrong? IF(OR({Date A}=BLANK(),{Date B})=BLANK(),{Date C})=BLANK(){Date A},{Date B},{Date C}) IF(OR({Date A}=BLANK(),{Date B})=BLANK(),{Date A},{Date B}),IF{{Date C}, {Date C})

I have a table containing a PostCode column, and Address columns 1 to 6, all of type “single line text”. I wish to know which of the columns contains the PostCode and will use SWITCH(PostCode,Address1,1,Address2,2 …). However even when I can see which column contains the PostCode, let’s say Address3, a formula of PostCode = Address3 is returning false, when it should be true as they appear equal. Adding LEN() to both sides of the formula confirms they are the same expected length. The values I see in both columns appear identical. This behaviour is not the same in a table created with just these 2 columns and the values manually input. What differences in the values could there be that is not visible and how might the difference be eliminated?

I have a formula that’s doing what I want it to do. But I’d like the field {ACTUAL VALUE} to be currency formatted like {EST. VALUE} - I’d also like to get a SUM at the bottom of this column, so I need it to be a number. This is what I have so far. IF(STAGE="Executed", {EST. VALUE},"") What do I need to add? Thank you so much in advance.

Hey, guys! I’m trying to add a column that return to me a specific message when attends some date criteria. Actually, I did the formula and it was working well: IF(AND(DAY({Final date})=DAY({Request date}),MONTH({Final date})=MONTH({Request date}),YEAR({Final date})=YEAR({Request date})),“ :warning: Warning”, IF({Final date}<{Request date},“ :red_circle: Wrong”,“ :white_check_mark: Right”)) But after some tests I noticed that something weird was happening How you can see, the first and the second line returned strange results… Is there something wrong with the formula? Thanks!

Dear airtable community, i am looking for a function that: if a certain cell is modified, i get a value in a ‘last modified time’ field. after getting that, i want to know the work day difference between my ‘last modified time’ field and a ‘order date’ field. if the mentioned cell at first hasn’t been modified yet, i won’t get an return for the ‘last modified time’ field and so i want to have a blanc for the difference in work days. the function i was able to set up for now is: IF({found at},WORKDAY_DIFF({order date},{found at}, BLANK()) i’m aware of the fact that the point of formulating the conditon, isn’t fullfilled, but i have no idea what else to put there. if there’s an easier way of fullfilling my need than the if function, please. let me know :slightly_smiling_face: as always thanks in advance and stay safe guys

Can a conditional rollup take a field value from the current table as a variable? So in the screen shot above I would like to insert the field {Last Reload Date} from the current view, so that I can aggregate from that date.

Airtable can take the sum of an array through the rollup field type. Was wondering if there was the equivalent for the product of an array? For example if there is a series of returns (1+r1) (1+r2) (1+r3) With each return figure on its own record line, I want to figure out how to take the product of all three?

Hi folks, I am fairly new to the formula game and I’m working on a formula where I have 3 columns with dates {LOI Target}, {ETA to AMZ}, {Content Delivery Date} and i’ll need to calculate the latest date out of the three of these Add 7 days to the latest date Calculate to the next Tuesday input date correctly into column It’s a bit of a monster. Any help even in little pieces would be great. I’ve been wandering the forums looking for help and this one was the closest formula to one of my needs (latest date out of the 3 dates). So, is the following formula on the right track for what I want? I know that the MAX function doesn’t really bode well with dates but that’s about all I know. IF( OR( {LOI Target}, {ETA TO AMZ}, {Content Delivery Date} ), DATETIME_PARSE( MAX( IF({LOI Target}, VALUE(DATETIME_FORMAT({LOI Target},’MM/DD/YYYY’)), 999999999999999), IF({ETA to AMZ}, VALUE(DATETIME_FORMAT({ETA to AMZ},’MM/DD/YYYY’)), 999999999999999) ) , IF({Content Delivery Date}, VALUE(DATETIME_FORM

I built a data set to automatically apply a task due date relative to a main project release date, but I’m now trying to apply that function to multiple projects that feed in from a lookup field. I was able to get it to stop ERROR messaging with ARRAYJOIN, but it’s only applying the DATEADD to the first date in the string, rather than applying it to both dates and outputting a string. Alternatively, I’d love to output this into multiple records to be used like a to do list, but if I can at least get it to apply to multiple dates at once, I’ll be satisfied. Essentially, we have a ton of projects with similar timelines that fall on top of one another and I want to use this to manage the steps across multiple projects at once. Here’s my formula right now. Adding a 2nd ARRAYJOIN neither fixed or broke my formula so I’m not sure where to go from here. Thanks! ARRAYJOIN(DATETIME_FORMAT(DATEADD(ARRAYJOIN({Release Date (from Master)}, ", "), {Less Weeks}, ‘week’), ‘M/DD/YYYY’), ", ")

Hi, I am creating a table which lists our shipments of containers. I need a column of data to pull data from another sheet, if a 3rd column’s contents match the data on the other sheet. For example. On sheet 1, in Column 1, I enter a serial number. In column 2 of sheet on is a Lot Number. Now on sheet 2, I have a table as well as a form view. On the Form view, a person on our ops team enters a serial number and creates a new record. Once that record is created, I want column 2 of sheet to automatically: a) check to see if the serial number entered in the form (which created said record) is listed in column 1 of sheet 1; and b) if it is listed there, to populate column 2 of sheet 2 with the relevant data in column 2 of sheet one. it’s basically a lookup function but is reliant on several different data points. I have tried the link function within Airtable but it doesn’t seem to be able to handle this. If it can, it’s not at all straightforward. PLEASE HELP Regards Erik

Hey everyone! Thanks so much for all the help you’ve given, I’m still learning and this community has made it a great experience! I think what I’m trying to do can be solved with nested IF statements, just can’t seem to find the definitive answer looking through old posts. Here’s what I want to do. I want to calculate the number of hours it takes to do a certain job for a certain employee, where each employee takes a different amount of time for that job. For example, Employee 1 takes 4 hours to do Task 1, but Employee 2 takes 3 hours to do the same task. There are a bunch of employees and a bunch different tasks, and the hours are different for each employee. Is there a not so messy way to do this? Thanks so much for the support! EDIT: this is the formula that I have that works for one Employee and one task IF( AND( {Employee} = “Employee 1”, TASK = “TASK 1” ), “4” ) Where it will show 4 hours for Employee 1 to do task 1. Where I get confused is how to nest a whole lot of these to c

Hi, I’m using the following formula in the column “is absent today”, but for some reason it isn’t correctly finding August 16th. I suspect this is because the linked “Absent” field is an array, which is why I included the &"" in my formula, but that didn’t fix it… IF(FIND({Today's Date},{Absent} & ""), 'Y', 'N')

IF({Pricing (How Sold?)}=“By SqFt”,{Takeoff SqFt From Installation}+{Overage Amt},"") Pricing (How Sold?) Is a single select field. Takeoff SqFt is a # field looked up from a different table Overage Amt is a formula that multiplies the Takeoff SqFt by a % to get a number. I get the following when I try to format the output: “Your result type is not a number or a date. Formatting options are currently only available if your result type is a number or a date.” The result of the calculation looks like a number but creates an error if you use it in subsequent formulas. Any help would be appreciated

If someone could help me with this, I’d be extremely grateful. I can’t figure this out. This is what I"d like to achieve through a formula: Most Track Titles have a name followed by a “space hyphen space” and a suffix. I’d like to automatically delete everything after and including the “space hyphen space”. If Track Title is not followed by “space hyphen space”, just copy the Track Title over. Thank you so much for pointing me in the right direction.

I am using Airtable to connect several different contact lists by locations. The primary column is based on a 3-digit country code. This same column is used to connect all the data from each list. I need to build different grids based on city, region, etc. How can I setup different column/parents with several children? For example, ZAF is the country code for South Africa. Say Parent3 = Cape Town and Child1, Child2, Child3 are different areas in Cape Town. I need the LIST of children to auto-generate once I enter the parent in the previous field. Logic example: select continent (or enter) “Africa” > lists of country displays, select “South Africa” > new list displays, select “Cape Town” > new lists of displays, and I can select the final destination of “Cape Floral Region”. How can I use this defining area to connect the grid sheets by locations. For example, if I have different category lists (schools, restaurants, properties, etc) and each list is a mixture of count

Hello, I am looking for some help. I’m pretty new to Airtable, so I don’t know if what I am looking for is possible. However, I want to create a formula with the below logic. IF value in column A IS "YES" > 30 minutes THEN "Needs action". I then want to create an automation that looks at the value in Column B, and if it has the value “Needs action”, it would trigger the automation. I currently have: IF(AND(LAST_MODIFIED_TIME(MINUTE(Column A)= > 30)), "Needs Action") Is such a formula possible in Airtable?

Hi, I’m trying to set up a sheet to manage attendance for a preschool center. Kids have a different “weight” depending on their age, which I successfully set up. The weighting is used to determine how many kids we can have at once. What I now need to do is show a daily breakdown of those weights, because some children don’t attend preschool every day. I need a calendar where I can look at any given day and see how many total kids will be there. I’m trying to combine the weight formula with START and END date fields, but can’t make it work… Any advice is much appreciated!

Hi! Would anyone know how to prefill a form using multiple linked record field? The prefill form works with a single linked record but does not work when multiple linked records are selected :frowning: