Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,939 Topics
  • 18,228 Replies
5939 Topics
Z
Zarah_EapenNew Participant
posted in Formulas
Generate Date based on Month input

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?

293
Kamille_Parks11
D
Daniela_FernandKnown Participant
asked in Formulas
Nested IF statement returning a blank cell

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.

116
D
A
AJTechInspiring
asked in Formulas
Calculating the total number of 'letters' in a record

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.

106
Dimitris_Goudis
D
Dano_IrustiaNew Participant
asked in Formulas
Edad a partir de la fecha de nacimiento

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!

312
D
G
Gregoire_DeroulNew Participant
posted in Formulas
Norm.dist formula in Airtable

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

142
G
K
ktsParticipating Frequently
posted in Formulas
Concatenate 21 lookup fields with comma separators

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}, {Field 13}, {Field 14}, {Field 15}, {Field 16}, {Field 17}, {Field

102
Justin_Barrett
M
Matthieu_LABERIInspiring
asked in Formulas
If contain write

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

34
kuovonne
Cole_Wedemeier
Cole_WedemeierInspiring
asked in Formulas
4 Column If / Then Formula Help Please

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!

92
Cole_Wedemeier
S
Sujit_JoshiNew Participant
posted in Formulas
Trying to get latest Kanban status into a table

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!

31
Justin_Barrett
J
Joshua_Vogel1New Participant
posted in Formulas
Comparing the DATE field of all records in a table versus DATE RANGES of ALL records in another table

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.

104
Justin_Barrett
R
Ryan_MeachamNew Participant
posted in Formulas
Concatenate Multiple Values

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!

527
Alexey_Gusev
D
Dana_SelzerInspiring
asked in Formulas
Concatenate percentages so they show as percentages with 2 decimal points

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%?

11095
D
R
Robin_WilliamsNew Participant
asked in Formulas
Issue with Formula updating the trigger date

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?

61
Kamille_Parks11
H
Heidi_WussowNew Participant
asked in Formulas
Returning a text value if a date is less than or equal to another date

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!!

416
ScottWorld
L
Leon_CliffNew Participant
posted in Formulas
Calculating percentage difference in airtable

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

362
Alexey_Gusev
A
Alex_GrahamNew Participant
posted in Formulas
Help with data extraction from consistent msg body?

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.

543
A
J
Jason_HimseyNew Participant
posted in Formulas
Looking up Data in Another Table

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 have tried all kinds of ope

464
Alexey_Gusev
Ricardo11
Ricardo11Inspiring
asked in Formulas
Formatting geo coordinates (split latitude/longitude) for Mapbox

Hello community, I’m trying to average geographic coordinates. First I split the coordinate into lat & lon fields, for example: 41.3310413 and 19.7828039 In Google Sheets I’m using average() with correct result In Airtable the rollup with average() fails with NaN Does anyone know another way of averaging coordinates? PS: Also tried to remove the dot with SUBSTITUTE(Lat,".","",1) but am getting an error.

374
Ricardo11
1
11191New Participant
asked in Formulas
Formula results are not recognized as dates

I came up with the following formula. IF({Registration date}=BLANK(), ‘’, SWITCH({PLAN}, ‘A’, SWITCH({TYPE}, ‘1’,DATEADD({Registration date},30, “days”), ‘2’,DATEADD({Registration date},30, “days”), ‘3’,DATEADD({Registration date},30, “days”) ), ‘B’, SWITCH({TYPE}, ‘2’,DATEADD({Registration date},30, “days”)), ‘3’,DATEADD({Registration date},30, “days”), ‘5’,DATEADD({Registration date},30, “days”) ) ) ) Depending on the PLAN and TYPE, we want to return the value of {Registration date} plus the number of days. And if {Registration date} is blank, I want the value of the formula to be blank as well (to avoid #ERROR). However, the returned value is not recognized as a date and is displayed as follows 2021-12-17t00:00:0.000z If I remove the IF statement at the beginning, it is recognized as a date format. Is there any way to resolve this?

83
1
A
Ashley_BunnerNew Participant
posted in Formulas
Making a formula to clear data

Hello, I am still very new to Airtable so maybe this is easier than I think but would still like help. In general, I am looking to write a formula so that when a field (that is a single select option) is selected to “deleted” it will then clear many fields (that are multiple select) of all of their data. So in the picture included below, when the field Deleted is selected to “Deleted” I want all of the data in the other rows to be cleared and back to like nothing had been selected. How would I go about this? Thanks

71
kuovonne
Tiaan_Terblanch
Tiaan_TerblanchInspiring
asked in Formulas
Multiple IF formulas in one field

Hi Currently, I have 3-columns Due date (date field) Overdue (formula) IF(IS_BEFORE({Due Date},TODAY()),“ :rotating_light: OVERDUE :rotating_light: ”,"") Due Now (formula) IF(IS_SAME({Due Date},TODAY()),“ :gift: NOW :gift: ”,"") I would love to see both results/formulas in the OVERDUE column. Then it is not necessary for both columns. That said, I am not finding the right way of merging these two formulas in one field/column. Any help will be much appreciated. Thank you

72
Tiaan_Terblanch
D
Daniel_SmithNew Participant
posted in Formulas
Formula to combine two linked record fields and retain the record links

I am trying to combine two fields in a base. Both of these fields are Linked Records. Is there a way to combine the two fields and retain the records. Currently I am getting text if I use CONCENTATE or ‘&’ Here is a demo base with the example https://airtable.com/shr1OM2ygBXu8ygke Thanks for the help!

456
T
A
Air_HockeyNew Participant
posted in Formulas
Using unit of measurement in a formula?

Hi, I’m making a database of coffee beans. Roasters often sell in different units of measurement like grams, pounds, kilograms and ounces. I have a column for weight (number) and another for unit of measurement (single select). I also have price. Is there a way I can use a formula to calculate price per pound without requiring extra work from contributors?

384
A
M
Martin_NoahParticipating Frequently
asked in Formulas
Use a formula to see if a record exists in a specific view?

Hi all, I have a table where I want to have a “Cleared?” formula box that looks up if the current record also exists in a view, “Outstanding items” In plain language, if an employee in “Pre-service” view also exists in the “Outstanding Items” view, place an :x: . Otherwise, if they don’t exist in the Outstanding Items view, place a :white_check_mark: I am thinking the RECORD_ID() formula might work (catching an exception) but I can’t quite wrap my head around how to use it with a specific view.

342
M
M
Maria11Inspiring
posted in Formulas
Create a fomula using OR with multiple IF(FIND) conditions

Hi Am fine with IF(FIND) formula when I want different outcomes but not sure how to set up when I want all outcomes to be the same. I know the outcome from AND/OR will be 1 or 0 which I can then work from but need help with how to write the actual formula! Below is my template formula I use to remember the IF(FIND) formula and adapt to whatever I need - if it helps to show me what I need to add where please use IF(FIND(“search#word”, {search#field}), “the#result”) Any help really appreciated have been searching but not finding yet. Thank you

906
Dimitris_Goudis

Badge winners

  • First Reply
    GegHhas earned the badge First Reply
  • Welcome
    youssefhas earned the badge Welcome
  • First Topic
    _Chris_has earned the badge First Topic
  • First Topic
    GegHhas earned the badge First Topic
  • Welcome
    blessing333has earned the badge Welcome
Show all badges
Terms & Conditions