Formulas

Hello Everyone, Maybe somebody form the community will help with something I’m struggling with. I’d like to make an API call with a Filter formula on a lookup field, which includes comme separated dates, see below : As you can see, there is a simple calculation field that is : IS_BEFORE(TODAY(),Booked_Dates) that doesn’t seem to be very happy with the multiple item field on Line 1. However, when I’m filtering lookup fields with conditions in Airtable it seems to work perfectly fine (don’t notice both conditions, only the second one): And the result : So my question is : Is there a formula I can use to have the same behavior th

I am making an automatically repopulating task list. I can’t use the Workday function because we work on Saturdays and have a non-standard holiday schedule. The formula I am using checks a field which adds days based on a daily, weekly, monthly assignment to the old due date. I have a formula that successfully checks that new date against the holiday string rollup, but it can only add 1 date at a time- so if there are multiple holidays in a row, it doesn’t recognize that. Is there a way to make this formula keep checking the Date Calculation 1 sequentially against my rollup of days to skip until it reaches a workday again? IF(FIND({Date Calculation 1 String}, {Skip String Rollup}&""),(DATEADD({Date Calculation 1}, 1, ‘day’)), {Date Calculation 1}) I’m guessing I need to change the Dateadd value of 1 day, but I’m not sure how. Any help is appreciated.

Hola, tengo un campo que indica el numero de vencimientos. ¿Como crear en otra tabla tantos registros como es el numero de venciimiento? O sea si tengo 3 vencimientos que cree en otra tabla tres registro para gestionar el pago. Gracias

Hello. I have a table that it is filled using a forms. there are two fields coming from two other tables that are selected by the customer. I need a formula column where it analyzes the two filled fields and returns the result (remuneration) based on a third table (remuneration_table). below I am attaching a photo to illustrate. basically I need the formula to return the candidate’s remuneration based on the sector and position he will be hired. Could someone help me?

Hi! I would like to set-up a formula where if Column A is not blank, Column B says “8.0”, and if Column A is blank, Columb B will say “7.0” Is there a formula to do this? Thank you!

I have a date formula to work out the number of days between 2 dates, if either or both of the dates are blank, it returns NaN, how can I amend the formula to only perform the calculation if both fields are populated? (Long overdue a lay-in and my brain is not functioning by itself, need help… :slightly_smiling_face: ) DATETIME_DIFF({Finished},{Started},“days”)

Hi I have a lookup field that is displaying values from a multiple select field, which I have converted into a comma-separated format. Let’s say the values in that field (field A) are: Cat, Dog, Antelope, Lion I have another field (field B) that has the possibility of having at least some of the same values as the first field. It might look like: Lizard, Elephant, Cat, Eagle The order of the variables is, well, variable because the user may enter them in whatever order they wish. What I need to do is evaluate whether any of the terms in field A match any of the terms in field B. In this case, it would return true because ‘Cat’ appears in both. Thanks!

Is it possible to sort records numerically in a roll up field?

Hi, I am trying to generate a “Date” based on the month input available in my airtable. I tried using the If function, however, the result is not being recognised as a date. A little more information on my use case. I’m creating a database from a form that asks people to enter their preferred month, these months are provided as dropdowns. So, for example, if my table has October as a Month - I want to be able to create an additional column that tells me the first date of the months, i.e October 1, 2021, should be the value. Any ways to do this?

I’m working on a table where I have different currencies (in separate columns) and need to convert them all to one currency. I have a column marking the currency used and so the conditional if statement uses this to have a different calculation for each statement. However, for some reason, the formula doesn’t work but it does not return an error, it simply returns a blank Cell, so I have no idea what I’m doing wrong. Please HELP. IF({Currency} = “MXN”, {Amount in MXN}/{FX}, IF({Currency} = “EUR”, {Amount in EUR}*{FX}, IF({Currency} = “USD”,{Amount in USD}))) I know about the Currency converter app, but due to the Terms and conditions/privacy settings, I can’t use it.

Its probably a simple formula…but here goes… I have a record in airtable that looks like this: The A, B, C represent the responses to survey What I want to do is COUNT the number of each letter - so for example A = 3 B = 1 C = 2 E = 1 I have got as far as: IF({Col1}=‘A’,1,0) and that returns the 1 (in this example) What does the formula look like to check in Col2 - Col7 contains ‘A’ and then give me the total of 3? I hope that explains what I am trying to do clearly :grimacing: :nerd_face: Thanks in advance.

Consulta: Si tengo un campo donde ingreso la fecha de nacimiento de una persona, cómo debo formular otro campo para que me devuelva la edad de esa persona y que se actualice automáticamente cada vez que consulte la tabla. Gracias a todos!

Hi, Is there in Airtable a (cumulative) normal distribution formula or work arround besides writing the full formula ? ie : Excel norm.dist function equivalant with cumulative option set as true. Thanks

I have 21 lookup fields that are used by different team members, which may or may not be filled in for a given Airtable record (i.e., one record might have lookup items tagged in 3 of the fields, another record 5 of the fields, yet another just 1, and so on). I’d like to summarize, with a formula field, all of the lookup items tagged for each record across these 21 fields. When I just use an & operator, I’ll end up with something like this, if the record has items tagged in more than one of these 21 fields: Tag 1A, Tag 1BTag2A, Tag 2B, Tag 2CTag 3A …so I want to add a comma and space separator between the tags from each field, but only when values are actually present in any of the fields so as to not have redundant commas. I tried doing something like this: {Field 1} & IF(AND({Field 1}, OR({Field 2}, {Field 3}, {Field 4}, {Field 5}, {Field 6}, {Field 7}, {Field 8}, {Field 9}, {Field 10}, {Field 11}, {Field 12},

Hi community, I need your help. There is something i don’t understand with the formula bellow : IF( SEARCH("Jungle", {Order Line Items}), "Jungle", IF( SEARCH("Baby Plantes", {Order Line Items}), "Baby plante", IF( SEARCH("Pets Friendly", {Order Line Items}), "Pet's Friendly" ) ) ) I want to write a 4th condition but it never work. What is the importance of the space between the condition and the “)” ? How can i built easly this type of formula ? Thank’s a lot, Matthieu

I have 3 columns that have data, and for any given record, only 1 of those columns will have data. I’d like a 4th column to populate the data that’s in whichever of the 3 columns has data. Examples: Column A = Blank, Column B = Blank, Column C = ABC, then Column D = ABC Column A = XYZ, Column B = Blank, Column C = Blank, then Column D = XYZ Column A = Blank, Column B = MNO, Column C = Blank, then Column D = MNO I cannot for the life of me figure out an IF/AND or a nested IF statement(s) that will make this work. Is this at all possible? Here’s a snipit of my table where I’d like Interaction ID to handle what Column D in my previous explanation would have. I appreciate any insight. Thank you!

Hello. I am trying to create a set of tables where our employees can fill a form to update the status of an order, and the latest status gets updated to a table which contains all orders. I have a table which contains details about orders, of which one of the fields is ‘status’. I have created another table with a form view, which has a single selection dropdown menu with different statuses of the order such as ‘Department 1’, ‘Department 2’, and ‘Department 3’. When an employee fills this form, they select the Order ID from a linked field (which is linked to the orders table), type in their name, and select the current status from the drop down. I would like only the latest entry from this Form Entries table to be visible in the status field of the ‘Orders’ table for that respective order. Any suggestions on how. to make this work? I would greatly appreciate any help you could provide. Thank you!

The setup: I have one table which is used to schedule marketing emails (each record contains a single date). I have another table which is used to schedule multiday promotions (each record contains a start date and and end date). What I’m looking for: For each record in my email-scheduling table, I would like a field that links to the records of all the promotions from the promotion-scheduling table that are ongoing during the day the marketing email is scheduled. Any help would be appreciated. Thank you.

I’m trying to combine multiple values from a multiple fields. For example. Column A: Type A, Type B, Type C (multiple values in a single cell) Column B: Option1, Option2 (multiple values in a single cell) Result: Type A - Option1, Type A - Option 2, Type B - Option1, Type B - Option 2, Type C - Option1, Type C - Option 2 Is this possible? Please help. Thanks!

I am trying to concatenate two columns, a percentage (based on a formula) in one column and its associated product in the other column. When I concatenate, the percent is converted to a decimal. I know to multiply this result by 100 and add ‘%’ to get the result I want. And it works for most values, but some of the decimals turn out like this: 15.920000000000002%. How do I get all resulting decimals to show as XX.XX%?

I have several tasks where a date field is filled for whatever date that task is completed. I tried to build a formula where if no date field is blank, it sets the date to TODAY() so that I can know when everything is done. It seems like this formula keeps re-updating to whatever today is everyday. If all the tasks were done Tuesday, it still keeps changing to whatever today is in that IF(***, TODAY(), BLANK()) statement. Am I doing something to cause the table to think these values are changing or do I need to use an automation trigger to accomplish this?

Hello, I’m trying to create a formula to return a value of “early deadline” if a field I have named {Application Date} contains a date less than or equal to 12/15/21. I’d also like to return a value of “Regular Deadline” if the date in the Application Date field is after 12/15/21. This is a variation of the the formulas I’ve tried: IF ({Application Deadline} <= (12/15/21),“Early Deadline” I’m wondering if I need to add anything to qualify the 12/15/21 date. The date functions I’m finding look like they compare dates in two fields on the table, where my issue is that I only have one date on the table and am comparing it to another date that is not on the table. When I try to save my formula I get an invalid formula error. Thanks in advance!!

Hi There, Apologies as I am very new to airtable. Essentially, I have a column which contains a series of numbers which indicates how many businesses make it through a particular process. This number automatically updates based on my input in other areas of the table. What I would like to be able to do is have the success rate automatically update in the next row. I can do it on excel, but with airtable being a database I am not sure how to go about doing this. In excel it looks like this : How can I do this in airtable? The point of this is because I would like to then create a chart which keeps updating with this information, showing how many as a percentage make it through each stage. Thanks L

Hello! I am trying to find a formula that will extract a customers name and address from a body of text and create new single line text fields with this data. The data is coming into airtable via an email automation, and the body of the text data coming in is always consistent in format, with four pieces of data that change, the name, address, market, and URL for the customer. There are other keywords within the body that could be used to identify the before and after for the desired text - but the left, right, and mid formula’s will not work because each body will have varying lengths based on the customers info.

Greetings All. I am new to Airtable, but getting around pretty well. I have a Base with multiple tables. I am trying to automate Prospect tracking as much as possible. I have a situation where when I enter a Zip Code I want it to populate a District, Regions, POC Name, POC Email, and POC Phone. I was able to do that with Zips I coded with a linked table and associated lookups. However, I had to restructure the table and index by District rather than zip. I just cant figure out how to do a sort of VLOOKUP function based on Zips in Airtable because the zips are no longer the index on the table. So, I figured I could use formulas instead. This is the full text of my formula: “If the value from the “Zip Code” column in the “Prospects” table appears in the “Zips” column on the “List of Districts” table, then enter the value in the “District” column of the same “List of Districts” table.” My issue is I cannot write the formula to save my life!! Is this possible? I hav