Formulas

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:

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

I had searched through the community and seen a post stating about the date difference of a period showing data like 2 months and 31 days or 3 months 30 days. It is worse if it involves February as it will be calculated as 1 month 30 days. Example as below. I’m making all the months defaulted to have 30 days, as per below formula. DATETIME_DIFF(end,start,‘months’ ) & ’ M ’ & ( DATETIME_DIFF(end,start,‘days’ ) -DATETIME_DIFF(end,start,‘months’ ) *30 ) & ‘D’ I would like to know if there is any way to make the difference to be more accurate like those in Excel? I saw that there is a post regarding Switch() formula but I have no idea how to include that into this formula. The Switch() formula is as below. SWITCH(MONTH({start Date}), 1, 31, 2, IF(MOD(YEAR({start Date}), 400) = 0, 29, IF(MOD(YEAR({start Date}), 100) = 0, 28, IF(MOD(YEAR({start Date}), 4) = 0, 29, 28 ) ) ), 3, 31, 4, 30, 5, 31, 6, 30, 7, 31, 8, 31, 9, 30, 10, 31, 11, 30, 12, 31) Is there a way to calculate the

I am trying to make a simple sales table that will communicate with my inventory table to subtract what is sold and show me how much inventory I have left. I don’t understand most of the lingo…

Hey everyone! I’m new here and have searched the forum and google for this problem with no success. I saw the thread about rounding by 0.25, but I am seeing a different issue. It appears that the “significance” value must be 10 or less. If I enter CEILING(4,6) the output is 6 as I expected. If I enter CEILING(12,12) the output is 10, but I believe the correct answer here is 12. This happens any time the second number is over 10 then the result is always only a multiple of 10 instead of the number you entered. If I enter CEILING(27,12) the output is 40??? Is this a known issue? Thanks for your time.

Hi all, I have an AT base with numerous tables, one of which is a “Document Master Record”, with a linked table for “Document Revisions”. At the moment, the revision number is manually entered e.g. 7 for the 7th revision. Ideally, I’d like that to automatically increase as a new record is added (i.e. it is automatically creating Revision 8, the next time a new revision recird is added under the Master Record), but needs to be in sequence for the master record, so {autonumber} isn’t (as far as I am aware anyway) an option. I had a look at automation too to see if +1 could be added each time a record is created, but doesn’t seem to be an option either. Anyone got any ideas? Thanks in advance.