Formulas

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(

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

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.

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( {Emplo

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, r

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:

I’m using formula to create a date field, which is used in Calendar view as a calendar’s source. My problem is to create empty date, if certain criteria is occured. I try to use BLANK(), but this returns it in non-date format. Here is the formula: SWITCH(UPPER({ :bellhop_bell: Agenda Status}), “PENDING”, IF({Scene Setup date}, {Scene Setup date}, {Due Date}), “SETUP”, IF({Scene Draft date}, {Scene Draft date}, {Due Date}), “SKETCH”, IF({Scene Draft date}, {Scene Finalize date}, {Due Date}), “DRAFT FOR REHEARSAL”, IF({Scene Finalize date}, {Scene Finalize date}, {Due Date}), “FINAL FOR SHOW”, IF({Scene Show date}, {Scene Show date}, {Due Date}), “CANCELLED”, BLANK(), “COMPLETE”, BLANK(), “REISSUE”, TODAY(), IF({Due Date}, {Due Date}, TODAY()) ) Note, formula works, but this does not allow to set field formatting as date. Is there a way return empty date in formula, so that field is in date format and not string ?

Hi guys!! I need to summarize the percent of a group over the total as shown in red in the example. Any suggestion? Thanks

Hello, First challenge…I am trying to show when a number is input to the RECEIVED column, that it subtracts itself from the ORDERED column and puts the result in the BACKORDERED column (however if the RECEIVED column is blank, I need the BACKORDERED column to be ‘0’ or blank as well). Second challenge…If the RECEIVED number equals the ORDERED column then the STATUS column should show ‘complete’. If the RECEIVED number is less than the ORDERED column, the STATUS column should show ‘in progress’. If I was to say this in one sentence it would be “If received is not 0 or blank, subtract it from ordered and show result in backordered AND if received equals ordered change status to complete OR if received is less than ordered change status to in progress” Any help would be much appreciated! :grinning:

Hi All - I would appreciate any support In one base, I have two tables (Master and Prices). I have a list of products on the master table with a field (column) named costs, and in Prices, I have Product and new Price fields. I need assistance (not sure if this is possible) with a formula that will help me update the costs in the Master table if the product in the Master table is also available in the Prices table. Example: In this example, the Master table will only update products A and C’s Cost fields because it has not picked up a product on the Price table. This means that in Table 1, product A will change automatically to 150 and C to 250. Nothing will happen to B because there is not a unit for it in Table 2. Any help will be much appreciated. Thanks